-- 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

/*
-- 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