Rewrite the trigger AvgNetWorthTrigger in the lecture notes Constraints and Triggers for delete and for insert. Note
Question:
Rewrite the trigger AvgNetWorthTrigger in the lecture notes "Constraints and Triggers" for delete and for insert. Note that for delete you need only the Old Table and for the insert you need only New Table.
AvgNetWorthTrigger in lecture notes:
Example of Trigger uGiven the relation schema MovieExec ( name, address, cert#, netWorth) We do want the netWorthof any executive to be below $500000. This constraint could be violated by insertion, deletion , or updating
Solution of the example in lecture notes: CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF netWorth ON MovieExec REFERENCING OLD TABLE AS OldStuff, NEW TABLE AS NewStuff FOR EACH STATEMENT WHEN (500000>(SELECT AVG(netWorth) FROM MovieExec)) BEGIN DELETE FROM MovieExec WHERE( name, address, cer#, netWorth) IN NewStuff; INSERT INTO MovieExec (SELECT * FROM OldStuff); END;
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last