Working With DML TRIGGERS In Oracle
TRIGGER DEFINITION:
A Trigger
- is a PLSQL block or a PLSQL procedure associated with a Table, View, Schema or Database.
- Executes implicitly whenever a particular event takes place. The event can be
- a data event such as DML
- a system event such as logon or shut down occurs on a schema or database.
- Using triggers guarantee that whenever specific operation takes place, related actions are performed.
- Do not defined triggers to implement integrity rules that are already implemented with declarative constraint like primary key, foreign key or any other constraints.
- Before defining any trigger please make sure that the same integrity rule is already defined in any other trigger or not. In this way we can remove the duplicacy action via trigger.
- If the logic inside a trigger is very length then we can modularize the code by putting the logic inside a stored procedure and calls it through tirgger.
- The database trigger fires irrespective of user each time the event occurs on which the trigger is created.
DML TRIGGERS:
The triggering statement contains the following parts:
| PARTS | DESCRIPTION | POSSIBLE VALUES |
|---|---|---|
| Trigger Timing | When the trigger fires due to the triggering event. | For Table BEFORE AFTER For View INSTEAD OF |
| Triggering Event | Which DML operation on Table or View causes the trigger to fire | INSERT UPDATE DELETE |
| Trigger Type | No of times the trigger executes | STATEMENT ROW |
| WHEN clause | Restricting condition | |
| Trigger Body | Actions to be performed by trigger | Complete PLSQL block |
BEFORE DML TRIGGER:
Trigger body is executed before the triggering DML event occurred on a table.
Example:
create or replace trigger emp_trig_update_sal
before update on employees
begin
dbms_output.put_line('Employees table to be updated');
end;
/
Check the calling:
set serveroutput on;
update employees
set salary = salary
where employee_id = 100;
1 row updated.
Employees table to be updated
AFTER DML TRIGGER:
create or replace trigger emp_trig_update_sal_after
after update on employees
begin
dbms_output.put_line('Employees table is updated');
end;
/
update employees
set salary = salary
where employee_id = 100;
1 row updated.
Employees table to be updated
Employees table is updated
DROP TRIGGER emp_trig_update_sal;
DROP TRIGGER emp_trig_update_sal_after;
More Example of BEFORE & AFTER TRIGGER:
create or replace trigger emp_trig_before_dml
before insert or update or delete on employees
begin
case
when inserting then
dbms_output.put_line('Employees table to be populated');
when updating then
dbms_output.put_line('Employees table to be updated');
when deleting then
dbms_output.put_line('Employees table to be deleted');
end case;
end;
/
BEGIN
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (207,'Steve','Smith','SSMITH','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'AD_PRES',2650,NULL,NULL,90);
UPDATE EMPLOYEES
SET SALARY = SALARY
WHERE EMPLOYEE_ID = 207;
DELETE EMPLOYEES
WHERE EMPLOYEE_ID = 207;
END;
/
PL/SQL procedure successfully completed.
Employees table to be populated
Employees table to be updated
Employees table to be deleted
DROP TRIGGER emp_trig_before_dml;
create or replace trigger emp_trig_after_dml
after insert or update or delete on employees
begin
case
when inserting then
dbms_output.put_line('Employees table is populated');
when updating then
dbms_output.put_line('Employees table is updated');
when deleting then
dbms_output.put_line('Employees table is deleted');
end case;
end;
/
BEGIN
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (207,'Steve','Smith','SSMITH','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'AD_PRES',2650,NULL,NULL,90);
UPDATE EMPLOYEES
SET SALARY = SALARY
WHERE EMPLOYEE_ID = 207;
DELETE EMPLOYEES
WHERE EMPLOYEE_ID = 207;
END;
/
PL/SQL procedure successfully completed.
Employees table is populated
Employees table is updated
Employees table is deleted
drop trigger emp_trig_after_dml;
TRIGGER TYPES:
- STATEMENT LEVEL
- ROW LEVEL
STATEMENT LEVEL TRIGGER:
- Trigger body executes only once
- This is the default type
- Whatever be the no of rows affected, the trigger body will execute only once
- If no rows affected by DML statement then also the trigger body will execute.
ROW LEVEL TRIGGER:
- Trigger body executes for each rows affected in the DML statement.
- Trigger will not execute if there is no row affected
Check the following examples:
STATEMENT LEVEL:
create or replace trigger emp_trig_after_dml_statement
before insert or update or delete on employees
begin
case
when inserting then
dbms_output.put_line('Employees table is populated..statement');
when updating then
dbms_output.put_line('Employees table is updated..statement');
when deleting then
dbms_output.put_line('Employees table is deleted..statement');
end case;
end;
/
ROW LEVEL:
create or replace trigger emp_trig_after_dml_row
after insert or update or delete on employees
for each row
begin
case
when inserting then
dbms_output.put_line('Employees table is populated..row');
when updating then
dbms_output.put_line('Employees table is updated..row');
when deleting then
dbms_output.put_line('Employees table is deleted..row');
end case;
end;
/
CALLING:
BEGIN
DBMS_OUTPUT.PUT_LINE('---------------INSERT 1---------------------------');
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (207,'Steve','Smith','SSMITH','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'AD_PRES',2650,NULL,NULL,90);
DBMS_OUTPUT.PUT_LINE('---------------INSERT 2---------------------------');
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (208,'Jhon','Smith','JSMITH','515.123.8888',TO_DATE('17-12-03','DD-MM-RR'),'AD_PRES',2660,NULL,NULL,90);
DBMS_OUTPUT.PUT_LINE('---------------UPDATE---------------------------');
UPDATE EMPLOYEES
SET SALARY = SALARY
WHERE EMPLOYEE_ID IN (207, 208);
DBMS_OUTPUT.PUT_LINE('---------------DELETE---------------------------');
DELETE EMPLOYEES
WHERE EMPLOYEE_ID IN (207, 208);
END;
/
PL/SQL procedure successfully completed.
---------------INSERT 1---------------------------
Employees table is populated..statement
Employees table is populated..row
---------------INSERT 2---------------------------
Employees table is populated..statement
Employees table is populated..row
---------------UPDATE---------------------------
Employees table is updated..statement
Employees table is updated..row
Employees table is updated..row
---------------DELETE---------------------------
Employees table is deleted..statement
Employees table is deleted..row
Employees table is deleted..row
In the above example for Statement Level Trigger all the time trigger body executed only once irrespective of the no of records.
But for Row Level Trigger, trigger body executed for each record affected by the DML statement. There are 2 separate insert statements, so both statement and row level triggers are executed once for each statement.
But for update and delete single statement consists 2 records. So statement level trigger executed only once but row level trigger executed twice for update and twice for delete.
DROP TRIGGER EMP_TRIG_AFTER_DML_ROW;
DROP TRIGGER EMP_TRIG_AFTER_DML_STATEMENT;
Trigger based on a single column:
create or replace trigger emp_trig_before_single_column_row
before insert or update of salary on employees
for each row
begin
case
when inserting then
if :new.job_id = 'AD_PRES' and :new.salary > 2000 then
raise_application_error(-20202, 'AD_PRES cannot earn more than 2000');
end if;
when updating then
if :new.job_id = 'ST_MAN' and :new.salary > 1000 then
raise_application_error(-20203, 'ST_MAN cannot earn more than 1000');
end if;
end case;
end;
/
update employees
set salary = 1100
where job_id = 'ST_MAN';
Error starting at line : 19 in command -
update employees
set salary = 1100
where job_id = 'ST_MAN'
Error report -
SQL Error: ORA-20203: ST_MAN cannot earn more than 1000
ORA-06512: at "HR.EMP_TRIG_BEFORE_SINGLE_COLUMN_ROW", line 9
ORA-04088: error during execution of trigger 'HR.EMP_TRIG_BEFORE_SINGLE_COLUMN_ROW'
DROP TRIGGER EMP_TRIG_BEFORE_SINGLE_COLUMN_ROW;
WHEN CLAUSE:
create or replace trigger emp_trig_before_single_column_when_clause_row
before insert or update of salary on employees
for each row
when (new.job_id = 'SA_REP')
begin
if inserting then
if :new.salary > 1500 then
:new.commission_pct := 0;
elsif :old.commission_pct is null then
:new.commission_pct := 0;
elsif :old.salary > 1100 then
:new.commission_pct := 0;
else
:new.commission_pct := :old.commission_pct + 0.05;
end if;
end if;
if updating then
:new.commission_pct := :old.commission_pct;
end if;
end;
/
select * from employees
where job_id = 'SA_REP'
and salary > 1500
union
select * from employees
where job_id = 'SA_REP'
and commission_pct is null
union
select * from employees
where job_id = 'SA_REP'
and salary > 1100;
begin
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES (207,'Steve','Smith','SSMITH','515.123.9999',TO_DATE('17-12-03','DD-MM-RR'),'SA_REP',1550,NULL,NULL,80);
update employees
set salary = salary * 1.2
where job_id = 'SA_REP'
and salary > 1100 and salary < 1500;
end;
/
select * from employees
where job_id = 'SA_REP'
and salary > 1500
union
select * from employees
where job_id = 'SA_REP'
and commission_pct is null
union
select * from employees
where job_id = 'SA_REP'
and salary > 1100;
DATA DICTIONARY:
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER';
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, AUTHID
FROM USER_PROCEDURES
WHERE OBJECT_TYPE = 'TRIGGER';
SELECT * FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'EMP_TRIG_BEFORE_SINGLE_COLUMN_WHEN_CLAUSE_ROW';
SELECT CASE WHEN LINE = 1 THEN NAME ELSE ' ' END NAME,
CASE WHEN LINE = 1 THEN TYPE ELSE ' ' END TYPE,
LINE,
TEXT
FROM USER_SOURCE
WHERE NAME = 'EMP_TRIG_BEFORE_SINGLE_COLUMN_WHEN_CLAUSE_ROW';
INSTEAD OF TRIGGER:
A view cannot be modified by normal DML statements if the view contains
- Set operators
- Group functions
- Contains GROUP BY, CONNECT BY, START WITH, DISTINCT operators or JOINs.
If a view consists of multiple tables, then an insert to the view may results an insertion into one table and update to another table. In this case we will write an Instead of Trigger that fires when you write an insert against the view. Instead of original insertion to the view, the trigger body executes and that results in an insertion to one underline table and an update to another.
Example:
CREATE TABLE NEW_EMPLOYEES
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
HIRE_DATE,
JOB_ID,
SALARY,
MANAGER_ID
DEPARTMENT_ID
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;
CREATE TABLE NEW_DEPARTMENTS
AS
SELECT A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID = A.MANAGER_ID) MANAGER_NAME,
SUM(B.SALARY) SUM_SALARY,
COUNT(B.EMPLOYEE_ID) TOTAL_EMPLOYEES
FROM DEPARTMENTS A, EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
GROUP BY A.DEPARTMENT_ID,
A.DEPARTMENT_NAME,
A.MANAGER_ID;
CREATE OR REPLACE VIEW EMPLOYEE_DETAILS
AS
SELECT A.EMPLOYEE_ID,
A.FIRST_NAME,
A.LAST_NAME,
A.EMAIL,
A.HIRE_DATE,
A.JOB_ID,
A.SALARY,
A.MANAGER_ID,
A.DEPARTMENT_ID,
B.DEPARTMENT_NAME
FROM EMPLOYEES A,
DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
CREATE OR REPLACE TRIGGER NEW_EMPLOYEES_TRIG
INSTEAD OF INSERT OR UPDATE OR DELETE ON EMPLOYEE_DETAILS
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
BEGIN
IF INSERTING THEN
INSERT INTO NEW_EMPLOYEES (EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
HIRE_DATE,
JOB_ID,
SALARY,
MANAGER_ID,
DEPARTMENT_ID
)
VALUES(:NEW.EMPLOYEE_ID,
:NEW.FIRST_NAME,
:NEW.LAST_NAME,
:NEW.EMAIL,
SYSDATE,
:NEW.JOB_ID,
:NEW.SALARY,
:NEW.MANAGER_ID,
:NEW.DEPARTMENT_ID
);
SELECT COUNT(1)
INTO V_COUNT
FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;
IF V_COUNT > 0 THEN
UPDATE NEW_DEPARTMENTS
SET SUM_SALARY = SUM_SALARY + :NEW.SALARY,
TOTAL_EMPLOYEES = TOTAL_EMPLOYEES + 1
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;
ELSE
INSERT INTO NEW_DEPARTMENTS(DEPARTMENT_ID,
DEPARTMENT_NAME,
MANAGER_ID,
MANAGER_NAME,
SUM_SALARY,
TOTAL_EMPLOYEES
)
VALUES(:NEW.DEPARTMENT_ID,
:NEW.DEPARTMENT_NAME,
:NEW.MANAGER_ID,
(SELECT FIRST_NAME||' '||LAST_NAME
FROM NEW_EMPLOYEES
WHERE EMPLOYEE_ID = :NEW.MANAGER_ID),
:NEW.SALARY,
1
);
END IF;
ELSIF DELETING THEN
DELETE NEW_EMPLOYEES
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
UPDATE NEW_DEPARTMENTS
SET SUM_SALARY = SUM_SALARY - :OLD.SALARY,
TOTAL_EMPLOYEES = TOTAL_EMPLOYEES - 1
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
ELSIF UPDATING ('SALARY') THEN
UPDATE NEW_EMPLOYEES
SET SALARY = :NEW.SALARY
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
UPDATE NEW_DEPARTMENTS
SET SUM_SALARY = SUM_SALARY + (:NEW.SALARY - :OLD.SALARY)
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
ELSIF UPDATING ('DEPARTMENT_ID') THEN
UPDATE NEW_EMPLOYEES
SET DEPARTMENT_ID = :NEW.DEPARTMENT_ID
WHERE EMPLOYEE_ID = :OLD.EMPLOYEE_ID;
UPDATE NEW_DEPARTMENTS
SET SUM_SALARY = SUM_SALARY - :OLD.SALARY,
TOTAL_EMPLOYEES = TOTAL_EMPLOYEES - 1
WHERE DEPARTMENT_ID = :OLD.DEPARTMENT_ID;
UPDATE NEW_DEPARTMENTS
SET SUM_SALARY = SUM_SALARY + :NEW.SALARY,
TOTAL_EMPLOYEES = TOTAL_EMPLOYEES + 1
WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID;
END IF;
END;
/
NOTE: For simplicity, UPDATE and DELETE operations are done on existing records of EMPLOYEE_DETAILS view. Since EMPLOYEE_DETAILS view is based on EMPLOYEES and DEPARTMENTS tables, any DML impact on NEW_EMPLOYEES and NEW_DEPARTMENTS tables will not affect the EMPLOYEE_DETAILS view.
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID = 90;
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = 90;
BEGIN
INSERT INTO EMPLOYEE_DETAILS (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES (207,'Steve','Smith','SSMITH',SYSDATE,'AD_PRES',1550,100,90, 'Executive');
END;
/
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID = 90;
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = 90;
UPDATING SALARY:
UPDATE EMPLOYEE_DETAILS
SET SALARY = 1950
WHERE EMPLOYEE_ID = 102;
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID = 90;
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = 90;
UPDATE DEPARTMENT_ID:
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20);
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID IN (10, 20);
UPDATE EMPLOYEE_DETAILS
SET DEPARTMENT_ID = 10
WHERE EMPLOYEE_ID = 201;
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20);
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID IN (10, 20);
DELETE EMPLOYEES:
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID = 30;
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = 30;
DELETE EMPLOYEE_DETAILS
WHERE EMPLOYEE_ID = 119;
SELECT * FROM NEW_EMPLOYEES
WHERE DEPARTMENT_ID = 30;
SELECT * FROM NEW_DEPARTMENTS
WHERE DEPARTMENT_ID = 30;
USING TRIGGER AS INTEGRITY CONSTRAINT:
CREATE TABLE EMP_INTEGRITY_TEST
(EMPLOYEE_ID NUMBER(8),
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
JOB_ID VARCHAR2(30),
SALARY NUMBER,
HIRE_DATE DATE,
DEPARTMENT_ID NUMBER
);
CREATE OR REPLACE TRIGGER TRIG_EMP_INTEGRITY_TEST
BEFORE INSERT OR UPDATE ON EMP_INTEGRITY_TEST
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
BEGIN
IF INSERTING THEN
SELECT COUNT(1)
INTO V_COUNT
FROM EMP_INTEGRITY_TEST
WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
IF V_COUNT > 0 THEN
RAISE_APPLICATION_ERROR(-20202, 'EMPLOYEE ALREADY EXISTS');
END IF;
IF :NEW.HIRE_DATE <> SYSDATE THEN
RAISE_APPLICATION_ERROR(-20202, 'HIRE DATE SHOULD BE CURRENT DATE');
END IF;
IF :NEW.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
RAISE_APPLICATION_ERROR(-20202, 'SA_REP CANNOT EARN MORE THAN 1000');
END IF;
ELSIF UPDATING THEN
IF :OLD.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
RAISE_APPLICATION_ERROR(-20202, 'SA_REP CANNOT EARN MORE THAN 1000');
END IF;
END IF;
END;
/
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE-1,
80);
Error report -
SQL Error: ORA-20202: HIRE DATE SHOULD BE CURRENT DATE
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_TEST", line 15
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_TEST'
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE,
80);
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_TEST", line 19
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_TEST'
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
900,
SYSDATE,
80);
1 row inserted.
Try to insert same record again:
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
900,
SYSDATE,
80);
Error report -
SQL Error: ORA-20202: EMPLOYEE ALREADY EXISTS
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_TEST", line 11
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_TEST'
UPDATE TEST:
SELECT * FROM EMP_INTEGRITY_TEST;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1500
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_TEST", line 23
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_TEST'
DROP TRIGGER TRIG_EMP_INTEGRITY_TEST;
TRUNCATE TABLE EMP_INTEGRITY_TEST;
TRIGGER TO KEEP AUDIT TRAIL RECORDS:
CREATE TABLE EMP_AUDIT_LOG
(AUDIT_ID NUMBER GENERATED ALWAYS AS IDENTITY,
EMPLOYEE_ID NUMBER,
AUDIT_OPS VARCHAR2(100),
USERID VARCHAR2(100),
AUDIT_DATE DATE,
REMARKS VARCHAR2(4000)
);
CREATE OR REPLACE PROCEDURE SP_INSERT_EMP_AUDIT_LOG
(
P_EMPLOYEE_ID NUMBER,
P_AUDIT_OPS VARCHAR2,
P_USERID VARCHAR2,
P_AUDIT_DATE DATE,
P_REMARKS VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMP_AUDIT_LOG(EMPLOYEE_ID,
AUDIT_OPS,
USERID,
AUDIT_DATE,
REMARKS
)
VALUES(P_EMPLOYEE_ID,
P_AUDIT_OPS,
P_USERID,
P_AUDIT_DATE,
P_REMARKS
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE TRIGGER TRIG_EMP_INTEGRITY_AUDIT_TEST
BEFORE INSERT OR UPDATE OR DELETE ON EMP_INTEGRITY_TEST
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
BEGIN
IF INSERTING THEN
SELECT COUNT(1)
INTO V_COUNT
FROM EMP_INTEGRITY_TEST
WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
IF V_COUNT > 0 THEN
SP_INSERT_EMP_AUDIT_LOG(:NEW.EMPLOYEE_ID,
'INSERT',
'SYSTEM',
SYSDATE,
'EMPLOYEE ALREADY EXISTS'
);
RAISE_APPLICATION_ERROR(-20202, 'EMPLOYEE ALREADY EXISTS');
ELSE
IF :NEW.HIRE_DATE <> SYSDATE THEN
SP_INSERT_EMP_AUDIT_LOG(:NEW.EMPLOYEE_ID,
'INSERT',
'SYSTEM',
SYSDATE,
'HIRE DATE SHOULD BE CURRENT DATE'
);
RAISE_APPLICATION_ERROR(-20202, 'HIRE DATE SHOULD BE CURRENT DATE');
ELSIF :NEW.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_INSERT_EMP_AUDIT_LOG(:NEW.EMPLOYEE_ID,
'INSERT',
'SYSTEM',
SYSDATE,
'SA_REP CANNOT EARN MORE THAN 1000'
);
RAISE_APPLICATION_ERROR(-20202, 'SA_REP CANNOT EARN MORE THAN 1000');
ELSE
SP_INSERT_EMP_AUDIT_LOG(:NEW.EMPLOYEE_ID,
'INSERT',
'SYSTEM',
SYSDATE,
'RECORD INSERTED'
);
END IF;
END IF;
ELSIF UPDATING THEN
IF :OLD.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_INSERT_EMP_AUDIT_LOG(:OLD.EMPLOYEE_ID,
'UPDATE',
'SYSTEM',
SYSDATE,
'SA_REP CANNOT EARN MORE THAN 1000'
);
RAISE_APPLICATION_ERROR(-20202, 'SA_REP CANNOT EARN MORE THAN 1000');
ELSE
SP_INSERT_EMP_AUDIT_LOG(:NEW.EMPLOYEE_ID,
'UPDATE',
'SYSTEM',
SYSDATE,
'RECORD UPDATED'
);
END IF;
ELSIF DELETING THEN
SP_INSERT_EMP_AUDIT_LOG(:OLD.EMPLOYEE_ID,
'DELETE',
'SYSTEM',
SYSDATE,
'RECORD DELETED'
);
END IF;
END;
/
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE-1,
80);
Error report -
SQL Error: ORA-20202: HIRE DATE SHOULD BE CURRENT DATE
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 26
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE,
80);
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 34
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
900,
SYSDATE,
80);
1 row inserted.
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1500
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 52
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 990
WHERE EMPLOYEE_ID = 100;
1 row updated.
SELECT * FROM EMP_AUDIT_LOG;
DROP TRIGGER TRIG_EMP_INTEGRITY_AUDIT_TEST;
DROP PROCEDURE SP_INSERT_EMP_AUDIT_LOG;
TRUNCATE TABLE EMP_INTEGRITY_TEST;
TRUNCATE TABLE EMP_AUDIT_LOG;
DEFINING FUNCTION OR PROCEDURE INSIDE A TRIGGER:
CREATE OR REPLACE TRIGGER TRIG_EMP_INTEGRITY_AUDIT_TEST
BEFORE INSERT OR UPDATE OR DELETE ON EMP_INTEGRITY_TEST
FOR EACH ROW
DECLARE
V_COUNT NUMBER := 0;
PROCEDURE SP_INSERT_EMP_AUDIT_LOG
(
P_EMPLOYEE_ID NUMBER,
P_AUDIT_OPS VARCHAR2,
P_USERID VARCHAR2,
P_AUDIT_DATE DATE,
P_REMARKS VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMP_AUDIT_LOG(EMPLOYEE_ID,
AUDIT_OPS,
USERID,
AUDIT_DATE,
REMARKS
)
VALUES(P_EMPLOYEE_ID,
P_AUDIT_OPS,
P_USERID,
P_AUDIT_DATE,
P_REMARKS
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
FUNCTION FUN_CHECK_DUPLICATE(P_EMPLOYEE_ID EMP_INTEGRITY_TEST.EMPLOYEE_ID%TYPE)
RETURN BOOLEAN
IS
V_COUNT NUMBER := 0;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM EMP_INTEGRITY_TEST
WHERE EMPLOYEE_ID = :NEW.EMPLOYEE_ID;
IF V_COUNT > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
PROCEDURE SP_TRIGGER_ACTION(P_EMPLOYEE_ID EMP_INTEGRITY_TEST.EMPLOYEE_ID%TYPE,
P_ACTION VARCHAR2,
P_REMARKS VARCHAR2,
P_SUCCESS VARCHAR2
)
IS
BEGIN
SP_INSERT_EMP_AUDIT_LOG(P_EMPLOYEE_ID,
P_ACTION,
'SYSTEM',
SYSDATE,
P_REMARKS
);
IF P_SUCCESS <> 'Y' THEN
RAISE_APPLICATION_ERROR(-20202, P_REMARKS);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
BEGIN
IF INSERTING THEN
IF NOT FUN_CHECK_DUPLICATE(:NEW.EMPLOYEE_ID) THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'EMPLOYEE ALREADY EXISTS',
'N'
);
ELSE
IF :NEW.HIRE_DATE <> SYSDATE THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'HIRE DATE SHOULD BE CURRENT DATE',
'N'
);
ELSIF :NEW.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'SA_REP CANNOT EARN MORE THAN 1000',
'N'
);
ELSE
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'INSERT',
'RECORD INSERTED',
'Y'
);
END IF;
END IF;
ELSIF UPDATING THEN
IF :OLD.JOB_ID = 'SA_REP' AND :NEW.SALARY > 1000 THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'UPDATE',
'SA_REP CANNOT EARN MORE THAN 1000',
'N'
);
ELSE
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'UPDATE',
'RECORD UPDATED',
'Y'
);
END IF;
ELSIF DELETING THEN
SP_TRIGGER_ACTION(:NEW.EMPLOYEE_ID,
'DELETE',
'RECORD DELETED',
'Y'
);
END IF;
END;
/
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE-1,
80);
Error report -
SQL Error: ORA-20202: HIRE DATE SHOULD BE CURRENT DATE
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 63
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 60
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 75
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
1200,
SYSDATE,
80);
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 63
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 60
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 80
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
INSERT INTO EMP_INTEGRITY_TEST(EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY,
HIRE_DATE,
DEPARTMENT_ID
)
VALUES(100,
'STEVEN',
'SMITH',
'SA_REP',
950,
SYSDATE,
80);
1 row inserted.
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1500
WHERE EMPLOYEE_ID = 100;
Error report -
SQL Error: ORA-20202: SA_REP CANNOT EARN MORE THAN 1000
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 66
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 62
ORA-06512: at "HR.TRIG_EMP_INTEGRITY_AUDIT_TEST", line 100
ORA-04088: error during execution of trigger 'HR.TRIG_EMP_INTEGRITY_AUDIT_TEST'
SELECT * FROM EMP_AUDIT_LOG;
UPDATE EMP_INTEGRITY_TEST
SET SALARY = 1000
WHERE EMPLOYEE_ID = 100;
1 row updated.
SELECT * FROM EMP_AUDIT_LOG;
RELATED TOPICS: