托管 SQL 触发器递归
我有一个分配有触发器的表。并且这个触发器改变了相同的表数据。当然，这会启动一个新的触发器。
每个触发器实例都知道（有一些规则），它是否是链中的最后一个。如果应该的话，它必须关闭下一个触发器。
我看到以下问题：如果我有一个状态（例如，停止标志），它可能会以意想不到的方式工作。例如，用户更改表。一个新的触发链正在启动。触发器想要成为终结者并设置停止标志。此时另一个用户更改了表 =>正在启动一个新的触发链，应该执行该触发链。但是，当设置停止标志时，它会清除该标志并退出。现在，启动递归触发器（我们认为是被忽略的），查看标志是否被清除......哎呀，它被执行了！
我不知道，在这种情况下的顺序是什么，是在更改数据后立即执行递归触发器还是先完成父级触发器，所以我不知道如何组织这个过程。
问候，
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 flag 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,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
考虑放弃复杂的触发器并将所有内容简化为存储过程，或者如果可能的话，简化为基于标准 SQL 集的操作。
存储过程比给定表上的多层触发器更容易理解和维护。触发器在某些情况下确实有价值，但是当您拥有调用触发器链的触发器，或者拥有依赖于从其他触发器修改的数据的触发器，并且所有触发器都在同一个表上时，那么您真的开始给自己带来维护噩梦。通过改进 SQL 更新/插入语句或将触发器重构为某种存储过程来简化作为起点。
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.