CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Álvaro González
Álvaro González

Reputation: 146558

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;

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:

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

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;

My questions are:

  1. Is it safe to just add it to all my updated_on triggers?
  2. Am I right to assume the performance gain will be negligible in this case?

Upvotes: 3

Views: 10199

Answers (1)

Rachel Ambler
Rachel Ambler

Reputation: 1604

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.

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

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).

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.

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.

Upvotes: 3

Related Questions