update trigger fired after an update in an insert trigger

update trigger fired after an update in an insert trigger

Post by QW1pc » Tue, 30 Nov 2004 01:49:03


Hello,
I have a table that has 3 triggers update, insert and delete

I use this triggers to insert changes on the table into a log table

In the insert trigger I have to update the row that was just inserted, this
fires the update trigger. so for an insert I get 2 rows in my log table
(insert, update)

To prevent this from happening, I have added a column to the table (sort of
an insert flag) which I switch off in the update.
I don't like this work around it isn't pretty

the question : how do I know in the update trigger not to insert a new
record into the log table?
 
 
 

update trigger fired after an update in an insert trigger

Post by David Port » Tue, 30 Nov 2004 05:01:38

You could combine the INSERT and UPDATE actions in one trigger. If
RECURSIVE_TRIGGERS is set OFF then the trigger will only be called once. You
can use the INSERTED and DELETED tables to determine the type of operation
being performed. For example:

... EXISTS
(SELECT *
FROM Inserted)
AND
NOT EXISTS
(SELECT *
FROM Deleted)
-- Action is INSERT

However couldn't you use a DEFAULT value rather than modify the data just
inserted? For example to enter the creation date on each new row:

CREATE TABLE YourTable (... creation_date DATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP)

--
David Portas
SQL Server MVP

 
 
 

update trigger fired after an update in an insert trigger

Post by Hugo Korne » Tue, 30 Nov 2004 06:38:37


Hi Amir,

You might find the builtin funciton TRIGGER_NESTLEVEL() useful. See the
documentation in Books Online.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)