Oracle PL/SQL Trigger Tutorial: Instead of, Compound [Example]

Details

What is Trigger in PL/SQL?

TRIGGERS are stored programs that are fired by Oracle engine automatically when DML Statements like insert, update, delete are executed on the table or some events occur. The code to be excecuted in case of a trigger can be defined as per the requirement. You can choose the event upon which the trigger needs to be fired and the timing of the execution. The purpose of trigger is to maintain the integrity of information on the database.

In this tutorial, you will learn-

Benefits of Triggers

Following are the benefits of triggers.

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

So each trigger is the combination of above parameters.

How to Create Trigger

Below is the syntax for creating a trigger.

Triggers in PL/SQL 


CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Syntax Explanation:

:NEW and :OLD Clause

In a row level trigger, the trigger fires for each related row. And sometimes it is required to know the value before and after the DML statement.

Oracle has provided two clauses in the RECORD-level trigger to hold these values. We can use these clauses to refer to the old and new values inside the trigger body.

This clause should be used based on the DML event. Below table will specify which clause is valid for which DML statement (INSERT/UPDATE/DELETE).

INSERT UPDATE DELETE
:NEW VALID VALID INVALID. There is no new value in delete case.
:OLD INVALID. There is no old value in insert case VALID VALID

INSTEAD OF Trigger

"INSTEAD OF trigger" is the special type of trigger. It is used only in DML triggers. It is used when any DML event is going to occur on the complex view.

Consider an example in which a view is made from 3 base tables. When any DML event is issued over this view, that will become invalid because the data is taken from 3 different tables. So in this INSTEAD OF trigger is used. The INSTEAD OF trigger is used to modify the base tables directly instead of modifying the view for the given event.

Example 1: In this example, we are going to create a complex view from two base table.

Then we are going to see how the INSTEAD OF trigger is used to issue UPDATE the location detail statement on this complex view. We are also going to see how the :NEW and :OLD is useful in triggers.

Step 1) Creating table 'emp' and 'dept' with appropriate columns

Triggers in PL/SQL 

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept( 
Dept_no NUMBER, 
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code Explanation

Output

Table Created

Step 2) Now since we have created the table, we will populate this table with sample values and Creation of Views for the above tables.

Triggers in PL/SQL 

BEGIN
INSERT INTO DEPT VALUES(10,‘HR’,‘USA’);
INSERT INTO DEPT VALUES(20,'SALES','UK’);
INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); 
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);
INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;
INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); 
COMMIT;
END;
/

Code Explanation

Output

PL/SQL procedure completed

Step 3) Creating a view for the above created table.

Triggers in PL/SQL 

CREATE VIEW gtupapers_emp_view(
Employee_name:dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM gtupapers_emp_view;

Code Explanation

Output

View created

EMPLOYEE_NAMEDEPT_NAMELOCATION
ZZZHRUSA
YYYSALESUK
XXXFINANCIALJAPAN

Step 4) Update of view before instead-of trigger.

Triggers in PL/SQL 

BEGIN
UPDATE gtupapers_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;
COMMIT;
END;
/

Code Explanation

Output

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Step 5)To avoid the error encounter during updating view in the previous step, in this step we are going to use "instead of trigger."

Triggers in PL/SQL 

CREATE TRIGGER gtupapers_view_modify_trg
INSTEAD OF UPDATE
ON gtupapers_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Explanation

Output

Trigger Created

Step 6) Update of view after instead-of trigger. Now the error will not come as the "instead of trigger" will handle the update operation of this complex view. And when the code has executed the location of employee XXX will be updated to "France" from "Japan."

Triggers in PL/SQL 

BEGIN
UPDATE gtupapers_emp_view SET location='FRANCE' WHERE employee_name='XXX'; 
COMMIT;
END;
/

SELECT * FROM gtupapers_emp_view;

Code Explanation:

Output:

PL/SQL procedure successfully completed

EMPLOYEE_NAMEDEPT_NAMELOCATION
ZZZHRUSA
YYYSALESUK
XXXFINANCIALFRANCE

Compound Trigger

The Compound trigger is a trigger that allows you to specify actions for each of four timing points in the single trigger body. The four different timing point it supports is as below.

It provides the facility to combine the actions for different timing into the same trigger.

Triggers in PL/SQL 


CREATE [ OR REPLACE ] TRIGGER <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
ON <name of underlying object>
<Declarative part>‭	‬
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Syntax Explanation:

Example 1: In this example, we are going to create a trigger to auto-populate the salary column with the default value 5000.

Triggers in PL/SQL 

CREATE TRIGGER emp_trig 
FOR INSERT 
ON emp
COMPOUND TRIGGER 
BEFORE EACH ROW IS 
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); 
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Explanation:

Output:

Trigger created

PL/SQL procedure successfully completed.

EMP_NAMEEMP_NOSALARYMANAGERDEPT_NO
CCC10045000AAA30

Enabling and Disabling Triggers

Triggers can be enabled or disabled. To enable or disable the trigger, an ALTER (DDL) statement needs to be given for the trigger that disable or enable it.

Below are the syntax for enabling/disabling the triggers. 

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Syntax Explanation:

Summary

In this chapter, we have learnt about PL/SQL triggers and their advantages. We have also learnt the different classifications and discussed INSTEAD OF trigger and COMPOUND trigger.

 

YOU MIGHT LIKE: