TRIGGER_NESTLEVEL

Here's the short of it: If you have a trigger that will be triggered by another trigger that will then trigger the original trigger which will again fire off the second trigger and so on and so forth, YOU need TRIGGER_NESTLEVEL

I ran into a situation where I had the same field (more or less) in two database (Microsoft SQL Server 2008 R2) tables and I needed to keep them synchronized so I thought to myself, "I'll just write a trigger on each table to update the other table's field, problem solved!", and I went to work.

I wrote the first trigger, tested it, and it worked just like I knew it would; Excellent! I, more or less, copied and pasted the first trigger into a second trigger, wrote it to the database, loaded up my Web app and input some data and watched NOTHING HAPPEN!?

I banged my head on my desk for a few minutes trying to figure out why these simple triggers wouldn't work together (especially when one by itself worked just fine) and then it dawned on me; They're canceling each other out!!

And enter my savior TRIGGER_NESTLEVEL.

As Microsoft will tell you, TRIGGER_NESTLEVEL "returns the number of triggers executed for the statement that fired the trigger to determine the current level of nesting." In other words, you can find out how many triggers have consecutively fired! And, even more spectacularly, you can specify an object id with TRIGGER_NESTLEVEL so you can find out how many times a particular trigger has fired. So, with a quick addition to my if statement, I was able to determine if the calling trigger had fired more than once:

if TRIGGER_NESTLEVEL(OBJECT_ID(N'[dbo].[trigger_1]')) < 2
begin
-- do my syncrhonization work
end

And that's it! If trigger_1 was firing a second time, trigger_2 didn't do anything. And, of course, I added the same logic to trigger_1 to prevent execution if trigger_2 was firing more than once. MAGIC!

So there you have it. I hope this helps somone as much as it helped me!