Triggers Examples

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 modelnew.model
• BEGIN
• OPEN model_check
• FETCH model_check INTO model_local
• IF model_checkNOTFOUND THEN
• RAISE_APPLICATION_ERROR(-20000, 'No model
  number '
• new.model
  

Now try INSERT INTO Product (model, maker,
type) VALUES (9999, 'Alex', 'PC') Since model
9999 isnt 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.priceTYPE
• CURSOR pc_cur IS
• SELECT MIN(price)
• FROM PC
• WHERE speednew.speed
• BEGIN
• OPEN pc_cur
• FETCH pc_cur INTO price_min
• IF price_min IS NOT NULL AND price_min lt
  old.price THEN
• RAISE_APPLICATION_ERROR(-20000, 'price rule
  violated. Lower price is '
• price_min)
• END IF
• CLOSE pc_cur

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 price1400
• WHERE model1111
• This update shouldnt go through according to our
  constraint, and indeed, it gives
• Error report
• SQL Error ORA-20000 price rule violated. Lower
  price is 1000