It is known that you cannot get inserted identity values using OUTPUT clause when inserting to view which has instead of trigger on it. I would like to offer another workaround I was used recently.
I am talking about fact that you will not succeed with this:
-- inserting records via vw_Customer which has instead of trigger on itself. -- Underlying Customer table has Id column defined as IDENTITY. INSERT INTO vw_Customer ( Name , Value_FilledBy_Instead_Of_Trigger ) OUTPUT inserted.Id, inserted.Name, inserted.Value_FilledBy_Instead_Of_Trigger INTO @someTable SELECT Name, Value FROM someOtherTable
I used workaround with process-keyed table and process-keyed view which encapsulates reading/inserting of keyed records. It is similar workaround as using #table but I dislike #table management within triggers.
Check this commented code:
/* -- drops DROP TABLE Customer DROP VIEW vw_Customer DROP TABLE PK_Customer_TriggerScope DROP VIEW vw_Customer_TriggerScope */-- create customer table CREATE TABLE Customer (id INT IDENTITY (1,1) NOT NULL, Name VARCHAR(20) NOT NULL, Value_FilledBy_Instead_Of_Trigger VARCHAR(20) NOT NULL) GO -- create customer view CREATE VIEW vw_Customer AS SELECT * FROM Customer GO -- create process keyed table for usage in instead of trigger CREATE TABLE PK_Customer_TriggerScope ( SPID INT, Id INT, Name VARCHAR(20), Value_FilledBy_Instead_Of_Trigger VARCHAR(20) ) GO -- add default constraint for process keyed table to ensure that SPID is filled/filtered automatically ALTER TABLE PK_Customer_TriggerScope ADD CONSTRAINT DF_PK_Customer_TriggerScope_SPID DEFAULT (@@spid) FOR [SPID] GO -- create view on process keyed table to encapsulate records process keying CREATE VIEW vw_Customer_TriggerScope AS SELECT * FROM PK_Customer_TriggerScope WHERE SPID = @@SPID GO -- create instead of insert trigger on customer view CREATE TRIGGER tg_IOI_vw_Customer ON vw_Customer INSTEAD OF INSERT AS BEGIN -- flush records keyed for this process DELETE FROM vw_Customer_TriggerScope -- do customer inserts and fill PK_Customer_TriggerScope with output keyed for this process -- unfortunately you cannot use view in output clause so PK_Customer_TriggerScope must be use directly INSERT INTO Customer ( Name , Value_FilledBy_Instead_Of_Trigger ) OUTPUT @@SPID, inserted.Id, inserted.Name, inserted.Value_FilledBy_Instead_Of_Trigger INTO PK_Customer_TriggerScope SELECT Name, 'Filled by IOI' FROM inserted END GO -- Insert few customers and try to get identity INSERT INTO vw_Customer ( Name ) SELECT TOP 10 name FROM msdb.sys.objects -- use sys.objects as list of customers -- get inserted records from process keyed table -- it is ensured that now it contains only customer records inserted within instead of trigger SELECT * FROM vw_Customer_TriggerScope
Jakub Dvorak @ www.sqltreeo.com