用于复制的系统触发器和 NOCOUNT 选项
在 Sql Server 2008 中设置复制时，系统会在每个复制表上创建触发器，以便于将数据传输到订阅数据库。
系统生成的触发器是否会使用 SET NOCOUNT ON 来防止其操作影响 @@ROWCOUNT 函数的值？
背景
我正在将 NHibernate 与 Sql Server 2008 后端数据库一起使用。数据库启用了复制，它在已发布的表上创建系统生成的触发器，以方便将数据传输到订阅者。
如果没有复制，一切都可以正常工作，但是有了复制，NHibernate 受影响的行验证检查就会失败。 ADO.Net 表示受影响的行数似乎包括受关联触发器影响的行以及受命令本身影响的行。
在最初的问题中，我提到了 @@ROWCOUNT Sql Server 函数，但我真正的意思是 ADO.Net ExecuteNonQuery 方法返回的“受影响的行”值。我想我正在假设（可能是错误的）后者是由前者提供的。
作为临时解决方案，我们修改了复制触发器，在任何更新之前添加SET NOCOUNT ON，并在任何更新完成后反转此操作。这暂时解决了我们的问题，但不是一个可行的永久解决方案。根据我们收到的建议和常识，不建议编辑系统触发器。
然而，这确实表明我们已经确定了确切的问题。受触发器影响的行将包含在当前命令的最终受影响行数中。 NHibernate 预计只有已知数量的行受到影响，它不会为任何未知（就 NHibernate 而言）触发器添加到此计数中。
我们目前正在研究涉及扩展 NHibernate 以添加处理或至少抑制此问题的功能的选项。我们的资源包括这个SO问题。
我还发现 这篇文章似乎表明系统生成的触发器已经默认将 NOCOUNT 选项设置为 ON，无需修改它们。对于我们来说绝对不是这种情况，所以我想知道为什么会这样。
- 系统生成的触发器的默认情况是什么？
- 行为是否可配置？
- 它是否取决于复制类型——合并、事务？
When setting up replication in Sql Server 2008 the system creates triggers on every replicated table in order to facilitate the transfer of data to the subscribing databases.
Would it be expected behaviour for system generated triggers to use SET NOCOUNT ON to prevent their actions affecting the value of the @@ROWCOUNT function?
Background
I am using NHibernate with a Sql Server 2008 backend database. The database has replication enabled which creates system generated triggers on the published tables to facilitate the data transfer to the subscribers.
Without the replication everything works fine, but with the replication NHibernate’s rows affected verification checks fail. The number of rows that ADO.Net says it has affected appears to include the rows affected by the associated trigger in addition to the rows affected by the command itself.
In the original question I mentioned the @@ROWCOUNT Sql Server function, but what I really mean is the “rows affected” value returned by the ADO.Net ExecuteNonQuery method. I suppose I was working on the assumption (possibly incorrectly) that that latter was fed by the former.
As a temporary solution we have modified the replication triggers to add the SET NOCOUNT ON before any updates and to reverse this after any updates are complete. This solves our problem for the time being, but is not a viable permanent solution. According to advice we have received and common sense, editing the system triggers is not recommended.
This does however suggest to me that we have identified the exact problem. The rows affected by the triggers are getting included in the final rows affected count for the current command. NHibernate expects only a known number of rows to be affected, it doesn’t provision for any unknown (as far as NHibernate is concerned) triggers adding to this count.
We are currently in the process of investigating options that involve extending NHibernate to add capability to deal with or at least suppress this. Our resources include this SO question.
I have also found this post that seems to suggest that system generated triggers would already set the NOCOUNT option to ON by default, negating the need to modify them. This is definitely not the case for us, so I was wondering why this might be.
- What is the default situation with regard to the system generated triggers?
- Is the behaviour configurable?
- Does it depend on the type of replication – merge, transactional?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我一直无法确定原始问题的答案 - （应该/可以/如何）Sql Server 系统生成的复制触发器自动包含 SET NOCOUNT ON 选项。
我们的 DBA 团队认为答案是否定的。他们还说（非常正确）他们不会在生产中自定义系统触发器。
留给我的选项是...
TooManyRowsAffectedException。我们选择了选项 1。
我的解决方案是按以下方式修改 NHibernate (2.1) Core...
TooManyRowsAffectedException。我对任何建议的替代实现感兴趣。
I have not been able to determine the answer to the original question - (should/can/how) Sql Server system generated replication triggers automatically include the SET NOCOUNT ON option.
Our DBA team suggest the answer is no. They also say (quite rightly) that they will not customise system triggers in production.
Options left open to me are...
TooManyRowsAffectedExceptionwhen a row count mismatch is detected.We have chosen option 1.
My solution was to modify the NHibernate (2.1) Core in the following way...
VerifyOutcomeNonBatchedandVerifyOutcomeBatchedmethods from theNHibernate.AdoNet.Expectationsclass to accept the new configuration value as a parameter. This involved modifying several places where these methods are called.TooManyRowsAffectedExceptionaccording to the config option.I would be interested in any suggested alternative implementations.