Disallow results from triggers?

At a meeting I was at a few months back, it was noted that in a future version of SQL Server, the intention was to disallow results being returned from triggers. That surprised me somewhat.

My main concern with this was about my ability to debug triggers. I often seem to end up working on systems where people have layers of nested triggers. (Perhaps I’m just lucky that way). Usually I have to end up using a combination of PRINT statements and writing values into tables to work out what’s going on in the triggers. And given that the action of the triggers may well be rolled-back, writing to a table is often not a simple option if you want the values to still be there later.

So I’m often back to using PRINT statements. I was concerned that these might not work either. As an example, if we set up a table to use for testing:

USE tempdb;

GO

CREATE TABLE t

( c1 int IDENTITY(1,1) PRIMARY KEY,

  c2 varchar(20) NULL

);

GO

CREATE TRIGGER t_Insert ON t FOR INSERT

AS

  SELECT * FROM inserted;

GO

If we now attempt an insert on the table, this is the outcome:

INSERT INTO t (c2) VALUES(‘Hello’),(‘There’);

GO

c1          c2

-———- ——-

2           There

1           Hello

If we now enable the option, this is the outcome:

sp_configure ‘disallow results from triggers’,1;

GO

RECONFIGURE;

GO

INSERT INTO t (c2) VALUES(‘Hello’),(‘There’);

GO

Msg 524, Level 16, State 1, Procedure t_Insert, Line 4

A trigger returned a resultset and the server option ‘disallow results from triggers’ is true.

If we try using PRINT, instead of SELECT in the trigger, we can see the result:

ALTER TRIGGER t_Insert ON t FOR INSERT

AS

  PRINT ‘Hello’;

GO

INSERT INTO t (c2) VALUES(‘Hello’),(‘There’);

GO

Hello

(2 row(s) affected)

Note that it still works. What is not allowed is returning rowsets from the trigger. That’s probably fair enough as resultsets coming back from triggers can cause unexpected results in client applications.

In Books Online, it is recommended that you set this value to 1 as the ability to return results from triggers will likely be removed from the product soon. If you strongly oppose that though, now would be a really good time to make it known to the product group via http://connect.microsoft.com .

2009-02-05