在触发器顶部添加 SET NOCOUNT ON [英] Add SET NOCOUNT ON on top of triggers

问题描述

我编写了几个触发器来以这种方式更新多个表的修改时间:

I wrote several triggers to keep modification times of several tables updated in this fashion:

CREATE TABLE foo (
    foo_id INT IDENTITY(1, 1) NOT NULL,
    -- [...]

    created_on DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_on DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT foo_pk PRIMARY KEY (foo_id)
);

CREATE TRIGGER foo_trg1 ON foo
FOR UPDATE
AS BEGIN
    UPDATE foo
    SET updated_on = CURRENT_TIMESTAMP
    FROM INSERTED ins
    WHERE foo.foo_id = ins.foo_id;
END;

不管什么原因我现在没有时间调试，我的 PHP 客户端代码在发出一系列查询时触发了一个 SQL 错误，尽管它只发生在一个特定的表中:

For whatever reason I don't have time to debug right now, my PHP client code was triggering a SQL error when issuing a series of queries, although it only happened in one specific table:

一个触发器返回了一个结果集和/或正在以 SET NOCOUNT OFF 运行，而另一个未完成的结果集处于活动状态.

A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active.

我查找了 SET NOCOUNT 的含义 并猜测错误会在我的触发器中启用它，正如它实际发生的那样:

I looked up what SET NOCOUNT means and guessed the error would go enabling it in my trigger, as it actually happened:

CREATE TRIGGER foo_trg1 ON foo
FOR UPDATE
AS BEGIN
    SET NOCOUNT ON; -- Problem solved!

    UPDATE foo
    SET updated_on = CURRENT_TIMESTAMP
    FROM INSERTED ins
    WHERE foo.foo_id = ins.foo_id;
END;

我的问题是:

1. 将它添加到我所有的 updated_on 触发器是否安全?
2. 我是否认为在这种情况下性能提升可以忽略不计?

推荐答案

简短的回答是肯定的，将它添加到顶部的任何地方都不是一个坏主意，但有一个警告，可能或可能不会影响你.

Short answer is yes it's not a bad idea at all to add this at the top everywhere, but with a caveat that may or may not effect you.

如果您将服务器选项设置为零，则在 SSIS 内的事务中使用时，设置 NoCount 可能会产生一些意想不到的副作用.

Setting NoCount on can have some unintended side effects when used within transactions inside SSIS if you have a server option set to zero.

Exec sp_configure 'show advanced options', 1
reconfigure
Exec sp_configure 'disallow results from triggers', 1
reconfigure

允许来自触发器的结果集是一个已弃用的功能，最终将被删除(通过将上述设置设置为 1 开箱即用).

Allowing result sets from triggers is a deprecated feature and eventually will be removed (by having the above setting set to 1 out the box).

如果您对更改服务器范围的设置感到不舒服，并且在 SSIS 包中遇到奇怪的事务错误，请在触发器结束时将 NoCount 设置为 Off.

If you don't feel comfortable with changing the server wide setting AND you're getting odd transaction errors within an SSIS package, Set NoCount to Off at the end of the trigger.

另外请记住，触发器中发生的事情是任何调用过程的一部分，因此如果出于任何原因在触发器中启动的表更改之前将 NoCount 设置为 Off，该值将被重置.

In addition just remember that what happens in the trigger is part of any calling procedure so if for any reason you had set NoCount to Off before the table change that kicked in the trigger, the value will be reset.

这篇关于在触发器顶部添加 SET NOCOUNT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！