Why is the INSERTED table of an INSTEAD OF UPDATE trigger empty?

The Plan: To use an INSTEAD OF INSERT trigger to redirect failed inserts to a 'pending' table. These rows remain in the 'pending' table until some addition information is inserted in another table; when the this new information is available the pending rows are moved to their original destination.

Background: Trades are recorded relating to a Holding. The service updating the Trades can have information which is not yet in the database, such as a trade on a Holding which has not been inserted yet (please don't focus on the 'why' of that part of the system, I can't change that).

Problem: The INSTEAD OF INSERT trigger working but I'm having trouble with the INSTEAD OF UPDATE trigger. When an UPDATE is applied but the rows to be updated are in the 'pending' table the INSERTED table in the trigger is empty and therefore I cannot update the 'pending' table. Here's the (simplified) DDL:

CREATE TABLE [Holding] (
    [HoldingID] INTEGER NOT NULL,
    [InstrumentID] INTEGER,
    CONSTRAINT [PK_Holding] PRIMARY KEY ([HoldingID])
)
GO
CREATE TABLE [Trade] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradeSummary] PRIMARY KEY ([TradeID])
)
GO
ALTER TABLE [Trade] ADD CONSTRAINT [CC_Trade_BuySell] 
    CHECK (BuySell = 'B' or BuySell = 'S')
GO
ALTER TABLE [Trade] ADD CONSTRAINT [Holding_Trade] 
    FOREIGN KEY ([HoldingID]) REFERENCES [Holding] ([HoldingID])
GO
CREATE TABLE [TradePending] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradePending] PRIMARY KEY ([TradeID])
)
GO
ALTER TABLE [TradePending] ADD CONSTRAINT [CC_TradePending_BuySell] 
    CHECK (BuySell = 'B' or BuySell = 'S')
GO
-- The INSERT trigger works, when the referenced holding does not exist the row is redirected to the TradePending table.
CREATE TRIGGER [Trg_Trade_Insert]
ON [Trade]
INSTEAD OF INSERT
AS
IF NOT EXISTS (SELECT 1 
    FROM inserted i INNER JOIN Holding h
    ON i.HoldingID = h.HoldingID)
BEGIN
    INSERT TradePending(HoldingID, BuySell) SELECT HoldingID, BuySell FROM inserted
END
ELSE
BEGIN
    INSERT Trade(HoldingID, BuySell) SELECT HoldingID, BuySell FROM inserted
END
GO

The Trigger to do the UPDATE works when the row exists in the Trade table but not when the row does not exist, the INSERTED virtual table is empty. I have added some PRINT statements to the trigger to try to see what is happening.

CREATE TRIGGER [dbo].[Trg_Trade_Update]
ON [dbo].[Trade]
INSTEAD OF UPDATE
AS

DECLARE @s char(1)
DECLARE @h int

IF NOT EXISTS (SELECT 1 
    FROM inserted i INNER JOIN Trade t
    ON i.HoldingID = t.HoldingID)
BEGIN
    PRINT 'Update TradePending'

SET @h = COALESCE((SELECT i.HoldingID
    FROM TradeSummaryPending t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID), 0)
SET @a = COALESCE((SELECT i.BuySell
    FROM TradeSummaryPending t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID), 'N')
    PRINT 'h=' + CAST(@h AS varchar(1)) + ' s=' + @s

    UPDATE TradePending 
    SET BuySell = i.BuySell
    FROM Trade t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID
END
ELSE
BEGIN
    PRINT 'Update Trade'    
    SET @h = (SELECT i.HoldingID
        FROM Trade t INNER JOIN inserted i
            ON t.HoldingID = i.HoldingID)
    SET @s = (SELECT i.BuySell
        FROM Trade t INNER JOIN inserted i
            ON t.HoldingID = i.HoldingID)
    PRINT 'h=' + CAST(@h AS varchar(1)) + ' s=' + @s

    UPDATE Trade    
    SET BuySell = i.BuySell
    FROM Trade t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID
