Toreador (5/13/2010)
Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!So how would you get the identity values of the rows you'd just inserted?
Change the trigger as follows. Of course, I'm not sure how you would capture the output result set, save using the temp table method as previously mentioned, but then the caller would have to know to look for that temp table.
CREATE TRIGGER TestTrigger ON TestTable
INSTEAD OF INSERT
AS
INSERT TestTable (string)
OUTPUT INSERTED.*
SELECT CASE WHEN string = 'TestString' THEN string ELSE 'Stub' END
FROM inserted
Excellent question, I definitely learned something today!