Why is the INSTEAD OF UPDATE insert table INSERTED empty?

Question

Plan: To use a trigger INSTEAD OF INSERTto redirect failed inserts to the pending table. These rows remain in the waiting table until any additional information is inserted into another table; When this new information is available, pending lines are moved to the original destination.

Background: Trades are recorded relative to the Holding. A service updating the Trading may have information that is not yet in the database, for example, a Holding trade that has not yet been inserted (please do not focus on the "why" of this part of the system, t change this value).

Problem: The trigger works INSTEAD OF INSERT, but I am having problems with the trigger INSTEAD OF UPDATE. When applied UPDATE, but the rows that need to be updated are in the pending table, the table INSERTEDin 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 for execution UPDATEworks when the row exists in the table Trade, but not when the row does not exist, the virtual table is INSERTEDempty. I added a few statements PRINTto the trigger to try to see what was 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 are some sample test data:

-- 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 result of the update:

Update Trade
h=1 s=S

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

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

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

The result is the following result:

Update TradePending
h=0 s=N

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

INSERTED , INSTEAD OF , SQL .

- , INSERTED ? , - , .

Answer 1

, - INSERTED , , : UPDATE Trade TradePending!

, INSTEAD OF INSERT . , , , concurrency. MERGE .

, , . INSTEAD OF , , , , , . , // .

, Trade, TradePending, , , Trade TradePending DML . , , .

Update:

, :

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

, - Trade , TradePending. , .

Answer 2

, , , ? ( , , ​​ .) ? .