sql - Trigger isn't inserting the correct row -

i have trigger made

alter trigger fuzzylogic  on oehdrhst_sql  insert begin declare @ordno char(8) declare @rownum int declare @id int  select @ordno = ord_no inserted select @rownum = a.id banktransactions  convert(char(8),a.ownreference) = (select ord_no inserted)   select @id = a.id banktransactions join inserted on i.ord_no = a.invoicenumber a.invoicenumber = @ordno begin     insert triggertest values(@ordno,@rownum,@id)     update banktransactions set matchid = @rownum,supplierinvoicenumber = @ordno id = @id end end 

when inside triggertest after insert row see ordno , rownum keep getting nulls on id

i ran sql statement test see if query wouldn't return did.

select a.id banktransactions join oehdrhst_sql b on a.invoicenumber = b.ord_no 

why won't variable print?

select @ordno = ord_no inserted

this logic fundamentally flawed. if insert more 1 row, value expect assigned variable? arbitrary. triggers fire per statement, not per row.

you need update trigger handle multi-row inserts. without schema, sample data , desired results, wild fuzzy guess @ trigger logic should using instead of assigning arbitrary row inserted:

alter trigger dbo.fuzzylogic -- use dbo prefix! on dbo.oehdrhst_sql -- use dbo prefix! insert begin   set nocount on;    insert dbo.triggertest -- use schema prefix!     -- column list?     select i.ord_no, b1.id, b2.id     inserted     inner join dbo.banktransactions b1 -- use schema prefix!     on i.ord_no = convert(char(8), b1.ownreference)     inner join dbo.banktransactions b2 -- use schema prefix!     on i.ord_no = b2.invoicenumber;    update b     set matchid = b1.id, supplierinvoicenumber = i.ord_no     dbo.banktransactions b     inner join dbo.banktransactions b2 -- use schema prefix!     on b.id = b2.id     inner join inserted      on i.ord_no = b2.invoicenumber     inner join dbo.banktransactions b1 -- use schema prefix!     on i.ord_no = convert(char(8), a.ownreference); end go 

this may totally wrong, again it's hard tell without more insight system (or why 3 different rows in bank transactions table should related - yikes!). should @ least give start.