Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0158-SQL Server-Returning result sets from triggers

May 7 2012 12:00AM by Nakul Vachhrajani   

This week, I am taking a brief break from my series on deprecated features of SQL Server 2012 to share with you two experiences I had recently. A developer had suspected that one of the triggers in the database had an issue. To debug this, the developer placed a simple SELECT statement within the trigger.

Below is an example reproducing the scenario for demonstration purposes.

--Use tempdb for demonstration purposes
USE tempdb
GO

--Create a demo table
CREATE TABLE dbo.TriggerResultSets (IdCol INT IDENTITY(1,1),
                                    IdName VARCHAR(50)
                                   )
GO

--Create a trigger that returns a result set
CREATE TRIGGER dbo.trig_TriggerResultSets
ON dbo.TriggerResultSets
FOR INSERT
AS
BEGIN
    /*Some business logic here*/
    SELECT INSERTED.IdCol, INSERTED.IdName
    FROM [INSERTED]
    ORDER BY INSERTED.IdCol
END
GO

Upon inserting some data into our demo table, TriggerResultSets, the developer could see the data that was inserted.

--Insert some test data
INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('Microsoft'),
       ('SQLServer')
GO

image

The Problem

After resolving the issue that the developer had encountered, the team proceeded to unit test the application, which failed. This was because the SELECT statement was left behind, and the application was not written to handle result sets from triggers.

While the ultimate solution to prevent such a situation in the future is to have proper code review checklists, techniques and methods that would help ensure such code never gets “deployed”, there are workarounds for which developers can be educated for, and a check that all database administrators can use to trap any fall-through occurrences.

The Workaround

The workaround is actually quite simple. Developers can be educated to use PRINT statements instead of the SELECT statements. Because PRINT does not return result sets, the application will remain “happy” and at work as expected. Although, please note that I am not very much in favour of this because data is being exposed here unnecessarily.

--CREATE TABLE statement removed for sake of brevity.

--Create a trigger that returns a result set
CREATE TRIGGER dbo.trig_TriggerResultSets
ON dbo.TriggerResultSets
FOR INSERT
AS
BEGIN
    /*Some business logic here*/
    DECLARE @InsertedId INT
    DECLARE @InsertedName VARCHAR(50)

    SELECT @InsertedId = inserted.IdCol, @InsertedName = inserted.IdName
    FROM [INSERTED]
    ORDER BY INSERTED.IdCol
     
    PRINT 'Id = ' + CAST(@InsertedId AS VARCHAR(10)) + ', Name = ' + ISNULL(@InsertedName,'')
END
GO

INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('SQLServer')
GO

/*
Result Set:
Id = 1, Name = SQLServer
*/

image

The Check

Despite the proper developer education, multiple checklists and code reviews, it is quite possible that sometimes things may slip through. In such cases (or until the developer education does not complete), the DBA may need to take extreme steps such as completely disabling functionalities that may cause problems. In the case I am referring to, I turned ON a configuration option - disallow results from triggers. Please note that this is an advanced option.

sp_configure 'show advanced options',1
RECONFIGURE
GO

sp_configure 'disallow results from triggers',1
RECONFIGURE
GO

Once turned ON, the original code with the SELECT statement would fail.

INSERT INTO dbo.TriggerResultSets (IdName)
VALUES ('Microsoft'),
       ('SQLServer')
GO

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

PLEASE NOTE

Please note that the “disallow results from triggers” option has been marked as deprecated from SQL Server 2008 onwards and now that SQL Server 2012 is out, it will be removed from the next version of SQL Server. Future versions of SQL Server will not support returning result sets from triggers and hence the switch is no longer required.

Per Microsoft’s recommendation, I have turned the switch to 1 in my environments.

Read more about this announcement here: http://msdn.microsoft.com/en-us/library/ms186337(v=sql.110).aspx

Until we meet next time,

Be courteous. Drive responsibly.

Tags: #SQLServer, SQL Server, Administration, Development, DBA, Tips, #TSQL, T-SQL


Nakul Vachhrajani
4 · 36% · 11635
8
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

3  Comments  

  • Nice article Nakul. I have faced similar issue in past and we commented the SELECT statement to resolve the issue.

    If developer request for some data as per trigger execution, we should create/use table with XML column and insert the required data in it. We just need to parse the required tag/attribute whenever required. Due to XML column, it can be easily extendable. What you say?

    commented on May 7 2012 12:46AM
    Hardik Doshi
    20 · 9% · 2863
  • Agree with you, Hardik. If there absolutely a need to return results from a trigger (a trigger, to me, is "fire-and-forget", i.e. applications should not be dependent upon something being returned from a trigger), then an XML column on the table (or on another related auditing table) is the way to go.

    commented on May 7 2012 12:44PM
    Nakul Vachhrajani
    4 · 36% · 11635
  • If you need to get information out of an INSERT, UPDATE or DELETE statement, use an OUTPUT or OUTPUT INTO clause. It will basically return the contents of the [inserted] or [deleted] virtual tables.

    Or you can insert a result set into a log table, or use a SQLCLR function to write data to a log text file. Do not allow end-users access to the log table or log text file and the solution remains secure.

    There are any number of options, depending on your needs.

    commented on May 8 2012 5:46AM
    Marc Jellinek
    95 · 2% · 586

Your Comment


Sign Up or Login to post a comment.

"#0158-SQL Server-Returning result sets from triggers" rated 5 out of 5 by 8 readers
#0158-SQL Server-Returning result sets from triggers , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]