Jebarson's dev blog

Coffee, Code

Working around the bug “Instead Of Trigger on View returns no output”

By JebarsonPosted on Posted in SQLNo Comments on Working around the bug “Instead Of Trigger on View returns no output”

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.

The 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.

  1. Table “Employee” has two columns “EmployeeId” which is an identity column and “Name”.
  2. The view “ViewEmployee” encapsulates the table with no logic within.
  3. 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:

EmployeeId Name
0 Jebarson
0 Scott

Output from select query:

EmployeeId Name
1 Jebarson
2 Scott

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.

The Workaround

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.

Working around the bug “Instead Of Trigger on View returns no output”

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top