Author: Jeff Garcia

Page: 1 2 3

Oracle Triggers:

Triggers are special PL/SQL constructs that are executed implicitly whenever the triggering event happens. Based on your business requirements you can write multiple triggers on a single table. It is possible to have a ‘BEFORE INSERT' trigger and ‘AFTER UPDATE' trigger on the same table. You can also define more than one trigger of the same type. For example you can have multiple ‘BEFORE UPDATE' or multiple ‘AFTER UPDATE' triggers. 

Order of Oracle Triggers:

In general Oracle executes triggers based on the type of the trigger. First all triggers of same type are executed and then of the other type. Oracle releases prior to 11g could create issues with multiple triggers of same type. Oracle 11g has introduced ‘FOLLOWS' clause to control the execution order of the triggers of the same type.

If we have multiple triggers of the same type on the same table then Oracle will execute the triggers randomly unless we specify the ‘FOLLOWS' clause. Consider below example

Create Table and Sequence:

First we create table and sequence. Then we will create two ‘BEFORE INSERT' trigger on the table.

CREATE TABLE MYTABLE
(
MYCOL_1 NUMBER(9),
MYCOL_2 NUMBER(9),
MYCOL_3 DATE
)
/

CREATE SEQUENCE MYTABLE_SEQ START WITH 1234;

Create Trigger 1 on table:

CREATE OR REPLACE TRIGGER TRI_MYTABLE_1
BEFORE INSERT
ON MYTABLE
FOR EACH ROW
BEGIN
:NEW.MYCOL_1 := MYTABLE_SEQ.NEXTVAL;
Dbms_output.put_line('In Trigger TRI_MYTABLE_1');
END;
/

Create Trigger 2 on table:

CREATE OR REPLACE TRIGGER TRI_MYTABLE_2
BEFORE INSERT
ON MYTABLE
FOR EACH ROW
DECLARE
v_MYCOL_2 VARCHAR2(10);
BEGIN
SELECT REVERSE(to_char(:NEW.MYCOL_1))
INTO v_MYCOL_2
FROM DUAL;
:NEW.MYCOL_2 := to_number(v_MYCOL_2); –REVERSE(:NEW.MYCOL_1);
Dbms_output.put_line('In Trigger TRI_MYTABLE_2');
END;
/ Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Effective Tips for Hang Databases!
   Inside Oracle Temporary Tables!!
   Oracle – SQL Guide for Parallel Stored Procedures
   DBA Tips for Determining Invalid Materialized Views!!