将更新触发器与事务复制结合使用时遇到错误
我正在使用带有可更新订阅的事务复制。 当我在发布者中选择复制的一个表中添加更新触发器时，我遇到此错误：
Maximum stored procedure, function,trigger,
or view nesting level exceeded(limit 32)
我的触发器代码出了
create trigger Isupdated
on tbl_worker
for update as
update tbl_worker SET
Isup=1
where id= (select id from inserted)
什么问题？
I'm using transactional replication with updatable subscription.
when I add a trigger for update in one of my tables which is chosen for replication in publisher, I encounter with this error:
Maximum stored procedure, function,trigger,
or view nesting level exceeded(limit 32)
My trigger code is
create trigger Isupdated
on tbl_worker
for update as
update tbl_worker SET
Isup=1
where id= (select id from inserted)
what's wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您似乎编写了一个递归（又名嵌套）触发器。
也许触发器正在导致表上的更新，从而导致触发器再次被触发？
如果您发布代码，这将帮助我们准确解释问题所在。
It looks like you've written a recursive (aka nested) trigger.
Perhaps the trigger is causing an update on the table, causing the trigger to be fired again?
If you post the code, that would help us explain exactly what the issue is.
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/4752/Maximum-stored-procedure-function-trigger-or-view
上面的链接提供了触发嵌套的解决方案。当您已经有一个触发器，然后复制添加另一个触发器时，这会非常有帮助。我比组合触发器更喜欢这个，因为它不会强迫您混合与功能相关的代码和与复制相关的代码。
总结一下解决方案：
您可以通过分配触发器来防止发生嵌套触发
使用 sp_settriggerorder 的订单并将以下检查添加到
您设置为首先触发的触发器的开头，以防止它被
另一个触发器：
创建触发器......
如果 TRIGGER_NESTLEVEL > 1 次退货
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/4752/Maximum-stored-procedure-function-trigger-or-view
The above link provides a solution to trigger nesting. This can be very helpful when you already have a trigger and then replication adds another one. I like this more than combining the triggers because it doesn't force you to mix code related to functionality and code related to replication.
To sum up the solution:
You can prevent the nested firing from happening by assigning the triggers
an order with sp_settriggerorder and add the following check to the
beginning of the trigger you set to fire first to prevent it being fired by
the other trigger:
CREATE TRIGGER....
IF TRIGGER_NESTLEVEL > 1 RETURN