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

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.

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.

StackExchange Database Administrators Q#335647, answer score: 11