By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[test]
(
[i] bigint NOT NULL
identity(1,1)
PRIMARY KEY CLUSTERED
, [d] varchar(10) NOT NULL
);
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;
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];
| i | d |
|---|---|
| 0 | test |
| i | d |
|---|---|
| 1 | test |