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

the plan: use instead of insert trigger redirect failed inserts 'pending' table. these rows remain in 'pending' table until addition information inserted in table; when new information available pending rows moved original destination.

background: trades recorded relating holding. service updating trades can have information not yet in database, such trade on holding has not been inserted yet (please don't focus on 'why' of part of system, can't change that).

problem: instead of insert trigger working i'm having trouble instead of update trigger. when update applied rows updated in 'pending' table inserted table in trigger empty , therefore cannot update 'pending' table. here's (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 -- insert trigger works, when referenced holding not exist row redirected tradepending table. create trigger [trg_trade_insert] on [trade] instead of insert if not exists (select 1      inserted inner join holding h     on i.holdingid = h.holdingid) begin     insert tradepending(holdingid, buysell) select holdingid, buysell inserted end else begin     insert trade(holdingid, buysell) select holdingid, buysell inserted end go 

the trigger update works when row exists in trade table not when row not exist, inserted virtual table empty. have added print statements trigger try see happening.

create trigger [dbo].[trg_trade_update] on [dbo].[trade] instead of update  declare @s char(1) declare @h int  if not exists (select 1      inserted inner join trade t     on i.holdingid = t.holdingid) begin     print 'update tradepending'  set @h = coalesce((select i.holdingid     tradesummarypending t inner join inserted         on t.holdingid = i.holdingid), 0) set @a = coalesce((select i.buysell     tradesummarypending t inner join inserted         on t.holdingid = i.holdingid), 'n')     print 'h=' + cast(@h varchar(1)) + ' s=' + @s      update tradepending      set buysell = i.buysell     trade t inner join inserted         on t.holdingid = i.holdingid end else begin     print 'update trade'         set @h = (select i.holdingid         trade t inner join inserted             on t.holdingid = i.holdingid)     set @s = (select i.buysell         trade t inner join inserted             on t.holdingid = i.holdingid)     print 'h=' + cast(@h varchar(1)) + ' s=' + @s      update trade         set buysell = i.buysell     trade t inner join inserted         on t.holdingid = i.holdingid end 

here's sample data testing:

-- create holding , trade, inserted normal. insert holding values(1,100) insert tradesummary values(1,'b')  -- create trade holding not exists, -- row redirected tradepending table. insert tradesummary values(2,'s')  -- update first trade buy, updates `trade` table update trade set buysell = 's' holdingid = 1 

the output executing update:

update trade h=1 s=s  (1 row(s) affected)     (1 row(s) affected) 

now update row exists in tradepending table:

update trade set buysell = 'b' holdingid = 2 

which results in following output:

update tradepending h=0 s=n  (0 row(s) affected) (0 row(s) affected) 

the inserted table appears contain rows though instead of trigger , should executed before sql applied table.

can explain why inserted table empty? i'm sure solution going trivial can't seem working.

of course rows don't exists in inserted pseudo-table when update rows don't exists in table start with: issue update statement on trade rows in tradepending!

besides, instead of insert trigger broken. works single row inserts, , fail under concurrency. use set based merge.

ultimately designing hack around data model disconnected application does. creating instead of triggers change shape of table used legacy code works far, problem encountered 1 of many issues down road. ultimately, client code has insert/update/delete right table.

as workaround try moving data table holds both trade , tradepending , uses state column distinguish two, expose old trade , tradepending tables views , use triggers capture dml on views redirect them proper table. not sure if work though, cannot test right now.

update:

here example how work 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 schemabinding select tradeid, holdingid, buysell dbo.tradestorage exists (     select holdingid dbo.holding     holding.holdingid = tradestorage.holdingid); go  create view tradepending schemabinding select tradeid, holdingid, buysell dbo.tradestorage not exists (     select holdingid dbo.holding     holdingid = tradestorage.holdingid); go    -- create holding , trade, inserted normal. insert holding values(1,100) insert trade values(1,'b')  -- create trade holding not exists, -- row redirected tradepending table. insert trade values(2,'b') go  select * trade; select * tradepending; go  -- update first trade buy, updates `trade` table update trade set buysell = 's' holdingid = 1 go  -- insert holding id 2,  -- automatically move pending trade trade insert holding values(2,100)  select * trade; select * tradepending; go  update trade set buysell = 's' holdingid = 2 go  select * trade; select * tradepending; go 

note still no possible update trade records in tradepending. no trigger, view or similar mechanism can such.