Table of Contents
FOLLOWS Keyword And COMPOUND TRIGGER in Oracle
FOLLOWS KEYWORD:
Prior 11g, when we create multiple triggers on same event then there were no such sequence of firing these triggers.
CREATE TABLE EMPLOYEES_FOLLOW_TEST
AS
SELECT * FROM EMPLOYEES;
CREATE OR REPLACE TRIGGER TRIGGER_SEQ_1
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES_FOLLOW_TEST
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 INSERT STATEMENT FIRED');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 UPDATE STATEMENT FIRED');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 DELETE STATEMENT FIRED');
END IF;
END;
/
CREATE OR REPLACE TRIGGER TRIGGER_SEQ_2
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES_FOLLOW_TEST
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 INSERT STATEMENT FIRED');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 UPDATE STATEMENT FIRED');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 DELETE STATEMENT FIRED');
END IF;
END;
/
SET SERVEROUTPUT ON;
INSERT INTO EMPLOYEES_FOLLOW_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,100,80);
Output:
1 row inserted.
TRIGGER_SEQ_2 INSERT STATEMENT FIRED
TRIGGER_SEQ_1 INSERT STATEMENT FIRED
From the output its clearly visible that there is no sequence of firing of above two triggers.
So 11g introduced a new FOLLOWS keyword which helps us to determine which trigger will fire first.
We will now apply the FOLLOWS keyword in trigger TRIGGER_SEQ_2, so that it will always fire after trigger TRIGGER_SEQ_1.
ROLLBACK;
CREATE OR REPLACE TRIGGER TRIGGER_SEQ_2
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES_FOLLOW_TEST
FOR EACH ROW
FOLLOWS TRIGGER_SEQ_1
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 INSERT STATEMENT FIRED');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 UPDATE STATEMENT FIRED');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_2 DELETE STATEMENT FIRED');
END IF;
END;
/
INSERT INTO EMPLOYEES_FOLLOW_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,100,80);
Output:
1 row inserted.
TRIGGER_SEQ_1 INSERT STATEMENT FIRED
TRIGGER_SEQ_2 INSERT STATEMENT FIRED
Checking the above steps for UPDATE:
SELECT * FROM EMPLOYEES_FOLLOW_TEST
WHERE EMPLOYEE_ID = 208;
UPDATE EMPLOYEES_FOLLOW_TEST
SET SALARY = 1600
WHERE EMPLOYEE_ID = 208;
1 row updated.
TRIGGER_SEQ_1 UPDATE STATEMENT FIRED
TRIGGER_SEQ_2 UPDATE STATEMENT FIRED
Check the above steps for DELETE:
DELETE EMPLOYEES_FOLLOW_TEST
WHERE EMPLOYEE_ID = 208;
1 row deleted.
TRIGGER_SEQ_1 DELETE STATEMENT FIRED
TRIGGER_SEQ_2 DELETE STATEMENT FIRED
Lets create another trigger TRIGGER_SEQ_3 to whom TRIGGER_SEQ_1 follows.
CREATE OR REPLACE TRIGGER TRIGGER_SEQ_3
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES_FOLLOW_TEST
FOR EACH ROW
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_3 INSERT STATEMENT FIRED');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_3 UPDATE STATEMENT FIRED');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_3 DELETE STATEMENT FIRED');
END IF;
END;
/
So we will redefine the trigger TRIGGER_SEQ_1 which follows TRIGGER_SEQ_3.
CREATE OR REPLACE TRIGGER TRIGGER_SEQ_1
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES_FOLLOW_TEST
FOR EACH ROW
FOLLOWS TRIGGER_SEQ_3
BEGIN
IF INSERTING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 INSERT STATEMENT FIRED');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 UPDATE STATEMENT FIRED');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE('TRIGGER_SEQ_1 DELETE STATEMENT FIRED');
END IF;
END;
/
INSERT INTO EMPLOYEES_FOLLOW_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,100,80);
1 row inserted.
TRIGGER_SEQ_3 INSERT STATEMENT FIRED
TRIGGER_SEQ_1 INSERT STATEMENT FIRED
TRIGGER_SEQ_2 INSERT STATEMENT FIRED
UPDATE EMPLOYEES_FOLLOW_TEST
SET SALARY = 1600
WHERE EMPLOYEE_ID = 208;
1 row updated.
TRIGGER_SEQ_3 UPDATE STATEMENT FIRED
TRIGGER_SEQ_1 UPDATE STATEMENT FIRED
TRIGGER_SEQ_2 UPDATE STATEMENT FIRED
DELETE EMPLOYEES_FOLLOW_TEST
WHERE EMPLOYEE_ID = 208;
1 row deleted.
TRIGGER_SEQ_3 DELETE STATEMENT FIRED
TRIGGER_SEQ_1 DELETE STATEMENT FIRED
TRIGGER_SEQ_2 DELETE STATEMENT FIRED
COMPOUND TRIGGERS:
CREATE TABLE EMPLOYEE_MASTER
AS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES;
CREATE TABLE EMP_AUDIT_LOG
(
AUDIT_ID NUMBER,
EMPLOYEE_ID NUMBER,
AUDIT_OPS VARCHAR2(100),
USERID VARCHAR2(100),
AUDIT_DATE DATE,
REMARKS VARCHAR2(4000)
);
CREATE TABLE EMPLOYEES_COMPOUND_TEST
(
EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
FULL_NAME VARCHAR2(60),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
MANAGER_NAME VARCHAR2(60),
DEPARTMENT_ID NUMBER(4,0)
);
CREATE TABLE EMPLOYEES_COMPOUND_TEST_HST
AS
SELECT * FROM EMPLOYEES_COMPOUND_TEST;
CREATE OR REPLACE TRIGGER TRIG_EMPLOYEES_COMPOUND_TEST
FOR INSERT OR UPDATE ON EMPLOYEES_COMPOUND_TEST
COMPOUND TRIGGER
TYPE T_AUDIT_TAB IS TABLE OF EMP_AUDIT_LOG%ROWTYPE INDEX BY PLS_INTEGER;
V_AUDIT_TAB T_AUDIT_TAB;
FUNCTION FUN_CHECK_DUPLICATE(P_EMPLOYEE_ID EMPLOYEE_MASTER.EMPLOYEE_ID%TYPE)
RETURN BOOLEAN
IS
V_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM EMPLOYEE_MASTER
WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
IF V_COUNT > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END FUN_CHECK_DUPLICATE;
PROCEDURE SP_INSERT_EMP_AUDIT_LOG
(
P_AUDIT_SEQ NUMBER,
P_EMPLOYEE_ID NUMBER,
P_AUDIT_OPS VARCHAR2,
P_USERID VARCHAR2,
P_AUDIT_DATE DATE,
P_REMARKS VARCHAR2
)
IS
BEGIN
V_AUDIT_TAB(P_AUDIT_SEQ).AUDIT_ID := P_AUDIT_SEQ;
V_AUDIT_TAB(P_AUDIT_SEQ).EMPLOYEE_ID := P_EMPLOYEE_ID;
V_AUDIT_TAB(P_AUDIT_SEQ).AUDIT_OPS := P_AUDIT_OPS;
V_AUDIT_TAB(P_AUDIT_SEQ).USERID := P_USERID;
V_AUDIT_TAB(P_AUDIT_SEQ).AUDIT_DATE := P_AUDIT_DATE;
V_AUDIT_TAB(P_AUDIT_SEQ).REMARKS := P_REMARKS;
EXCEPTION
WHEN OTHERS THEN
NULL;
END SP_INSERT_EMP_AUDIT_LOG;
BEFORE STATEMENT IS
BEGIN
DELETE EMP_AUDIT_LOG;
IF INSERTING THEN
SP_INSERT_EMP_AUDIT_LOG(1,
NULL,
'INSERT',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'EMP_AUDIT_LOG DELETED'
);
ELSIF UPDATING THEN
SP_INSERT_EMP_AUDIT_LOG(1,
NULL,
'UPDATE',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'EMP_AUDIT_LOG DELETED'
);
END IF;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
V_COUNT NUMBER := 0;
EMPLOYEE_EXIST EXCEPTION;
V_MANAGER_NAME VARCHAR2(60);
BEGIN
IF INSERTING THEN
IF NOT FUN_CHECK_DUPLICATE(:NEW.EMPLOYEE_ID) THEN
RAISE EMPLOYEE_EXIST;
ELSE
:NEW.FULL_NAME := UPPER(:NEW.FIRST_NAME||' '||:NEW.LAST_NAME);
BEGIN
SELECT FIRST_NAME||' '||LAST_NAME
INTO V_MANAGER_NAME
FROM EMPLOYEE_MASTER
WHERE EMPLOYEE_ID = :NEW.MANAGER_ID;
EXCEPTION
WHEN OTHERS THEN
V_MANAGER_NAME := NULL;
END;
:NEW.MANAGER_NAME := UPPER(V_MANAGER_NAME);
SP_INSERT_EMP_AUDIT_LOG(2,
:NEW.EMPLOYEE_ID,
'INSERT',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'EMPLOEE DOES NOT EXISTS..PROCEED FOR INSERTION'
);
END IF;
ELSIF UPDATING THEN
SP_INSERT_EMP_AUDIT_LOG(2,
:NEW.EMPLOYEE_ID,
'UPDATE',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'PROCEED FOR UPDATION'
);
END IF;
EXCEPTION
WHEN EMPLOYEE_EXIST THEN
SP_INSERT_EMP_AUDIT_LOG(2,
:NEW.EMPLOYEE_ID,
'INSERT',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'EMPLOEE ALREADY EXISTS EXCEPTION IS RAISED.'
);
RAISE_APPLICATION_ERROR(-20202, 'EMPLOYEE ALREADY EXISTS');
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
IF INSERTING THEN
INSERT INTO EMPLOYEES_COMPOUND_TEST_HST(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, FULL_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, MANAGER_NAME, DEPARTMENT_ID)
VALUES(:NEW.EMPLOYEE_ID, :NEW.FIRST_NAME, :NEW.LAST_NAME, :NEW.FULL_NAME, :NEW.EMAIL, :NEW.PHONE_NUMBER, :NEW.HIRE_DATE, :NEW.JOB_ID, :NEW.SALARY, :NEW.COMMISSION_PCT, :NEW.MANAGER_ID, :NEW.MANAGER_NAME, :NEW.DEPARTMENT_ID);
SP_INSERT_EMP_AUDIT_LOG(3,
:NEW.EMPLOYEE_ID,
'INSERT',
SYS_CONTEXT('USERENV','OS_USER'),
SYSDATE,
'DATA INSERTED INTO EMPLOYEES_COMPOUND_TEST_HST TABLE'
);
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FORALL I IN 1..V_AUDIT_TAB.COUNT
INSERT INTO EMP_AUDIT_LOG(AUDIT_ID,
EMPLOYEE_ID,
AUDIT_OPS,
USERID,
AUDIT_DATE,
REMARKS)
VALUES(V_AUDIT_TAB(I).AUDIT_ID,
V_AUDIT_TAB(I).EMPLOYEE_ID,
V_AUDIT_TAB(I).AUDIT_OPS,
V_AUDIT_TAB(I).USERID,
V_AUDIT_TAB(I).AUDIT_DATE,
V_AUDIT_TAB(I).REMARKS
);
END AFTER STATEMENT;
END;
/
INSERT INTO EMPLOYEES_COMPOUND_TEST (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Steven','George','SGEORGE','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,100,80);
SELECT * FROM EMPLOYEES_COMPOUND_TEST;
SELECT * FROM EMPLOYEES_COMPOUND_TEST_HST;
SELECT * FROM EMP_AUDIT_LOG;
RELATED TOPICS:
