Good question, I didn't think about it when I was writing the QOTD.

Here is one of the possible solutions. We can get the identity values inside the trigger using the OUTPUT clause. These values can be passed outside the trigger via a temporary table.

CREATE TABLE TestTable (id INT IDENTITY, string VARCHAR(100))

GO

CREATE TRIGGER TestTrigger ON TestTable

INSTEAD OF INSERT

AS

IF OBJECT_ID('tempdb..#id') IS NOT NULL

INSERT TestTable (string)

OUTPUT inserted.id

INTO #id

SELECT CASE WHEN string = 'TestString' THEN string ELSE 'Stub' END

FROM inserted

ELSE

INSERT TestTable (string)

SELECT CASE WHEN string = 'TestString' THEN string ELSE 'Stub' END

FROM inserted

GO

CREATE TABLE #id (id INT)

SELECT * FROM TestTable

INSERT TestTable (string)

SELECT x.string

FROM

( SELECT 'TestString' AS string

UNION ALL

SELECT 'AnotherString' AS string

) x

SELECT * FROM #id

GO

Maybe it's not the best solution, but it works 🙂