Update Trigger in SQL Server, OldValues

Hi

I work with SQL Server 2012, having two tables myMaster and myDetail. Under some circumstances I have to modify the key-value of the master. I think I have to update the reference-value in an update-Trigger, but how can I Access there the old value of the changed field?
Thanks, Peter
formiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
updating the key value is not easy to capture in the trigger.
if it's a single row update: no problem, both INSERTED and UPDATED tables have only 1 row, so you can just cross join them (result will be 1 row)

if you are updating multiple rows in this case, this will be difficult to tackle, unless you provide another "unique" key/combination of rows that will NEVER be updated.
0
formiAuthor Commented:
ok, I tried
CREATE TRIGGER trgUpd
ON fotest
AFTER UPDATE AS
IF UPDATE(Reihenfolge)
BEGIN
  UPDATE fotestdetail SET Bezug = Reihenfolge WHERE EXISTS (SELECT Bezug FROM DELETED WHERE Bezug = Reihenfolge)
END

Open in new window

but creating this trigger fires an error "invalid columnname Reihenfolge". If I change it to "SET Bezug = 999" it works. Why?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why? because at that level the column Reihenfolge is indeed "not know".

this syntax would not be rejected:
CREATE TRIGGER trgUpd
ON fotest
AFTER UPDATE AS
IF UPDATE(Reihenfolge)
BEGIN
  UPDATE fd
     SET fd.Bezug = d.Reihenfolge 
  FROM fotestdetail  df
  JOIN DELETED d ON fd.Bezug = d.Reihenfolge
END 

Open in new window

BUT it will not work, because if you "update" the column, you cannot join any longer ...
you will need to join the INSERTED table also ...
 

so, let's got this way (still presuming you are updating only 1 record in fotest):
CREATE TRIGGER trgUpd
ON fotest
AFTER UPDATE AS
IF UPDATE(Reihenfolge)
BEGIN
  UPDATE fd
     SET fd.Bezug = i.Reihenfolge 
  FROM fotestdetail  df
  JOIN DELETED d ON fd.Bezug = d.Reihenfolge
  JOIN INSERTED i ON 1 = 1
END 

Open in new window


hope this helps
0
formiAuthor Commented:
Meanwhile I have found that it can solved by using a foreign key with UPDATE CASCADE:
ALTER TABLE fotestDetail ADD CONSTRAINT fk_rf 
FOREIGN KEY (Bezug) REFERENCES test (Reihenfolge) 
ON UPDATE CASCADE

Open in new window

Can you confirm this?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that works also.
many DBA won't like it, but it does work.
the preferred method is to have this done in the application logic code, to update both tables at once, and not defer to triggers or contraints.
  the reason is that contraints and triggers are less obvious to troubleshoot, and are often overlooked when it comes to troubleshoot the performance of a UPDATE .
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.