Managed SQL triggers recursion

I have a table with a trigger assigned to it. And this trigger changes the same table data. Sure, this initiates a new trigger.

Every trigger instance knows (there are some rules), should it be the last one in the chain or not. And if it should, it has to turn the next trigger off.

I see the following problem: if I have a state (say, stop flag), it could work in an unexpected way. For instance, a user changes the table. A new trigger chain is being initiated. The trigger wants to be a terminator and set the stop flag up. In this moment another user changes the table => a new trigger chain is being initiated, that should be executed. But, as the stop fla开发者_运维百科g is set up, it clear the flag and quits. Now, the recursive trigger (which is ignored we think) is started, looking whether the flag is cleared... Oops, it is executed!

I don't know, what is the order in such cases, will the recursive trigger be executed immediately after changing the data or the parent one is completed first, so I have no ideas, how to organize this process.

Regards,

Consider ditching the complicated triggers and simplifying everything into either stored procedures, or if possible, standard SQL set-based operations.

Stored procedures are easier to understand and maintain then many layers of triggers on a given table. Triggers do have value in some scenarios, but when you have triggers that invoke a chain of triggers, or have triggers that have dependencies on data being revised from other triggers, all on the same table, then you really begin to give yourself a maintenance nightmare. Simplify as a starting point by either improving your SQL update / insert statements, or refactor your triggers into a stored procedure of some sort.