Database Trigger with Example

A trigger:

·         Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or database

·         Executes implicitly whenever a particular event takes place

·         Can be either of the following:

Application trigger: Fires whenever an event occurs with a particular application

Database trigger: Fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.

Guidelines for Designing Triggers:

•       You can design triggers to:

–      Perform related actions

–      Centralize global operations

•       You must not design triggers:

–      Where functionality is already built into the Oracle server

–      That duplicate other triggers

•       You can create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy.

•       The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.

Creating DML Triggers:

CREATE [OR REPLACE] TRIGGER trigger_name  

  timing  

  event1 [OR event2 OR event3]  

 ON object_name  

 [[REFERENCING OLD AS old | NEW AS new] 

  FOR EACH ROW  

  [WHEN (condition)]]  

 trigger_body  

•       A statement trigger fires once for a DML statement.

•       A row trigger fires once for each row affected.

Note: Trigger names must be unique with respect to other triggers in the same schema.

Types of DML Triggers:

The trigger type determines if the body executes for each row or only once for the triggering statement.

•       A statement trigger:

–      Executes once for the triggering event

–      Is the default type of trigger

–      Fires once even if no rows are affected at all

•       A row trigger:

–      Executes once for each row affected by the triggering event

–      Is not executed if the triggering event does not affect any rows

–      Is indicated by specifying the FOR EACH ROW clause

Trigger Timing:

When should the trigger fire?

•       BEFORE: Execute the trigger body before the triggering DML event on a table.

•       AFTER: Execute the trigger body after the triggering DML event on a table.

•       INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

Note: If multiple triggers are defined for the same object, then the order of firing triggers is arbitrary

Trigger Event Types and Body:

A trigger event:

•       Determines which DML statement causes the trigger to execute

•       Types are:

–      INSERT

–      UPDATE [OF column]

–      DELETE

A trigger body:

•       Determines what action is performed

Is a PL/SQL block or a CALL to a procedure.

Creating a DML Statement Trigger:

CREATE OR REPLACE TRIGGER secure_emp  

 BEFORE INSERT ON employees BEGIN  

  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR  

    (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN  

  RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||' 

    into EMPLOYEES table only during ' ||' business hours.');  

  END IF;  

 END; 

Testing SECURE_EMP:

INSERT INTO employees (employee_id, last_name,first_name, email, 

                       hire_date,job_id, salary, department_id)  

 VALUES     (300, 'Smith', 'Rob', 'RSMITH', 

            SYSDATE,'IT_PROG', 4500, 60);  

Using Conditional Predicates:

CREATE OR REPLACE TRIGGER secure_emp BEFORE  

 INSERT OR UPDATE OR DELETE ON employees BEGIN  

  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR  

   (TO_CHAR(SYSDATE,'HH24')   

     NOT BETWEEN '08' AND '18') THEN  

   IF DELETING THEN RAISE_APPLICATION_ERROR(  

   -20502,'You may delete from EMPLOYEES table'||  

       'only during business hours.');  

   ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(  

   -20500,'You may insert into EMPLOYEES table'||   

       'only during business hours.');  

   ELSIF UPDATING('SALARY') THEN  

   RAISE_APPLICATION_ERROR(-20503, 'You may '||

        'update SALARY only during business hours.');  

   ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||  

    ' update EMPLOYEES table only during'||  

    ' normal hours.');  

   END IF;  

  END IF;  

 END;  

Creating a DML Row Trigger:

CREATE OR REPLACE TRIGGER restrict_salary  

 BEFORE INSERT OR UPDATE OF salary ON employees  

 FOR EACH ROW  

 BEGIN  

  IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))  

    AND :NEW.salary > 15000 THEN  

   RAISE_APPLICATION_ERROR (-20202,  

    'Employee cannot earn more than $15,000.');  

  END IF;  

 END;  

 /  

Using OLD and NEW Qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values  

 AFTER DELETE OR INSERT OR UPDATE ON employees  

 FOR EACH ROW  

 BEGIN  

  INSERT INTO audit_emp(user_name, time_stamp, id,  

   old_last_name, new_last_name, old_title,  

   new_title, old_salary, new_salary)  

  VALUES (USER, SYSDATE, :OLD.employee_id,  

   :OLD.last_name, :NEW.last_name, :OLD.job_id,  

   :NEW.job_id, :OLD.salary, :NEW.salary);  

 END;  

 /  

Restricting a Row Trigger: Example

CREATE OR REPLACE TRIGGER derive_commission_pct  

 BEFORE INSERT OR UPDATE OF salary ON employees  

 FOR EACH ROW  

 WHEN (NEW.job_id = 'SA_REP')  

 BEGIN  

  IF INSERTING THEN  

   :NEW.commission_pct := 0;  

  ELSIF :OLD.commission_pct IS NULL THEN  

   :NEW.commission_pct := 0;  

  ELSE   

   :NEW.commission_pct := :OLD.commission_pct+0.05;  

  END IF;  

 END;  

 /