Sample Header Ad - 728x90

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

8 votes
2 answers
910 views
Consider the following minimal, complete, and verifiable example code (see [dbfiddle here](https://dbfiddle.uk/qm2q8bkS)) :
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](https://dba.stackexchange.com/questions/34258/getting-identity-values-to-use-as-fk-in-an-instead-of-trigger) however that seems quite different since I'm not using a view at all. The trigger is on the table in my example.
Asked by Hannah Vernon (70988 rep)
Feb 8, 2024, 06:51 PM
Last activity: Feb 9, 2024, 04:53 AM