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
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
*/
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.
SQLTwins by Nakul Vachhrajani 