disallow results from triggers

drsql — Sun, 07 Jun 2009 02:38:06 GMT

A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers. Triggers are one of my favorite subjects, and you will find a lot of good uses of them in my book (triggers are also well named, as poor usage of them will allow you to shoot your foot off in a heartbeat as I discussed in my earlier post: http://sqlblog.com/blogs/louis_davidson/archive/2008/07/13/triggers-evil.aspx).

One of the main ways you can mess up your system is to have triggers that return results, often by accident, that the client doesn’t know about. There are three kinds of return values that are interesting:

Result sets
Raiserror messages
Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases. It is never a good idea to return data from a trigger, but it is actually pretty useful for testing. In the following code, I will test this setting and show how it will affect returning data to the client from the aforementioned methods.

What will the setting do? It will raise an error if you try to do a result set. It will automatically suppress rowcount messages (without the need for SET NOCOUNT ON), but will allow error messages (I won’t cover it here, but error messages can cause issues with transactions in triggers, particularly when coupled with TRY…CATCH blocks).

To demonstrate the setting, I will use tempdb. Checking the system setting for your server, use:

SELECT value
FROM sys.configurations
WHERE name = 'disallow results from triggers'

This will probably return, unless you have discovered the setting before I did, in which case you probably aren’t reading any longer, so there.

---------
0

To show you the effect of this setting , let's build the following scenario. The trigger will return 3 types of values

the contents of the table named triggerResult
the rowcount of rows affected from an insert statement like you would normally have in a database
the rowcount of the rows from the insert statement into a temp table

To start, we create 2 tables, one as the “main” table, and another that will hold the results of a side effect causing query:

--primary test table
create table triggerResult
(
    triggerResultId int primary key
)
--holds our side effect to prove the trigger executed
create table triggerResultSideEffect
(
    triggerResultId int
)

Then we will create the trigger that gives us several different types of output

create trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    --returns a result set
    select triggerResultId
    from   triggerResult

    --side effect like you might expect in a trigger
    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end
Now we will test out the trigger by inserting one row into the triggerResult table:

insert into triggerResult
values (1)

This returns (the final rows affected is from the original statement):

triggerResultId
---------------
1

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Now try a multi-row operation, to see the difference

insert into triggerResult
values (2),(3),(4)

This returns the following (which you can clearly now see the different number of rows that were affected by each statement, 4 from the one that returns all of the rows in the table, 3 from the side effect query that inserts the rows from the inserted table.

triggerResultId
---------------
1
2
3
4

(4 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(3 row(s) affected)

Now, change the setting to disallow trigger results (you may need to do allow advanced options)

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'disallow results from triggers',1
RECONFIGURE

Now, just to make sure of where our data is, we know that there are 4 rows in the triggerResult table. Let’s check the triggerResultSideEffect table contents:

SELECT *
FROM triggerResultSideEffect
ORDER BY triggerResultId

This returns, showing all of the values we have inserted:

triggerResultId
---------------
1
2
3
4

Now, trying to run the statement with the same trigger:

insert into triggerResult
values (5)

This will cause the following error message:

Msg 524, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 6
A trigger returned a resultset and the server option 'disallow results from triggers' is true.

Altering the trigger, just remove the statement that returns a result set:

alter trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    ----returns a result set
    --select triggerResultId
    --from   triggerResult
    --side effect like you might expect in a trigger

    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end

Now, re-executing the statement with no results being returned:

insert into triggerResult
values (5)

This simply returns the error message that are returned, and the rows affected message from the insert statement:

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Just to make sure that the rows were created:

select *
from triggerResult

select *
from triggerResultSideEffect

This returns:

triggerResultId
---------------
1
2
3
4
5

(5 row(s) affected)

triggerResultId
---------------
1
4
3
2
5

(5 row(s) affected)

Which shows that the data was inserted..

Note that books online states: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

This means that the ability to return result sets from a trigger will go away in the next version of SQL Server. Returning results from a trigger is certainly not something that has ever been a good practice, so if this does in fact occur, it is a good setting to set in your development servers and begin to use.

Search results matching tags 'T-SQL', 'Development', 'Tips', 'Best Practices', and 'SQL Techniques'

disallow results from triggers