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
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 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 */
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 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,
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?
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.
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.