THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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.

Published Thursday, February 05, 2009 4:51 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

I'm really opposed to this for one simple reason - it's playing to the lowest common denominator. My guess is the reason that this is being enforced is because they've had too many support calls or have had people complain with people writing triggers that are intended to return a result set to be used elsewhere. Personally I can't conceive of using a trigger that way, and I avoid them like the plague in the first place. But again MS is instead focus not on education but hard-coding into SQL what should be the "proper usage". Which again makes SQL seem like anyone can use it ("It won't let you do the wrong thing!" - I can see the marketing now). Wake up - this is as complex a DBMS as Oracle is now - take the knowledge of the engine seriously.

February 5, 2009 8:25 AM
 

Adam Machanic said:

I used to use rowsets coming back from triggers quite often before the OUTPUT clause was added... I haven't had any reason to use it since then, but I am also not sure that there is no possible use case.  I'm curious as to the reason they want to disable this functionality.  Yes, it may be a bit confusing in some cases, but it's been around for quite some time and as far as I know hasn't caused anyone major issues; it actually seems like something not many people know about.  Seems a tiny issue to focus on when there are so many other important areas that could be addressed...

February 5, 2009 11:40 AM
 

John Paul said:

@Adam

Yes I also agree with you. Microsoft has 1000 things to resolve other than disabling a feature that can be very helpfull sometimes.

December 25, 2010 10:46 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement