Application sometimes crashes because of a trigger's resultset

Question

Our company does trigger-based audit-logging on each relevant database-table to track our application user's changes every time they change data. The concept behind this is simple enough. Say, we have a simple table we want to historize, like this one:

CREATE TABLE Product
(
    ProductId INT Identity(1,1) NOT NULL,
    Name VARCHAR(32) NOT NULL,
    CONSTRAINT pk__product PRIMARY KEY (ProductId)
);

First of all, we create a second table that will contain the full history of every write operation that will be applied to the table above:

CREATE TABLE Product__Hist
(
    Revision INT NOT NULL,--Starts at 1. Will be incremented on every update.
    IsRecordDeleted BIT NOT NULL,--Indicates whether the record still exists or has been deleted
    [User] VARCHAR(8) NOT NULL,--Identifies the user that did the change
    [Timestamp] DATETIME NOT NULL,--Timestamp of the user-activity
    ProductId INT NOT NULL,
    Name VARCHAR(32) NOT NULL,
    CONSTRAINT pk__product__hist PRIMARY KEY CLUSTERED (ProductId ASC, Revision DESC)
);

And last but not least we need the trigger on the table «Product» that will write data into the history-table when needed. The trigger will be called on any kind of data modification, such as insert, update and delete:

CREATE TRIGGER tr__product__a__iud ON Product
AFTER INSERT, UPDATE, DELETE
AS
DECLARE
    @productid INT,
    @revision INT,
    @user VARCHAR(8),
    @name VARCHAR (32)
DECLARE
    @usercontext TABLE (Usr VARCHAR(8))
BEGIN
    INSERT INTO @usercontext (Usr)
    EXEC dbo.GetContextInfo
    SELECT @user = Usr FROM @usercontext--Retrieves user's identity

    IF EXISTS(SELECT * FROM INSERTED)--INSERT OR UPDATE
    BEGIN
        DECLARE crs CURSOR FOR
            SELECT ProductId, Name  FROM inserted;
        OPEN crs
        FETCH NEXT FROM crs INTO @productid, @name
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT @revision = COALESCE((SELECT Max(Revision) FROM Product__Hist WHERE ProductId = @productid),0)+1
                INSERT INTO Product__Hist(ProductId, Revision, IsRecordDeleted, [User],  Name) VALUES (@productid, @revision, 0, @user, @name)
    
                FETCH NEXT FROM crs INTO @productid, @name
            END
        CLOSE crs
        DEALLOCATE crs
    END
    ELSE IF EXISTS(SELECT * FROM DELETED)--DELETE
    BEGIN
        DECLARE crs CURSOR FOR
            SELECT ProductId, Name  FROM deleted;
        OPEN crs
        FETCH NEXT FROM crs INTO @productid, @name
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT @revision = COALESCE((SELECT Max(Revision) FROM Product__Hist WHERE ProductId = @productid),0)+1
                INSERT INTO Product__Hist(ProductId, Revision, IsRecordDeleted, [User],  Name) VALUES (@productid, @revision, 1, @user, @name)
    
                FETCH NEXT FROM crs INTO @productid, @name
            END
        CLOSE crs
        DEALLOCATE crs
    END
END

Now for the problem: Most of the time, this solution works perfectly fine. However, I had to experience that Entity-Framework sometimes comes up the following exception when the trigger gets called: «A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding resultset was active». Sadly, I haven’t been able to find out what the root cause of this behavior is. Here are some additional inputs that might be useful and what I’ve tried and found out so far:

I tried to SET NOCOUNT ON at the beginning of the trigger and SET NOCOUNT OFF at the end of the trigger (according to A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active); didn't help
I tried to reproduce the problem (isolate a data-record that causes the issue) but failed.
It has to do something with the trigger since no exception gets thrown if I disable it
I found out that the issue has nothing to do with the amount of data our trigger needs to write
I tried to set "disallow results from triggers" to 1 (according to Add SET NOCOUNT ON on top of triggers). This solution worked fine on my local workstation (DB: SQL-Server 2014). However, if the Database is hosted on Microsoft Azure, I get the following error (40510):

Statement 'CONFIG' is not supported in this version of SQL Server.

Any information that may help me to solve this weird issue is highly appreciated. Thank you in advance!

I know that there are few other questions regarding the same exception. However, since I haven't found anything that would help in my case I've decided to go for a new question.

Application sometimes crashes because of a trigger's resultset

0 Answers0