Oracle – trigger example

This note provides an example on a trigger. I like this example as it shows how additional business logic can be implemented.

It is an example of a statement trigger: whenever a statement is issued, the trigger fires once, no matter how many records are affected.

CREATE OR REPLACE TRIGGER security_time_check
     BEFORE DELETE OR UPDATE ON emp
DECLARE
  dy_of_week   CHAR(3);
  hh_of_day    NUMBER(2);
BEGIN
  dy_of_week := TO_CHAR(SYSDATE,'DY');
  hh_of_day := TO_CHAR(SYSDATE,'HH24');
  IF dy_of_week IN ('WO')
     OR hh_of_day NOT BETWEEN 8 AND 17 THEN
    RAISE_APPLICATION_ERROR(-20600, 'Transaction rejected for security reasons');
  END IF;
END;
/