I have this scheme. I have 2 tables A and B. I made triggers TA and TB. At changing table A run trigger TA and changing table B. At changing table B run trigger TB and changing table A. When I change A run TA and certainly TB, but code in TB raises an error. The triggers run cyclic. In my scheme I need there is not the cycle and the triggers run only one direction.
How can I organize it?
Best Regards
Temur
Take a look here:
You'd want to add a TRIGGER_NESTLEVEL check in both triggers, anything greater than 1 should abort/RETURN from the trigger. It should probably be the first statement in the trigger.
Edit: Or, don't have a trigger on one of the tables. Without extra detail of what you're trying to do I can't really advise any better.
Thanks for answer. I have SQL Server 2008. I used @@nestlevel. I put if @@nestlevel>3 return; It worked for me.
Examining TRIGGER_NESTLEVEL as @robert_volk suggested would be more future-proof than using @@nestlevel. If the calling patterns of your code change, value returned from @@nestlevel can change. This is because @@nestlevel measures nesting level of the current stored procedure rather than the nesting level of the trigger itself.
Edit: I realized you don't have TRIGGER_NESTLEVEL in your version of SQL Server. So perhaps using @@nestlevel is the best you can do.
I am sorry, I was desoriented. First I thought there was not trigger_nestlevel in SQL Server 2008 but there was. Moreover, it seems to me using @@nestlevel is easier. I will of course study using trigger_nestlevel better. Thank you for your recommendation.