{"id":82054,"date":"2009-06-06T22:38:06","date_gmt":"2009-06-06T22:38:06","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73245"},"modified":"2018-12-12T13:30:25","modified_gmt":"2018-12-12T13:30:25","slug":"disallow-results-from-triggers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/disallow-results-from-triggers\/","title":{"rendered":"disallow results from triggers"},"content":{"rendered":"<p>A setting that I noticed a while back when looking at sys.configurations was <strong>disallow results from triggers.<\/strong>&#160; 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: <a title=\"http:\/\/sqlblog.com\/blogs\/louis_davidson\/archive\/2008\/07\/13\/triggers-evil.aspx\" href=\"https:\/\/www.webstaging.red-gate.com\/simple-talk\/uncategorized\/triggers-evil\/\">http:\/\/sqlblog.com\/blogs\/louis_davidson\/archive\/2008\/07\/13\/triggers-evil.aspx<\/a>). <\/p>\n<p>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\u2019t know about.&#160; There are three kinds of return values that are interesting:<\/p>\n<ul>\n<li>Result sets <\/li>\n<li>Raiserror messages <\/li>\n<li>Rowcount messages <\/li>\n<\/ul>\n<p>Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases.&#160; It is never a good idea to return data from a trigger, but it is actually pretty useful for testing.&#160; In the following code, I will test this setting and show how it will affect returning data to the client from the aforementioned methods.&#160; <\/p>\n<p>What will the setting do? It will raise an error if you try to do a result set.&#160; It will automatically suppress rowcount messages (without the need for SET NOCOUNT ON), but will allow error messages (I won\u2019t cover it here, but error messages can cause issues with transactions in triggers, particularly when coupled with TRY\u2026CATCH blocks).<\/p>\n<p>To demonstrate the setting, I will use tempdb.&#160; Checking the system setting for your server, use:<\/p>\n<p>SELECT value      <br \/>FROM&#160;&#160;&#160; sys.configurations       <br \/>WHERE name = &#8216;disallow results from triggers&#8217; <\/p>\n<p>This will probably return, unless you have discovered the setting before I did, in which case you probably aren\u2019t reading any longer, so there.<\/p>\n<p>&#8212;&#8212;&#8212;    <br \/>0 <\/p>\n<p>To show you the effect of this setting , let&#8217;s build the following scenario. The trigger will return 3 types of values<\/p>\n<ul>\n<li>the contents of the table named triggerResult <\/li>\n<li>the rowcount of rows affected from an insert statement like you would normally have in a database <\/li>\n<li>the rowcount of the rows from the insert statement into a temp table <\/li>\n<\/ul>\n<p>To start, we create 2 tables, one as the \u201cmain\u201d table, and another that will hold the results of a side effect causing query:<\/p>\n<p>&#8211;primary test table      <br \/>create table triggerResult       <br \/>(       <br \/>&#160;&#160;&#160; triggerResultId int primary key       <br \/>)&#160;&#160;&#160; <br \/>&#8211;holds our side effect to prove the trigger executed       <br \/>create table triggerResultSideEffect       <br \/>(       <br \/>&#160;&#160;&#160; triggerResultId int       <br \/>)<\/p>\n<p>Then we will create the trigger that gives us several different types of output    <\/p>\n<p>create trigger triggerResult$insertTrigger      <br \/>on triggerResult       <br \/>after insert       <br \/>as       <br \/>begin       <br \/>&#160;&#160;&#160; &#8211;returns a result set       <br \/>&#160;&#160;&#160; select triggerResultId       <br \/>&#160;&#160;&#160; from&#160;&#160; triggerResult<\/p>\n<p>&#160;&#160;&#160; &#8211;side effect like you might expect in a trigger      <br \/>&#160;&#160;&#160; insert into triggerResultSideEffect (triggerResultId)       <br \/>&#160;&#160;&#160; select triggerResultId       <br \/>&#160;&#160;&#160; from&#160;&#160; inserted<\/p>\n<p>&#160;&#160;&#160; &#8211;just to get a fixed rowcount output      <br \/>&#160;&#160;&#160; declare @test table (value char(1))       <br \/>&#160;&#160;&#160; insert into @test       <br \/>&#160;&#160;&#160; values (1)       <\/p>\n<p>&#160;&#160;&#160; &#8211;and a couple of errors      <br \/>&#160;&#160;&#160; raiserror (&#8216;Low&#8217;,10,1)       <br \/>&#160;&#160;&#160; raiserror (&#8216;Normal&#8217;,16,1) <\/p>\n<p>end      <br \/>Now we will test out the trigger by inserting one row into the triggerResult table:<\/p>\n<p>insert into triggerResult      <br \/>values (1)<\/p>\n<p>This returns (the final rows affected is from the original statement): <\/p>\n<p>triggerResultId      <br \/>&#8212;&#8212;&#8212;&#8212;&#8212;       <br \/>1 <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>Low      <br \/>Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22       <br \/>Normal <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>Now try a multi-row operation, to see the difference <\/p>\n<p>insert into triggerResult      <br \/>values (2),(3),(4)<\/p>\n<p>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.<\/p>\n<p>triggerResultId      <br \/>&#8212;&#8212;&#8212;&#8212;&#8212;       <br \/>1       <br \/>2       <br \/>3       <br \/>4 <\/p>\n<p>(4 row(s) affected) <\/p>\n<p>(3 row(s) affected) <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>Low      <br \/>Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22       <br \/>Normal <\/p>\n<p>(3 row(s) affected) <\/p>\n<p>Now, change the setting to disallow trigger results (you may need to do allow advanced options) <\/p>\n<p>exec sp_configure &#8216;show advanced options&#8217;,1      <br \/>RECONFIGURE       <br \/>exec sp_configure &#8216;disallow results from triggers&#8217;,1       <br \/>RECONFIGURE<\/p>\n<p>Now, just to make sure of where our data is, we know that there are 4 rows in the triggerResult table. Let\u2019s check the triggerResultSideEffect table contents:<\/p>\n<p>SELECT *      <br \/>FROM&#160;&#160; triggerResultSideEffect       <br \/>ORDER&#160; BY triggerResultId <\/p>\n<p>This returns, showing all of the values we have inserted: <\/p>\n<p>triggerResultId      <br \/>&#8212;&#8212;&#8212;&#8212;&#8212;       <br \/>1       <br \/>2       <br \/>3       <br \/>4 <\/p>\n<p>Now, trying to run the statement with the same trigger:<\/p>\n<p>insert into triggerResult      <br \/>values (5)<\/p>\n<p>This will cause the following error message: <\/p>\n<p>Msg 524, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 6      <br \/>A trigger returned a resultset and the server option &#8216;disallow results from triggers&#8217; is true. <\/p>\n<p>Altering the trigger, just remove the statement that returns a result set:<\/p>\n<p>alter trigger triggerResult$insertTrigger      <br \/>on triggerResult       <br \/>after insert       <br \/>as       <br \/>begin       <br \/><strong><em>&#160;&#160;&#160; &#8212;-returns a result set          <br \/>&#160;&#160;&#160; &#8211;select triggerResultId           <br \/>&#160;&#160;&#160; &#8211;from&#160;&#160; triggerResult           <br \/>&#160;&#160;&#160; &#8211;side effect like you might expect in a trigger<\/em><\/strong>&#160;<\/p>\n<p>&#160;&#160;&#160; insert into triggerResultSideEffect (triggerResultId)      <br \/>&#160;&#160;&#160; select triggerResultId       <br \/>&#160;&#160;&#160; from&#160;&#160; inserted <\/p>\n<p>&#160;&#160;&#160; &#8211;just to get a fixed rowcount output      <br \/>&#160;&#160;&#160; declare @test table (value char(1))       <br \/>&#160;&#160;&#160; insert into @test       <br \/>&#160;&#160;&#160; values (1) <\/p>\n<p>&#160;&#160;&#160; &#8211;and a couple of errors      <br \/>&#160;&#160;&#160; raiserror (&#8216;Low&#8217;,10,1)       <br \/>&#160;&#160;&#160; raiserror (&#8216;Normal&#8217;,16,1) <\/p>\n<p>end <\/p>\n<p>Now, re-executing the statement with no results being returned: <\/p>\n<p>insert into triggerResult      <br \/>values (5) <\/p>\n<p>This simply returns the error message that are returned, and the rows affected message from the insert statement: <\/p>\n<p>Low      <br \/>Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22       <br \/>Normal <\/p>\n<p>(1 row(s) affected) <\/p>\n<p>Just to make sure that the rows were created:<\/p>\n<p>select *      <br \/>from&#160;&#160; triggerResult <\/p>\n<p>select *      <br \/>from&#160;&#160; triggerResultSideEffect <\/p>\n<p>This returns: <\/p>\n<p>triggerResultId      <br \/>&#8212;&#8212;&#8212;&#8212;&#8212;       <br \/>1       <br \/>2       <br \/>3       <br \/>4       <br \/>5 <\/p>\n<p>(5 row(s) affected) <\/p>\n<p>triggerResultId      <br \/>&#8212;&#8212;&#8212;&#8212;&#8212;       <br \/>1       <br \/>4       <br \/>3       <br \/>2       <br \/>5 <\/p>\n<p>(5 row(s) affected) <\/p>\n<p>Which shows that the data was inserted..<\/p>\n<p>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. <\/p>\n<p>This means that the ability to return result sets from a trigger will go away in the next version of SQL Server.&#160; 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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers.&#160; 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&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82054","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82054","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82054"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82054\/revisions"}],"predecessor-version":[{"id":82346,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82054\/revisions\/82346"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82054"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}