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
Last activity: Feb 9, 2024, 04:53 AM