Can INSTEAD OF trigger see multi row IDENTITY column inserts
Posted on 2009-04-20
I am writing an auditing INSTEAD OF UPDATE trigger, and want to perform an INSERT and then cascade the changes to the PK to child tables based on the PK-FK
To cascade I need to find the IDENTITY values from the newly Inserted rows so the question is, can an INSTEAD OF trigger ever see the new IDENTITY values, or will I need to perform the INSERT and then implement an AFTER INSERT trigger to see the new Identity values (probably in the inserted table?)
I am unable to use @@IDENTITY as this is a muilti row trigger, and would prefer a set based solution than cursors.
Basially I am simulating the Cascade on Update rule of a foreign key, but with a soft delete and versioning involved.