November 25, 2002 at 2:35 pm
Hello... Me again...
Why do UPDATE Triggers fire when a row is INSERTed ???
I'm trying to keep an audit trail and log the different events. i.e. - I want to distinguish an INSERT from an UPDATE in an audit table.
But - the UPDATE fires on every INSERT as well so every insert has 2 events - an INSERT *and* an UPDATE.
Not really THAT big of a deal - but it's redundant and generally irritating.
Any ideas ??
btw - I posted this queestion once before and received no answers - hoping to land one here 🙂
November 25, 2002 at 2:53 pm
Technically, an Update is defined as a delete followed by an insert. If you had a delete trigger, you would see it firing as well.
You can verify this by creating a three triggers which simply log that they fired, and view the log after performing the different functions (insert, delete, update)
You also have evidence of this by the inserted, deleted temp tables available within the scope of the trigger. Inserted makes use of the inserted, deleted makes use of the deleted, and update makes use of the inserted and the deleted.
Edited by - scorpion_66 on 11/25/2002 2:54:08 PM
November 25, 2002 at 2:54 pm
What does the insert trigger do? Does it update the table? Might cause a 2nd trigger to fire there.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 25, 2002 at 7:25 pm
I agree with Steve, there is something causing it. What is your code doing since INSERT and UPDATE triggers do not get fired but by those events alone. It is a common mistake to write an INSERT trigger that say updates a row to a specific value based on the inserted data and as a result the UPDATE trigger will fire from this. Do you have an INSERT trigger as well that does an update?
November 26, 2002 at 6:55 am
Oopsie - but still have a question...
I have another trigger which fires on INSERT to update a particular column in the same table - my bad.
However, is there a recommended method of avoiding this situation?
I know there is a way to turn off "Recursive" triggers - but I only want to override this one particular case. Not ALL triggers in the DB.
As usual - thanks for the replies...
- B
November 26, 2002 at 7:13 am
You can use trigger_nestlevel() in your UPDATE trigger to test if another trigger fired causing it to fire.
Really lame example
create table testme (
[col1] int not null,
col2 int not null,
col3 int null
)
go
create trigger tr_testi on dbo.testme
FOR INSERT
as
if (select col1 from inserted) = 0
begin
update testme set col3 = 4
end
go
create trigger tr_testu on dbo.testme
FOR UPDATE
as
if trigger_nestlevel() > 0
begin
RAISERROR('BrokeIt', 11,1)
return
end
if (select col1 from inserted) = 0
begin
update testme set col3 = 4
end
go
insert testme (col1, col2) values (0, 2)
go
November 26, 2002 at 7:58 am
! Perfect !
In the meantime I've tried to set the RECURSIVE_TRIGGER dbOption to OFF.
But, to no avail.
If, in MMC, I go into the Database Properties and into the "Options" tab - the setting for "Recursive Triggers" is not checked yet a direct recursion event fires - ????
1) What's the easy way to see your "Options"
2) Why does this not work as advertised:
sp_dboption 'MyDB', 'RECURSIVE_TRIGGERS', 'OFF'
I really wish:
1) ALL the functions behaved the same(parameter specs, return values)
2) ALL functions behaved like other useful functions in the world.
i.e.:
exec sp_dboption
would return the current Database options!
SQL Server is just soooooo quirky. The product needs to be "Microsofted".
Oh - worst of all GRRRRR - are modal property boxes which are not sizeable (Permissions dialog box is a good example)
whining complete.
November 26, 2002 at 8:55 am
OK - got things moving OK.
My "Things to note"
1) Some functions set values to ON or OFF and some to TRUE or FALSE. Very annoying to keep track of.
2) sp_dbOption is deprecated - usd ALTER DATABASE instead.
In the SQL Server help file under topic:
"Using Nested Triggers"
These statements are not fully accurate:
<
A trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set. There are two types of recursion:
Direct recursion
Occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again.
Indirect recursion
Occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1 which causes Trig1 to fire again.
Only direct recursion is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion, set the nested triggers server option to 0, as well.
>
I have turned "Recursive Triggers" OFF and my INSERT trigger still calls the same tables UPDATE trigger. I guess the recursion refers ONLY TO THE EXACT SAME TRIGGER ON THE EXACT SAME TABLE. Not necessarily any trigger on the same table.
So - I used the Server option "Nested Triggers" and turned that off - now it's behaving properly.
Also Antares:
It appears that the trigger_nestlevel() function refers only to how many triggers WILL BE FIRED from a particular action - not necessarily how deep in the levels you are. I may be wrong on this but in my testing - things appeared to happen in this fashion.
I tested by altering the Trigger Fire Order.
Seemd that no matter what trigger fired first, the nestlevel always remained the same.
OK - thanks again all - B
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply