SQL Server 2022

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