Database Users and Triggers

I created a trigger on a table T1 for insert and update. The trigger
selects values from table T2, which happens to be in a different
database. It seems I have to create users in that other database for
every user who updates or inserts records into T1. Is there any way
around this, using views or stored procedures, in SQL Server 2000?
Thanks
TimoHi Timo,
I don't think you'll be able to do what you want.
Trigger is running in security context of a user who fired it. As so, select
statement on T2 table is run as that user and user must have proper
permissions.
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Timo" <timo@.org.org> wrote in message
news:eBj$lRp9FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I created a trigger on a table T1 for insert and update. The trigger
>selects values from table T2, which happens to be in a different database.
>It seems I have to create users in that other database for every user who
>updates or inserts records into T1. Is there any way around this, using
>views or stored procedures, in SQL Server 2000?
> Thanks
> Timo|||Hi,
please refer article FYI :
http://www.netdscure.co.in/Articles/AuditDML.htm
--
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Timo" wrote:

> I created a trigger on a table T1 for insert and update. The trigger
> selects values from table T2, which happens to be in a different
> database. It seems I have to create users in that other database for
> every user who updates or inserts records into T1. Is there any way
> around this, using views or stored procedures, in SQL Server 2000?
> Thanks
> Timo
>

Database triggers - SQL Server - Fields only allowed if listed in another field in another

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).

eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.

Does anyone know the easy way to do this? / Syntax for the trigger?

Why not use PK/FK instead of trigger? I mean you can define the YesNoBlank column as Primary Key in some table, and other columns whose values must exist in the YesNoBlank column as Foreign Key referenceing the YesNoBlank column. For more information about PK/FK, you can take a look at:

Creating and Modifying FOREIGN KEY Constraints

|||

I think Check constraints are my preferance, as I have all of my fields which would potentially be Primary keys in your example in a single reference table (ie multiple columns)

My statement in('Yes','No','') does not work for my check constraint.

Does anyone know what my syntax would be?

|||A quick sample:

CREATE TABLE testConstraint (ID INT, NAME sysname, YESNO VARCHAR(3))

ALTER TABLE testConstraint
ADD CONSTRAINT yesno_check CHECK (UPPER(YESNO) in ('YES','NO'))

--This will succeed
INSERT INTO testConstraint VALUES(1,'Iori','Yes')
--This will fail
INSERT INTO testConstraint VALUES(2,'Kyo','noo')

For mor information, you can refer to:?ALTER?TABLE?

Database trigger to run managed C# code

Hi there,

Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.

How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?

The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.

http://msdn2.microsoft.com/en-us/library/ms345136.aspx

I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.

However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....

|||

I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.

Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last

This increases performance significantly! Awesome!

database trigger question

Hi

I am trying to setup a trigger on a database where the trigger fires off a store proc when there is an insert. For some reason, its working on a database in Dev and not on a database in QA.

the trigger is on an insert to a table, the trigger looks something like this

create trigger XXX

after Insert

SET XACT_ABORT OFF -- this so that when the proc attached to the trigger fails, insert it anyway

exec updatesomething

if @.@.error <> 0

exec createAudit

In dev, the row is inserted, but in QA the row is not. I did a trace, both have the SQL:BatchCompleted event of the insert sql statement, but in QA environment, the trace does not have the sql statement after exec updatesomething. it just stops at exec updatesomething.

I check the database settings to make sure there were the same, looks like they are, I do not know how to find out what is causing it to work in 1 database and not the other

thanks

Pauli

A few things to check for -

Make sure that the trigger exists and is enabled -- select objectproperty(object_id('dbo.XXX'), 'ExecIsTriggerDisabled') -- should return 0.

Your description above seems to imply that the trigger actually fired in the QA environment. If so, then it could be that there was an error causing the 'exec createAudit' to be skipped. You can check for this by looking for error events in the trace output.

Hope that helps you track it down.

Database Trigger on Update Table

Dear All,
I want to create a trigger on a table (A) which can copy the records
into new table(B) whenever it updates or any new record inserted into
the table (A).
Kindly create this trigger for me.
TIACREATE TRIGGER A_IU
ON A
FOR INSERT, UPDATE
AS
INSERT B
SELECT *
FROM Inserted
GO
On 28 Jun 2006 04:54:08 -0700, "Atif Iqbal" <aatif.iqbal@.gmail.com>
wrote:

>Dear All,
>I want to create a trigger on a table (A) which can copy the records
>into new table(B) whenever it updates or any new record inserted into
>the table (A).
>Kindly create this trigger for me.
>TIA|||Hi
Marco
create trigger tr_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0 return
insert MyAuditTable
select i.ID, d.MyColumn, i.MyColumn from inserted i join deleted d on
d.ID = o.Id
"Atif Iqbal" <aatif.iqbal@.gmail.com> wrote in message
news:1151495648.866045.307900@.75g2000cwc.googlegroups.com...
> Dear All,
> I want to create a trigger on a table (A) which can copy the records
> into new table(B) whenever it updates or any new record inserted into
> the table (A).
> Kindly create this trigger for me.
> TIA
>

Database Trigger in MSSQL

How can i create database level triggers in MS SQL? i m not talking
about table trigger or view trigger.

Khurram.What do u really want to capture?|||Khurram (khurramanis@.gmail.com) writes:
> How can i create database level triggers in MS SQL? i m not talking
> about table trigger or view trigger.

In SQL 2000 you cannot create trigger on database level. In SQL 2005,
currently in beta, you can.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DataBase Trigger

Hi All,
I want to create a comon insert trigger which should be applicable to all the tables in a database
i.e,
the trigger should be fired every time when we try to insert a record in any one of the tables in that database
i don't to achieve this using Stored Procedures
Thanks and Best Regards
Jothi Magesh
> I want to create a comon insert trigger which should be applicable to all
> the tables in a database
There is no such thing in current versions of SQL Server. There are tools
that can help you though.
http://www.aspfaq.com/search.asp?q=lumigent