1 / 5

Triggers Examples

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.

zalika
Download Presentation

Triggers Examples

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Triggers Examples

  2. 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 model of a product must also be the model of a PC, a laptop, or a printer. • When updating the price of a PC, check that there is no lower priced PC with the same speed.

  3. (a) CREATE OR REPLACE TRIGGER ProductModelCheck AFTER INSERT on Product FOR EACH ROW DECLARE --local variables model_local INT; CURSOR model_check IS SELECT model FROM ( (SELECT model from PC) UNION (SELECT model from Laptop) UNION (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 ProductModelCheck; Now try: INSERT INTO Product (model, maker, type) VALUES (9999, 'Alex', 'PC'); Since model 9999 isn’t yet in PC, the above will fail.

  4. (b) CREATE OR REPLACE TRIGGER CHECK_PRICE BEFORE UPDATE OF price ON PC FOR EACH ROW DECLARE pragma autonomous_transaction; 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; Needed in ORACLE if you are accessing the same table that caused the trigger to fire.

  5. 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'

More Related