SQL Server Updating from a table variable -

SQL Server Updating from a table variable -

final edit - solved - solution below

i can't believe didn't know this, apparently issue table variable , real table both had same name: @checkers , dbo.checkers. trigger apparently confusing two. discovered changing order of insert , update operations in trigger such insertion real table happens after update table variable. suddenly, updates began passing without issue.

of course, created dbo.checkers debug @checkers, original problem existed started all; regardless, problem has been resolved , trigger works fine. i'm posting below, sense free offer feedback if wish. i'm not expert (clearly), , happy receive constructive feedback.

thanks help , comments. hope post helps out there avoid headache...

query - solution

alter trigger catchchange on [dbo].[orders] -- lastly updated : 07 oct 2014 after update not replication -- trigger not fired agent updates begin if update(updaterecorddate) -- trigger fired user updates include timestamp. -- **not** fire primary stored procedure (dictated updaterecorddate). begin -- table variable storing **multi-row updates** -- using scalar variables not work! up. declare @checkers table ( ordernum int, old_dcb int, new_dcb int, old_mc int, new_mc int, old_p2 numeric(28,3), new_p2 numeric(28,3), old_pq numeric(28,3), new_pq numeric(28,3), old_qt numeric(28,3), new_qt numeric(28,3) ) -- old & new values populated table variable. -- can add together table in same format variable & -- check values passed, if necessary. -- insert scenario commented out @ end. insert @checkers select i.orderno, d.drivercb, i.drivercb, d.mcode, i.mcode, d.price2, i.price2, d.pricequantity2, i.pricequantity2, d.quantity1, i.quantity1 inserted bring together deleted d on i.orderno = d.orderno -- these checks crucial, cutting downwards on reiteration. -- without them, programme fire trigger redundantly. -- here check changes relevant values & -- ensure update new one, not repetitive one. i.orderkind = 0 , isnull(d.updaterecorddate,convert(datetime,0)) <> isnull(i.updaterecorddate,convert(datetime,0)) , (d.drivercb <> i.drivercb or d.mcode <> i.mcode or d.price2 <> i.price2 or d.pricequantity2 <> i.pricequantity2 or d.price3 <> i.price3 or d.pricequantity3 <> i.pricequantity3 or d.quantity1 <> i.quantity1) -- case conditions preferable in instance if conditions. -- relevant values checked here against dictating conditions & -- updated accordingly. update o set o.drivercb = case when c.new_dcb = 0 null else o.drivercb end, <...>, o.quantity4 = case when c.new_qt > 0 o.quantity1 else o.quantity4 end orders o inner bring together @checkers c on o.orderno = c.ordernum -- insert populates debug table, if want. -- naturally, name here must match name of created table! -- , yes, dd, need create actual table outside of trigger. :) /* insert checkme select c.id, c.ordernum, c.old_drivercodebizua, c.new_drivercodebizua, c.old_maslulcode1, c.new_maslulcode1, c.old_price2, c.new_price2, c.old_pricequntity2, c.new_pricequntity2, c.old_quntity, c.new_quntity @checkers c */ end end go

end query - solution

--------begin original post--------

i have client needs me touch bunch of old triggers perform various functions. generally, i'm not in favor of , think programme should updated - whatever.

three triggers exist, , need combine them single trigger works little more smoothly , has few more conditions. (presently, total lack of conditions makes trigger run @ every operation, painfully slow watch.)

in short, have created new trigger in declare table variable (query below). give myself direct command on column names rather using select x #temp. thereafter, attempting utilize case checks update various fields in main table dependent upon changes logged in table variable.

the problem update doesn't anything. no errors, values in main table remain same. ensure table variable getting values , available use, added new table test database , @ each fire of trigger insert values contained in table variable new table, this:

declare @checkers table ( ordernum int, old_dcb int, new_dcb int, old_mc int, new_mc int, old_qt numeric(28,3), new_qt numeric(28,3) ) insert @checkers select i.orderno, d.drivercb, i.drivercb, d.mcode, i.mcode, d.quantity, i.quantity inserted bring together deleted d on i.orderno = d.orderno i.orderkind = 0 , (d.drivercb <> i.drivercb or d.mcode1 <> i.mcode1 or d.quantity <> i.quantity) insert checkers select ordernum, old_dcb, new_dcb, old_mc, new_mc, old_qt, new_qt @checkers -- actual table created check functionality -- works until point update orders set quantity2 = case when new_qt > 0 new_qt else quantity2 end @checkers c orderno = c.ordernum

naturally, there many more updates in actual trigger, idea.

any ideas why update orders doesn't go through? small, silly, , embarassing - 1 time again say, whatever ;) thanks.

--------end original post--------

-- edit:

i have tried writing update such:

update o set quantity2 = case when c.new_qt > 0 c.new_qt else quantity2 end @checkers c inner bring together orders o on o.orderno = c.ordernum

this approach gives same result: no error message (or other message, matter), , no update...

-- 2nd edit:

if utilize actual table checkers rather table variable @checkers, update passes:

update o set quantity2 = case when c.new_qt > 0 c.new_qt else quantity2 end checkers c -- actual table, not table variable! inner bring together orders o on o.orderno = c.ordernum

this works regardless of presence of orders in join:

update orders set quantity2 = case when c.new_qt > 0 c.new_qt else quantity2 end checkers c -- actual table, not table variable! orderno = c.ordernum

the problem that, in customer's database, cannot add together real table checkers. added in test database clarify whether or not values passing table variable! restriction of table variables not familiar with? i'm having hard time finding such limitation in online documentation...

you need add together orders in statement inner join. seek this:

update o set quantity2 = case when new_qt > 0 new_qt else quantity2 end @checkers c inner bring together orders o on o.orderno = c.ordernum

sql sql-server triggers updates table-variable