Update Trigger Help 1

[meBrian]

I want to create an UPDATE Trigger to copy a value from 2 different tables to the third as described below:

equip.classid = xx
equip.Failure = <-- r
equip.Code = <-- q

struct.classid = xx
struct.column1 = q
struct.column2 = yy

asset.assetid = yy
asset.column1 = r

So what I want to get is:
equip.Code = struct.column1
equip.Failure = asset.column1

Thanks for the help.

 

[vongrunt]

Wow, trigger for denormalization. First school example in last 6 months (IMO).

Try something like:

create trigger blah on equip
for update
as
if update(classid)
begin
	update E
	set Code=S.column1, Failure = A.column1
	from equip E
	inner join inserted I on E.<primaryKeyColumn>=I.<primaryKeyColumn>
	inner|left join struct S on I.classid=S.classid
	inner|left join asset A on S.column2=A.assetid
end

Choose between left and inner join depending on data integrity and NULLability of foreign key columns (E.classid, S.column2).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[meBrian]

Thanks. I will give this a workout.