END

Here's some sample data for testing:

-- Create a Holding and a Trade, this will be inserted as normal.
INSERT Holding VALUES(1,100)
INSERT TradeSummary VALUES(1,'B')

-- Create a Trade where the Holding does not exists,
-- row redirected to TradePending table.
INSERT TradeSummary values(2,'S')

-- Update the first trade to be a Buy, updates the `Trade` table
UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 1

The output from executing the update:

Update Trade
h=1 s=S

(1 row(s) affected)    
(1 row(s) affected)

Now update the row which only exists in the TradePending table:

UPDATE Trade SET BuySell = 'B' WHERE HoldingID = 2

Which results in the following output:

Update TradePending
h=0 s=N

(0 row(s) affected)
(0 row(s) affected)

Th开发者_高级运维e INSERTED table appears to contain now rows even though this is an INSTEAD OF trigger and should be executed before the SQL is applied to the table.

Can anyone explain why the INSERTED table is empty? I'm sure the solution is going to be something trivial but I just can't seem to get it working.

Of course the rows don't exists in the INSERTED pseudo-table when you update rows that don't exists in the table to start with: you issue UPDATE statement on Trade for rows that are in TradePending!

Besides, your INSTEAD OF INSERT trigger is broken. It only works for single row inserts, and even for those it will fail under concurrency. Use a set based MERGE.

Ultimately you are designing a hack around a data model that is disconnected from what the application does. Creating INSTEAD OF triggers to completely change the shape of a table used by legacy code only works so far, this problem you encountered is just one of the many issues down the road. Ultimately, your client code has to insert/update/delete the right table.

As a workaround you could try moving all data into a table that holds both Trade and TradePending and uses a state column to distinguish the two, expose the old Trade and TradePending tables as views and use triggers to capture the DML on the views to redirect them to the proper table. Not sure if would work though, I cannot test it right now.

Update:

Here is an example how this would work with updateable views:

CREATE TABLE [Holding] (
    [HoldingID] INTEGER NOT NULL,
    [InstrumentID] INTEGER,
    CONSTRAINT [PK_Holding] PRIMARY KEY ([HoldingID])
)
GO

CREATE TABLE [TradeStorage] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradeSummary] PRIMARY KEY ([TradeID])
    , CONSTRAINT [CC_Trade_BuySell] CHECK (BuySell IN ('B','S'))
    )
GO

create view Trade
with schemabinding
as
select TradeID, HoldingID, BuySell
from dbo.TradeStorage
where exists (
    select HoldingID from dbo.Holding
    where Holding.HoldingID = TradeStorage.HoldingID);
go

create view TradePending
with schemabinding
as
select TradeID, HoldingID, BuySell
from dbo.TradeStorage
where not exists (
    select HoldingID from dbo.Holding
    where HoldingID = TradeStorage.HoldingID);
go  

-- Create a Holding and a Trade, this will be inserted as normal.
INSERT Holding VALUES(1,100)
INSERT Trade VALUES(1,'B')

-- Create a Trade where the Holding does not exists,
-- row redirected to TradePending table.
INSERT Trade values(2,'B')
go

select * from Trade;
select * from TradePending;
go

-- Update the first trade to be a Buy, updates the `Trade` table
UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 1
go

-- Insert a holding with ID 2, 
-- this will automatically move the pending trade to Trade
INSERT Holding VALUES(2,100)

select * from Trade;
select * from TradePending;
go

UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 2
go

select * from Trade;
select * from TradePending;
go

Note that is still no possible to update Trade for records that are in TradePending. No trigger, view or similar mechanism can do such.

I have not had time to run this, but are you sure that the inserted table is empty? (You are always joining to other tables, so the lack of records in those tables may cause the row to be suppressed in your result sets.) What about the deleted? For an update, you should have an inserted and a deleted set.