Update trigger examples

The following example cascades an update from the primary table titles to the dependent tables titleauthor and roysched.

create trigger cascade_utrig
on titles
for update as
if update(title_id)
begin
     update titleauthor
           set title_id = inserted.title_id
           from titleauthor, deleted, inserted
           where deleted.title_id = titleauthor.title_id
     update roysched
           set title_id = inserted.title_id
           from roysched, deleted, inserted
           where deleted.title_id = roysched.title_id
     update salesdetail
           set title_id = inserted.title_id
           from salesdetail, deleted, inserted
           where deleted.title_id = salesdetail.title_id
end

To test this trigger, suppose that the book Secrets of Silicon Valley was reclassified to a psychology book from popular_comp. The following query updates the title_id PC8888 to PS8888 in titleauthor, roysched, and titles.

update titles
set title_id = "PS8888"
where title_id = "PC8888"

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF