Pages

Showing posts with label Trigger. Show all posts
Showing posts with label Trigger. Show all posts

Thursday, January 1, 2015

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;  
 /