Question

13.. Assume the Branch table contains a column called ?Total value that-represents the -total price. for all books.at -that branch. Following ?the style shown in-the text, write the code for the following-triggers. a.-When ?inserting a row in the Copy table, -add -the price -to the -total -value for the appropriate branch . b. When-updating -a -row in-the Copy table, .add-the-difference between -the newprice-and the old price to the total value for the appropriate branch c. When deleting a-row-from the Copy table, subtract the price -on-the record -being -deleted-from-the total value for the appropriate branch. I am supposed to create the code in SQL for the 3 Triggers using the information on the table below.

I am supposed to create the code in SQL for the 3 Triggers using the information on the table below.

Answer 1

a)

create or replace trigger t1
after insert on copy for each row

declare

begin
   update branch set totalvalue = totalvalue + (:old.copynum*:old.price) where branch = :old.branchnum;
end;

b)

create or replace trigger t2
after update on copy for each row

declare

begin
   update branch set totalvalue = totalvalue + (:new.price - :old.price) where branch = :old.branchnum;
end;

c)

create or replace trigger t2
after delete on copy for each row

declare

begin
   update branch set totalvalue = totalvalue - :new.price where branch = :old.branchnum;
end;