Creating Triggers
- Types of Triggers
A Trigger :
- Is a PL/SQL block or a PL/SQL Procedure associated with a Table,view,schema.
- 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 occurs
Triggers can be:
DML triggers on tables.
INSTEAD OF triggers on views.
System triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA, triggers fire for each event for that specific user.
There are four types of Database Triggers:
- Table – Level Triggers
- View – Level Triggers
- Database Level Triggers
- Session – Level Triggers
- Creating DML Statement Triggers
— PL/SQL Statement Level Trigger to restrict inserts into Employee Master Table only on certain business days and not on SAT and SUN —
CREATE TABLE employee_master AS SELECT * FROM emp WHERE 1 = 2
/
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employee_master
BEGIN
IF (TO_CHAR(SYSDATE,’DY’) IN (‘MON’)) THEN
RAISE_APPLICATION_ERROR(-20500,’You may Insert into Employee Master Table only during Business Days.’);
END IF;
END;
/
INSERT INTO employee_master(empno,hiredate)VALUES(8000,SYSDATE)
/
SELECT * FROM employee_master
/
— PL/SQL Statement Level Trigger to restrict inserts/deletes/updates into EMP Table only on certain business days and not on SAT and SUN —
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employee_master
BEGIN
IF (TO_CHAR(SYSDATE,’DY’) IN (‘TUE’,’WED’)) THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR(-20500,’You may Delete from Employee Master Table only during Business Days.’);
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR(-20501,’You may Insert into Employee Master Table only during Business Days.’);
ELSIF UPDATING THEN
RAISE_APPLICATION_ERROR(-20502,’You may Update Salary only during Business Days.’);
ELSE
RAISE_APPLICATION_ERROR(-20503,’You may Update Employee Master Table only during Business Days.’);
END IF;
END IF;
END;
/
— Query to retrive the Trigger Details —
SELECT trigger_type,table_owner,triggering_event
FROM user_triggers WHERE table_name = ‘EMPLOYEE_MASTER’
/
INSERT INTO employee_master(EMPNO,HIREDATE)values(8000,SYSDATE)
/
INSERT INTO employee_master(EMPNO,HIREDATE)SELECT empno,hiredate FROM emp
/
DELETE employee_master
/
UPDATE employee_master SET comm = 0
/
DROP TABLE employee_master PURGE
/
— Query to retrive the Trigger Details after the Table is Dropped —
SELECT trigger_type,table_owner,triggering_event
FROM user_triggers WHERE table_name = ‘EMPLOYEE_MASTER’
/
- Creating DML Row Triggers
The new column values are referenced using the new qualifier before the column name, while the old column values are referenced using the old qualifier before the column name.
Record should exist in a Table on which the trigger is fired for a ROW LEVEL Trigger to execute.
For a empty table,ROW LEVEL Trigger will not fire.
CREATE TABLE employee_master AS SELECT * FROM emp
/
— PL/SQL Trigger to allow only certain Employees to be able to earn a Salary of more than 10,000 —
CREATE OR REPLACE TRIGGER restrict_salary
BEFORE INSERT OR UPDATE OF sal ON employee_master
FOR EACH ROW
BEGIN
IF NOT(:NEW.JOB IN(‘PRESIDENT’,’MANAGER’))
AND :NEW.sal > 10000
THEN
RAISE_APPLICATION_ERROR(-20202,’Employee cannot earn this amount’);
END IF;
END;
/
UPDATE employee_master SET sal = 15000
WHERE ENAME = ‘SMITH’
/
UPDATE employee_master SET sal = 15000
WHERE ENAME = ‘KING’
/
INSERT INTO employee_master(EMPNO,JOB,SAL)values(8000,’CONC’,11000)
/
DELETE employee_master
/
DROP TABLE employee_master PURGE
/
— PL/SQL Trigger using the :OLD and :NEW Qualifier and Insert Records into Employee Audit Table —
CREATE TABLE employee_master AS SELECT * FROM emp
/
CREATE TABLE audit_emp_table(user_name VARCHAR2(30),timestamp DATE,id NUMBER,old_ename VARCHAR2(30),
new_ename VARCHAR2(30),old_sal NUMBER,new_sal NUMBER)
/
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employee_master
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table(user_name,timestamp,id,old_ename,new_ename,old_sal,new_sal)
VALUES(‘SCOTT’,SYSDATE,:old.empno,:old.ename,:new.ename,:old.sal,:new.sal);
END;
/
SELECT user_name,timestamp,new_ename,new_sal FROM AUDIT_EMP_TABLE
/
INSERT INTO employee_master(EMPNO,ENAME,JOB,SAL)select empno,ename,job,sal FROM EMP
/
SELECT user_name,timestamp,old_ename,new_ename,new_sal FROM AUDIT_EMP_TABLE
/
UPDATE employee_master SET sal = sal + 4000 WHERE empno = 7369
/
SELECT user_name,timestamp,old_ename,new_ename,old_sal,new_sal FROM AUDIT_EMP_TABLE
/
DELETE employee_master WHERE empno = 7369
/
SELECT user_name,timestamp,old_ename,new_ename,old_sal,new_sal FROM AUDIT_EMP_TABLE
/
DROP TABLE audit_emp_table PURGE
/
Data Operation Old Value New Value
INSERT NULL Inserted Value
UPDATE Value before Update Value after update
DELETE Value before Delete NULL
A commit inside a trigger would defeat the basic definition of an atomic transaction (see ACID). Trigger logic is by definition an extension of the original DML operation. Changes made within triggers should thus be committed or rolled back as part of the transaction in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers).
- Instead of Trigger
Use INSTEAD of Triggers to modify data in which the DML statement has been issued against an nonupdatable view.
These triggers are called INSTEAD OF Triggers because unlike other triggers Oracle Server fires the Trigger instead of executing the triggering statement.INSTEAD OF Triggers are Row Triggers
— Example to demonstrate INSTEAD OF Triggers —
1.
— Create a Non – Updatable View —
CREATE OR REPLACE VIEW v_emp
AS
SELECT DISTINCT empno,ename
FROM emp
ORDER BY ename
/
2.
— Try updating the Ename Column in the View —
UPDATE v_emp
SET ename = ‘SMI’
WHERE empno = 7369
/
3.
— So in order to allow the User to Update the View we can create a INSTEAD OF Trigger —
CREATE OR REPLACE TRIGGER v_emp_iu
INSTEAD OF UPDATE ON v_emp
FOR EACH ROW — By Default INSTEAD OF Trigger takes FOR EACH ROW —
DECLARE
v_error_txt VARCHAR2(150);
BEGIN
IF updating(‘EMPNO’) THEN
v_error_txt:=’You cannot update the PK!’;
RAISE_APPLICATION_ERROR(-20999,v_error_txt);
ELSE
UPDATE emp
SET ename = :new.ename;
–WHERE empno = :old.empno;
END IF;
END v_emp_iu;
/
4.
— Now try updating the Ename Column in the View after Trigger is created —
UPDATE v_emp
SET ename = ‘SMI’
WHERE empno = 7369
/
UPDATE v_emp
SET empno = 8000
WHERE empno = 7369
/
5.
— Select the table to see the changes —
SELECT * FROM emp
/
— Example to demonstrate INSTEAD OF Triggers —
1.
— Create Employee Master Table —
CREATE TABLE employee_master AS SELECT empno,ename,sal,deptno,job,hiredate FROM EMP
/
2.
— Create Department Master Table —
CREATE TABLE department_master
AS
SELECT d.deptno,d.dname,sum(e.sal) tot_dept_sal
FROM emp e,dept d WHERE e.deptno = d.deptno
GROUP BY d.deptno,d.dname
/
3.
— Create a Complex View —
CREATE OR REPLACE VIEW emp_dept_v
AS
SELECT e.empno,e.ename,e.sal,e.deptno,e.job,d.dname
FROM
employee_master e,department_master d WHERE e.deptno = d.deptno
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
SELECT * FROM emp_dept_v
/
4.
— Create a PL/SQL Trigger using the INSTEAD OF Trigger —
CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT OR UPDATE OR DELETE on emp_dept_v
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO employee_master VALUES (:NEW.empno,:NEW.ename,:NEW.sal,:NEW.deptno,:NEW.job,SYSDATE);
UPDATE department_master
SET tot_dept_sal = tot_dept_sal + :NEW.sal
WHERE deptno = :NEW.deptno;
ELSIF DELETING THEN
DELETE FROM employee_master
WHERE empno = :OLD.empno;
UPDATE department_master
SET tot_dept_sal = tot_dept_sal - :OLD.sal
WHERE deptno = :OLD.deptno;
ELSIF UPDATING('SAL') THEN
UPDATE employee_master
SET sal = :NEW.sal
WHERE empno = :OLD.empno;
UPDATE department_master
SET tot_dept_sal = tot_dept_sal + (:NEW.sal - :OLD.sal)
WHERE deptno = :OLD.deptno;
ELSIF UPDATING('deptno') THEN
UPDATE employee_master
SET deptno = :NEW.deptno
WHERE empno = :OLD.empno;
UPDATE department_master
SET deptno = :NEW.deptno
WHERE deptno = :OLD.deptno;
END IF;END;
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
SELECT * FROM emp_dept_v
/
5.
— Check the results while Inserting —
INSERT INTO emp_dept_v VALUES(8000,’Rajesh’,5001,10,’CONC’,’ERP’)
/
Points to be noted :
a.INSERT INTO employee_master VALUES(:NEW.empno,:NEW.ename,:NEW.sal,:NEW.deptno,:NEW.job,SYSDATE);
b.UPDATE department_master
SET tot_dept_sal = tot_dept_sal + :NEW.sal
WHERE deptno = :NEW.deptno;
SELECT * FROM emp_dept_v
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
6.
— Check the results while Deleting —
DELETE FROM emp_dept_v WHERE empno = 8000
/
Points to be noted :
a. DELETE FROM employee_master
WHERE empno = :NEW.empno;
b. UPDATE department_master
SET tot_dept_sal = tot_dept_sal – :OLD.sal
WHERE deptno = :OLD.deptno;
SELECT * FROM emp_dept_v
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
7.
— Check the results while Updating —
UPDATE emp_dept_v SET sal = 5002 WHERE empno = 7369
/
Points to be noted :
a.UPDATE employee_master
SET sal = :NEW.sal
WHERE empno = :OLD.empno;
b.UPDATE department_master
SET tot_dept_sal = tot_dept_sal + (:NEW.sal – :OLD.sal)
WHERE deptno = :OLD.deptno;
SELECT * FROM emp_dept_v
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
UPDATE emp_dept_v SET deptno = 30 WHERE empno = 7369
/
Points to be noted :
a. UPDATE employee_master
SET deptno = :NEW.deptno
WHERE empno = :OLD.empno;
c. UPDATE department_master
SET tot_dept_sal = tot_dept_sal + :NEW.sal
WHERE deptno = :NEW.deptno;
SELECT * FROM emp_dept_v
/
SELECT * FROM employee_master
/
SELECT * FROM department_master
/
8.
— Drop the relevant Objects created —
DROP VIEW emp_dept_v
/
DROP TABLE employee_master PURGE
/
DROP TABLE department_master PURGE
/
- Managing Triggers
Disable or reenable a Database Trigger :
ALTER TRIGGER trigger_name DISABLE|ENABLE
ALTER TRIGGER new_emp_dept DISABLE
/
ALTER TRIGGER new_emp_dept ENABLE
/
Disable or reenable all triggers for a Table :
ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS
Recompile a Trigger for a Table :
ALTER TRIGGER trigger_name COMPILE
- Creating Triggers on DDL Statements
CREATE [OR REPLACE] TRIGGER trigger_name
timing
[ddl_event1 [OR ddl_event2 OR …]]
ON {database|schema}
trigger_body
Creating Triggers on System Events
CREATE [OR REPLACE] TRIGGER trigger_name
timing
[database_event1 [OR database_event2 OR …]]
ON {DATABASE|SCHEMA}
trigger_body
LOGON and LOGOFF Trigger Example
— PL/SQL Trigger for LOGON to a schema —
CREATE TABLE logon_trig_table(user_id varchar2(40),log_date DATE,action VARCHAR2(50))
/
CREATE OR REPLACE TRIGGER logon_trig_schema
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO logon_trig_table(user_id,log_date,action)
VALUES(USER,SYSDATE,’Logging On’);
END;
/
SELECT * FROM logon_trig_table
/
CREATE OR REPLACE TRIGGER logon_trig_db
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO logon_trig_table(user_id,log_date,action)
VALUES(USER,SYSDATE,’Logging On’);
END;
/
SELECT * FROM logon_trig_table
/
DROP TABLE logon_trig_table PURGE
/
— PL/SQL Trigger for LOGOFF to a schema —
CREATE TABLE logoff_trig_table(user_id varchar2(40),log_date DATE,action VARCHAR2(50))
/
CREATE OR REPLACE TRIGGER logoff_trig_schema
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO logoff_trig_table(user_id,log_date,action)
VALUES(USER,SYSDATE,’Logging Off’);
END;
/
SELECT * FROM logoff_trig_table
/
CREATE OR REPLACE TRIGGER logoff_trig_db
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO logoff_trig_table(user_id,log_date,action)
VALUES(USER,SYSDATE,’Logging Off’);
END;
/
SELECT * FROM logoff_trig_table
/
ALTER TRIGGER logon_trig_schema DISABLE
/
ALTER TRIGGER logon_trig_db DISABLE
/
DROP TRIGGER logon_trig_schema
/
DROP TRIGGER logon_trig_db
/
DROP TRIGGER logoff_trig_db
/
DROP TABLE logoff_trig_table PURGE
/
— Example of a PL/SQL Trigger which fires with DDL Statements at the Schema Level —
CREATE OR REPLACE TRIGGER scott_before_create_drop
BEFORE CREATE OR DROP ON scott.SCHEMA
BEGIN
raise_application_error(-20001,’You cannot create or drop an Object in SCOTT Schema’);
END scott_before_create_drop;
/
Which one fires first (Trigger or Constraint)
Depends on what the trigger is Row level or Statement Level.
Before Statement level and row level triggers fire before the constraints.
Constraints are always checked first in AFTER Trigger whereas BEFORE Trigger fires first.
If Trigger is written on AFTER INSERT and UPDATE then Constraint will fire first
- Mutating Tables
Mutating Problem occurs if we are trying to access a table from a trigger which is fired by a command that modifies the table.
— Example to demonstrate a Mutating Trigger Error —
1.
— Create a Table emp_tab —
CREATE TABLE emp_tab AS SELECT * FROM emp
/
2.
— Create a Table bin —
CREATE TABLE bin(id NUMBER)
/
3.
— Create a Row Level Trigger which fires for each row —
CREATE OR REPLACE TRIGGER mut_trg
BEFORE DELETE ON emp_tab
FOR EACH ROW
DECLARE
a NUMBER;
BEGIN
SELECT COUNT(empno) INTO a
FROM emp_tab;
INSERT INTO bin VALUES(a);
END mut_trg;
/
4.
— Execute the Trigger (Delete Records from emp_tab Table for the Trigger to fire) —
DELETE emp_tab
/
Note :
This DML statement will result in Mutating Error. So in order to avoid this error we have to first
disable the trigger on the Table. Next create a Package which contains a Global Variable.
Then create a Statement Level Trigger which will retrive the no.of records from emp_tab into the Global Variable and insert that value into the bin table.
5.
— Disable the Trigger mut_trg —
ALTER TRIGGER mut_trg DISABLE
/
6.
— Create a Package having the Global Variable —
CREATE OR REPLACE PACKAGE global_var
IS
cnt_g NUMBER:=0;
END global_var;
/
7.
— Create a Statement Level Trigger to retrieve the no.of records and store it in the Global Variable —
CREATE OR REPLACE TRIGGER mut_trg_st
BEFORE DELETE ON emp_tab
BEGIN
SELECT COUNT(empno) INTO global_var.cnt_g
FROM emp_tab;
DBMS_OUTPUT.PUT_LINE(‘This Trigger is a Statement Level Trigger’);
INSERT INTO bin VALUES(global_var.cnt_g);
END mut_trg_st;
/
8.
— Execute the Trigger (Delete Records from emp_tab Table for the Statement Level Trigger to fire) —
DELETE emp_tab
/
9.
— Select Records from bin Table —
SELECT * FROM bin
/
10.
— DROP Package and Tables and Triggers —
DROP TABLE bin PURGE
/
DROP TABLE emp_tab PURGE
/
- Calling a Procedure through Triggers
A Procedure can be referred from a Trigger.
— Example to demonstrate the on how to refer a Procedure inside a Trigger —
CREATE TABLE employees AS SELECT * FROM emp WHERE 1=2
/
1.
— Create the Procedure —
CREATE OR REPLACE PROCEDURE proc_p
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This Procedure is referred inside the Trigger.’);
END proc_p;
/
2.
— Create the Trigger —
CREATE OR REPLACE TRIGGER proc_trig
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
CALL proc_p; — Call the Procedure Inside the Trigger —
END proc_trig;
/
CREATE OR REPLACE TRIGGER proc_trig
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
EXECUTE IMMEDIATE ‘CALL proc_p;’; — Call the Procedure Inside the Trigger —
END proc_trig;
/
3.
— Insert Records into Employees Table —
BEGIN
INSERT INTO employees(ename)VALUES(‘K1’);
END;
/
DROP TABLE employees PURGE
/
Example of using a Sequence inside a Trigger
1.
— Create Table test_emp —
CREATE TABLE test_emp(id NUMBER,name VARCHAR2)
/
2.
— Create a Sequence —
CREATE SEQUENCE test_emp_seq
/
3.
— Create a Row Level Trigger —
CREATE OR REPLACE TRIGGER seq_trig
BEFORE INSERT ON test_emp
FOR EACH ROW
BEGIN
SELECT test_emp_seq.NEXTVAL INTO
:NEW.id
FROM dual;
END seq_trig;
/
4.
— Insert Records into the Table —
INSERT INTO test_emp(name)VALUES(‘K1’)
/
5.
— Select Records from the Table —
SELECT * FROM test_emp
/
- Compound Triggers
In PL/SQL,there are two areas of scope for DML Triggers, Row Level (Which firesonce for each row) and Statement Level).
We can combine the triggering DMLs into one trigger,but not the events or the types.
— Example to demonstrate BEFORE INSERT UPDATE OR DELETE —
1.
— Create the Table for storing the Event Before Insert —
CREATE TABLE before_insert_tab(note VARCHAR2(250))
/
CREATE TABLE before_insert_tab_desc(note VARCHAR2(250))
/
2.
— Create the Trigger Before Insert —
CREATE OR REPLACE TRIGGER before_insert_trig
BEFORE INSERT
ON before_insert_tab
BEGIN
IF INSERTING THEN
INSERT INTO before_insert_tab_desc VALUES(‘This Record was inserted Before Insert through Trigger’);
END IF;
END before_insert_trig;
/
BEGIN
INSERT INTO before_insert_tab VALUES(‘Before Insert Record’);
END;
/
SELECT note FROM before_insert_tab
/
SELECT note FROM before_insert_tab_desc
/
3.
— Create the Table for storing the Event After Insert —
CREATE TABLE after_insert_tab(note VARCHAR2(250))
/
CREATE TABLE after_insert_tab_desc(note VARCHAR2(250))
/
4.
CREATE OR REPLACE TRIGGER after_insert_trig
AFTER INSERT OR UPDATE OR DELETE
ON after_insert_tab
BEGIN
IF INSERTING THEN
INSERT INTO after_insert_tab_desc VALUES(‘This Record was inserted After Insert through Trigger’);
END IF;
END after_insert_trig;
/
BEGIN
INSERT INTO after_insert_tab VALUES(‘After Insert Record’);
END;
/
SELECT note FROM after_insert_tab
/
SELECT note FROM after_insert_tab_desc
/
5.
— Now try creating a Trigger with Both BEFORE and AFTER INSERT —
— This will error out since we cannot have BEFORE AND AFTER inside the same trigger —
CREATE OR REPLACE TRIGGER insert_trig_before_after
BEFORE OR AFTER
INSERT
ON after_insert_tab
BEGIN
IF INSERTING THEN
INSERT INTO after_insert_tab_desc VALUES(‘This Record was inserted After Insert through Trigger’);
END IF;
END insert_trig_before_after;
/
6.
— This error can be resolved using Compund Trigger —
CREATE OR REPLACE TRIGGER insert_trig_before_after
FOR INSERT ON after_insert_tab
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
IF INSERTING THEN
INSERT INTO before_insert_tab_desc VALUES(‘This Record was inserted Before Insert through Compound Trigger’);
END IF;
END BEFORE STATEMENT;
AFTER STATEMENT IS
BEGIN
IF INSERTING THEN
INSERT INTO after_insert_tab_desc VALUES(‘This Record was inserted after Insert through Compound Trigger’);
END IF;
END AFTER STATEMENT;
END insert_trig_before_after;
/