Triggers Examples Exercise PCs Laptops Printers Productmaker model

Exercise – PCs, Laptops, Printers Product(maker, model, type) PC(model, speed, ram, hd, rd, price)

(a) CREATE OR REPLACE TRIGGER Product. Model. Check AFTER INSERT on Product FOR EACH

(b) CREATE OR REPLACE TRIGGER CHECK_PRICE BEFORE UPDATE OF price ON PC FOR EACH

Now try… INSERT INTO PC(model, speed, ram, hd, rd, price) VALUES(1111, 3, 4, 80,

Slides: 5

Download presentation

Triggers Examples

Exercise – PCs, Laptops, Printers Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Create triggers to enforce the following business rules. a) A model of a product must also be the model of a PC, a laptop, or a printer. b) When updating the price of a PC, check that there is no lower priced PC with the same speed.

(a) CREATE OR REPLACE TRIGGER Product. Model. Check AFTER INSERT on Product FOR EACH ROW DECLARE --local variables Now try: model_local INT; INSERT INTO Product (model, maker, type) CURSOR model_check IS VALUES (9999, 'Alex', 'PC'); SELECT model FROM ( (SELECT model from PC) UNION Since model 9999 isn’t yet in PC, the above (SELECT model from Laptop) UNION will fail. (SELECT model from Printer) ) WHERE model=: new. model; BEGIN OPEN model_check; FETCH model_check INTO model_local; IF model_check%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20000, 'No model number ' || : new. model || ' in PC, Laptop or Printer'); END IF; END Product. Model. Check;

(b) CREATE OR REPLACE TRIGGER CHECK_PRICE BEFORE UPDATE OF price ON PC FOR EACH ROW DECLARE Needed in ORACLE if you are pragma autonomous_transaction; accessing the same table that caused the trigger to fire. price_min PC. price%TYPE; CURSOR pc_cur IS SELECT MIN(price) FROM PC WHERE speed=: new. speed; BEGIN OPEN pc_cur; FETCH pc_cur INTO price_min; IF price_min IS NOT NULL AND price_min < : old. price THEN RAISE_APPLICATION_ERROR(-20000, 'price rule violated. Lower price is: ' || price_min); END IF; CLOSE pc_cur; END;

Now try… INSERT INTO PC(model, speed, ram, hd, rd, price) VALUES(1111, 3, 4, 80, 24, 1500); INSERT INTO Product(model, maker, type) VALUES(1111, 'DELL', 'PC'); COMMIT; INSERT INTO PC(model, speed, ram, hd, rd, price) VALUES(2222, 3, 4, 120, 32, 1000); INSERT INTO Product(model, maker, type) VALUES(2222, 'IBM', 'PC'); COMMIT; UPDATE PC SET price=1400 WHERE model=1111; This update shouldn’t go through according to our constraint, and indeed, it gives: Error report: SQL Error: ORA-20000: price rule violated. Lower price is: 1000 ORA-06512: at "THOMO. CHECK_PRICE", line 15 ORA-04088: error during execution of trigger 'THOMO. CHECK_PRICE'