Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Instead Of Trigger and OUTPUT clause headache

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

Result is that you don’t have identity values in output @someTable. This is behavior described e.g. here or discussed on Microsoft Connect here.

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

Comments

Posted by Anonymous on 29 June 2011

Pingback from  Dew Drop – June 29, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.