Question

Question from book - Database systems the complete book (second edition) by hector Garcia-molina Jeffery D. Ullman Jennifer Widom

Exercise 7.5.2: Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The database scheme is from the "PC" example of Exercise 2.4.1:
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

A) When updating the price of a PC, check that there is no lower priced PC with the same speed.

B) When inserting a new printer, check that the model number exists in Product.

Answer 1

SOLUTION:-The Required triggers are given below:-

(A) AFTER UPCING OL (A) CREATE TRIGGER CheckLower Price AFTER UPDATE OF price ON PC REFERENCING OLD ROW AS OldTuple NEW ROW AS NewTuple FOR EACH ROW WHEN(NOT(NewTuple.price <= ALL (SELECT price FROM PC WHERE speed = NewTuple.speed BEGIN DELETE FORM PC WHERE (model, speed, ram, hd, price) = New Tuple; INSERT INTO PC VALUES (OldTuple.model, OldTuple.speed, OldTuple.ram, OldTuple.hd, OldTuple.price); END;

(B) CREATE TRIGGER CheckModel Number AFTER INSERT on Printer REFERENCING new row as newrow for each row when model not in (select model from Product) begin delete from Printer where model=newrow.model end;

====================================================================================