Chapter 10
Page 253
Example 10-3

Triggers to Enforce Security on PAYROLL

CREATE OR REPLACE TRIGGER pay_bir BEFORE INSERT ON payroll
FOR EACH ROW
BEGIN
  IF :new.dept <> k_payroll.my_dept THEN
    raise_application_error (-20001, "Insufficient Privileges");
  END IF;
  IF NOT k_payroll.mgr THEN
    :new.salary := NULL;
  END IF;
END pay_bir;
/

CREATE OR REPLACE TRIGGER pay_bdr BEFORE DELETE ON payroll
FOR EACH ROW
BEGIN
  IF :new.dept <> k_payroll.my_dept
  OR NOT k_payroll.mgr THEN
    raise_application_error (-20001, 'Insufficient Privileges');
  END IF;
END pay_bdr;
/

CREATE OR REPLACE TRIGGER pay_bur BEFORE UPDATE ON payroll
FOR EACH ROW
BEGIN
  IF :new.dept <> k_payroll.my_dept THEN
    raise_application_error (-20001, "Insufficient Privileges");
  END IF;
  IF NOT K_PAYROLL.mgr THEN
    :new.salary := :old.salary;
  END IF;
  IF :new.dept <> :old.dept THEN
raise_application_error (-20002, "Cannot transfer department");
  END IF;
END pay_bur;
/