OUTPUT clause returning 0 for newly inserted identity value due to INSTEAD OF trigger

Question

Consider the following minimal, complete, and verifiable example code (see dbfiddle here):

CREATE TABLE [dbo].[test]
(
      [i] bigint NOT NULL 
        identity(1,1) 
        PRIMARY KEY CLUSTERED
    , [d] varchar(10) NOT NULL
);
GO

With an INSTEAD OF INSERT, UPDATE trigger:

CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
SET NOCOUNT ON;

MERGE INTO [dbo].[test] [target]
USING [inserted] [source] ON [target].[i] = [source].[i]
WHEN NOT MATCHED THEN
    INSERT
    (
        [d]
    )
    VALUES 
    (
        [source].[d]
    )
WHEN MATCHED THEN 
    UPDATE
    SET [target].[d] = [source].[d];

END;
GO

I'm running an insert into the table, hoping to get the inserted identity value, however the value returned is 0:

DECLARE @output TABLE
(
      [i] bigint NOT NULL
    , [d] varchar(10) NOT NULL
);
INSERT INTO [dbo].[test]
(
    [d]
)
OUTPUT 
      [inserted].[i]
    , [inserted].[d]
INTO @output 
(
      [i]
    , [d]
) 
VALUES ('test');
/* shows [i] is 0 /
SELECT 
FROM @output;
/* shows [i] is 1 /
SELECT 
FROM [dbo].[test];

Results are:

i	d
0	test

and

i	d
1	test

The desired outcome would be that both sets of output match, however they don't.

What am I doing wrong?

I have seen this however that seems quite different since I'm not using a view at all. The trigger is on the table in my example.

Paul White · Accepted Answer · 2024-02-09T04:53:09.813

The behaviour is confusing and poorly documented.

It used to be slightly better documented in INSTEAD OF INSERT Triggers (link to 2008 R2 docs):

The explanation is that at the time the values are captured—before the trigger fires— SQL Server cannot know how many rows will actually be inserted by the trigger. It does not assign identity values in advance and somehow attempt to match them up when the results of the trigger are known.

The quote:

the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred

conveys the general concept. It does not mean the returned results are exactly the same in all respects as if the underlying operation had occurred. That's just not possible in all situations.

The preceding paragraph says (emphasis added):

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

No identity values are assigned before the INSTEAD OF actions take place so they cannot be present in the OUTPUT set.

It may help to remember that the initial operation (INSERT in this example) occurs against a hidden temporary table, despite being labelled in execution plans as the target table itself. More information in my article Interesting things about INSTEAD OF triggers.

INSTEAD OF triggers are different because this type of DML trigger completely replaces the triggered action. The inserted and deleted pseudo-tables now represent changes that would have been made, had the triggering statement actually executed. Row-versioning cannot be used for these triggers because no modifications have occurred, by definition. So, if not using row versions, how does SQL Server do it?

The answer is that SQL Server modifies the execution plan for the triggering DML statement when an INSTEAD OF trigger exists. Rather than modifying the affected tables directly, the execution plan writes information about the changes to a hidden worktable. This worktable contains all the data needed to perform the original changes, the type of modification to perform on each row (delete or insert), as well as any information needed in the trigger for an OUTPUT clause.

Zikato · Answer 2 · 2024-02-08T20:40:12.483

It's behaving as documented.

Relevant quote: OUTPUT clause (Transact-SQL)

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation.

Updated demo:

DROP TABLE IF EXISTS dbo.Test
CREATE TABLE [dbo].[test]
(
      [i] bigint NOT NULL 
        identity(1,1) 
        PRIMARY KEY CLUSTERED
    , [d] varchar(10) NOT NULL
);
GO
INSERT INTO [dbo].[test]
(
    [d]
)
VALUES ('seed');
go
CREATE TRIGGER [dbo_test_trigger]
ON [dbo].[test]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
SET NOCOUNT ON;

INSERT INTO [dbo].[test] 
OUTPUT Inserted.* , 'trigger', IDENT_CURRENT('dbo.test') INTO #output
SELECT d FROM Inserted
--WHERE 1 = 0


END;
GO
DROP TABLE IF EXISTS #output
CREATE table #output
(
    SeqNo int IDENTITY (1,1)
    ,  [i] bigint NOT NULL
    , [d] varchar(10) NOT NULL
    , type varchar(20)
    , identCurrent bigint
);
INSERT INTO [dbo].[test]
(
    [d]
)
OUTPUT 
      [inserted].[i]
    , [inserted].[d]
    , 'outer'
    , IDENT_CURRENT('dbo.test')
INTO #output 
VALUES ('test');
SELECT *
FROM #output;
SELECT *, IDENT_CURRENT('dbo.test') AS identCurrent
FROM [dbo].[test]

Result

Since the temp table is visible in the nested scope, I can see that the first output is the outer one (even before the trigger was called.

If you uncomment the WHERE 1 = 0 you can see that the intended values are inserted anyway.

OUTPUT clause returning 0 for newly inserted identity value due to INSTEAD OF trigger

2 Answers2