There is an inherent and documented bug with SQL for a very long time where the database will not yield any output when you have got an “Instead Of” trigger on a view and you update the view. The bug is currently updated as “Won’t fix” as the fix will alter the core bits of the engine. I confirmed this recently with the team and the state remains unchanged. In this post we will discuss on how to work around this bug.
Before getting into the workaround, lets see what exactly the bug is about.
Lets consider below situation and for the sake of sample we will keep it simple.
- Table “Employee” has two columns “EmployeeId” which is an identity column and “Name”.
- The view “ViewEmployee” encapsulates the table with no logic within.
- The view now poses an “Instead Of” trigger which just inserts into the table “Employee”.
Below is the schema of the same.
-- Employee Table CREATE TABLE Employee ( EmployeeId INT IDENTITY(1, 1) CONSTRAINT PKEmployee PRIMARY KEY , Name NVARCHAR(200) ); GO -- View Employee CREATE VIEW ViewEmployee AS SELECT EmployeeId , Name FROM dbo.Employee; GO -- Instead Of Insert trigger on ViewEmployee CREATE TRIGGER [dbo].[TrgViewEmployeeInsteadOfInsert] ON [dbo].ViewEmployee INSTEAD OF INSERT AS SET NOCOUNT ON; INSERT INTO dbo.Employee ( Name ) SELECT inserted.Name FROM inserted; GO
Now lets write a procedure which will insert multiple employees into the table in a transaction. Since the id is an identity column, we will rely on SQL to know the id it generated for the employee and therefore once the records are inserted we will get back the “id” and the corresponding “name” of the employee.
-- Type to hold multiple names CREATE TYPE UdtEmployee AS TABLE (Name NVARCHAR(200)); GO -- Procedure to create new employees CREATE PROCEDURE CreateEmployees ( @employees UdtEmployee READONLY ) AS BEGIN CREATE TABLE #tmpEmployees ( EmployeeId INT , Name NVARCHAR(200) ); INSERT INTO dbo.ViewEmployee ( Name ) OUTPUT Inserted.EmployeeId, Inserted.Name INTO #tmpEmployees SELECT Name FROM @employees; SELECT EmployeeId , Name FROM #tmpEmployees; END; GO
If you pay attention to the procedure, while we are inserting the records to the view, we are also getting back the id and the name of the employees to store in to the temp table and finally as a result. So lets now put the procedure to test.
DECLARE @emps UdtEmployee; INSERT INTO @emps VALUES('Jebarson') INSERT INTO @emps VALUES('Scott') EXEC CreateEmployees @emps; SELECT * FROM Employee
Below is what the result looks like.
Output from procedure:
Output from select query:
As you can see that the procedure couldn’t return identity values of the inserted rows. This is due to the underlying bug filed in Connect Portal. To understand as to why it happens, read Eugene’s comment. We are not going to discuss that any further as he has given a comprehensive reason to understand the same.
Now lets talk about the workaround we can put in place. The problem we are dealing here is that the value which was generated by SQL while in the scope of trigger is lost when we are trying to access the same at the procedure level. So what we need is a mechanism where we can store the value (identities) somewhere and retrieve it at the procedure level. However, it should be transaction and session safe.
CONTEXT_INFO does the same what we need at the moment. I will not explain what it does here and will leave that to you to read it over msdn.
Solution 1: We will save the identities / columns in the Context_Info and then retrieve the same in the procedure. However, Context_Info can store only 128 bytes which will cease to work when you have multiple rows getting saved in an operation. With respect to our situation, it may work if we are saving only one record every time. But, it will break the moment we allow to save multiple records. The solution is simple and you can opt for this if you know for a fact that your column size is not going to exceed 128 bytes and also your queries are not going to insert multiple rows in a shot.
Solution 2: The workaround here also involves the context however, a little more sophisticated than what the last solution proposes. In the trigger we will create a GUID and save it in the context and use the same GUID to save the actual values into a global temporary or physical table and then retrieve that value in the procedure.
Lets put the solution on the ground.
I am going to create procedure so that I can port it into any trigger I want to implement this.
CREATE PROCEDURE [dbo].[UspCreateContextInfo] ( @list NVARCHAR(MAX) ) AS BEGIN IF OBJECT_ID('GlobalContextInfo') IS NULL BEGIN CREATE TABLE GlobalContextInfo ( Id UNIQUEIDENTIFIER , Value NVARCHAR(MAX) , CreatedOn DATETIME CONSTRAINT [DcGlobalContextInfoCreatedOn] DEFAULT ( GETUTCDATE() ) NOT NULL ); END; -- Create a new GUID for the operation DECLARE @id UNIQUEIDENTIFIER = NEWID(); -- Store the ID and the values into the table INSERT INTO GlobalContextInfo ( Id, Value ) VALUES ( @id, @list ); DECLARE @idStr VARBINARY(128) = CONVERT(VARBINARY(128), CONVERT(NVARCHAR(36), @id)); -- Store the ID into the context SET CONTEXT_INFO @idStr; END;
The procedure implements a physical table which stores a key-value pair tagged with a auto-generated created time; this will serve as an easy way of cleaning up the data. You could use global temp table instead. However, it isn’t supported in Azure yet and with the physical table you will need to worry about its contribution to the log file. So whether it is going to be a physical table or temporary table, it is going to be your choice considering the pros and cons.
Now lets see how the trigger is going to change to implement this workaround.
CREATE TYPE [dbo].[UdtIntList] AS TABLE ( [IntId] BIGINT ) GO CREATE TRIGGER [dbo].[TrgViewEmployeeInsteadOfInsert] ON [dbo].ViewEmployee INSTEAD OF INSERT AS SET NOCOUNT ON; DECLARE @result [dbo].[UdtIntList]; INSERT INTO dbo.Employee ( Name ) OUTPUT inserted.EmployeeId INTO @result SELECT inserted.Name FROM inserted; DECLARE @concatRes NVARCHAR(MAX) = '' -- Concat the result using comma as a separator to store in a single row. SELECT @concatRes = @concatRes + CONVERT(NVARCHAR(100), [@result].[IntId]) + ',' FROM @result EXEC [dbo].[UspCreateContextInfo] @concatRes GO
We have changed the trigger to output the id and then concat them into a comma separated value to serve the purpose of allowing multiple records to be created in our case.
Now that we have saved the ids generated at the trigger, its a matter of just retrieving at the procedure. If you have gone through the documentation of Context_Info, you will find that the information which is set there can retrieved from session / request. We will exactly do the same to get back the result.
CREATE PROCEDURE [dbo].[UspGetCustomScopeIdentity] AS BEGIN BEGIN TRY DECLARE @result NVARCHAR(2000); DECLARE @contextid NVARCHAR(2000); -- Get the GUID we created and stored for the current request SELECT @contextid = CONVERT(NVARCHAR(2000), [sys].[dm_exec_requests].[context_info]) FROM sys.dm_exec_requests WHERE [sys].[dm_exec_requests].[session_id] = @@SPID AND [sys].[dm_exec_requests].[request_id] = CURRENT_REQUEST_ID(); -- Get the values using the GUID from the physical table we created to store the identities. SELECT TOP 1 @result = [Value] FROM GlobalContextInfo WHERE CAST(Id AS NVARCHAR(100)) = @contextid; -- Split the comma separated values and return the result SELECT value FROM STRING_SPLIT(@result, ','); END TRY BEGIN CATCH -- Raise an error with the details of the exception DECLARE @errMsg NVARCHAR(4000) , @errSeverity INT; SELECT @errMsg = ERROR_MESSAGE() , @errSeverity = ERROR_SEVERITY(); RAISERROR(@errMsg, @errSeverity, 1); END CATCH; END;
This procedure is responsible of returning me the newly introduced employee ids. You can also choose to write the same as a function if that pleases you. Do remember that you will need to clean up the table “GlobalContextInfo” on regular intervals; be it a global temporary table or physical table.
Now that we have got the mechanism to store the values and also read them. Its time to put them into action on our procedure and find how it works.
CREATE PROCEDURE CreateEmployees ( @employees UdtEmployee READONLY ) AS BEGIN DECLARE @contextInfo [dbo].[UdtIntList]; INSERT INTO dbo.ViewEmployee ( Name ) SELECT Name FROM @employees; -- Get the identities that are created as a part of above insert. INSERT INTO @contextInfo EXEC [GetCustomScopeIdentity]; SELECT EmployeeId , Name FROM dbo.ViewEmployee WHERE ViewEmployee.EmployeeId IN ( SELECT IntId FROM @contextInfo ); END; GO
The above procedure should work as expected.
As I have mentioned everywhere, this is not a solution but a workaround. The best solution to the problem is to avoid the problem. However, since programming is not always black and white, you may choose this approach as a workaround if you can’t change the design.