PL/SQL - Triggers

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

1.Benefits of Triggers
2.Types of Triggers in Oracle
3.How to Create Trigger
4.:NEW and :OLD Clause
5.INSTEAD OF Trigger
6.Compound Trigger

Benefits of Triggers

Following are the benefits of triggers.

1.Generating some derived column values automatically
2.Enforcing referential integrity
3.Event logging and storing information on table access
4.Auditing
5.Synchronous replication of tables
6.Imposing security authorizations
7.Preventing invalid transactions

Types of Triggers in Oracle

Triggers can be classified based on the following parameters.

Classification based on the timing

BEFORE Trigger: It fires before the specified event has occurred.
AFTER Trigger: It fires after the specified event has occurred.
INSTEAD OF Trigger: A special type. You will learn more about the further topics. (only for DML )

Classification based on the level

STATEMENT level Trigger: It fires one time for the specified event statement.
ROW level Trigger: It fires for each record that got affected in the specified event. (only for DML)

Classification based on the Event

DML Trigger: It fires when the DML event is specified (INSERT/UPDATE/DELETE)
DDL Trigger: It fires when the DDL event is specified (CREATE/ALTER)
DATABASE Trigger: It fires when the database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

So each trigger is the combination of above parameters.

How to Create Trigger

Below is the syntax for creating a trigger.

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:

1.The above syntax shows the different optional statements that are present in trigger creation.
2.BEFORE/ AFTER will specify the event timings.
3.INSERT/UPDATE/LOGON/CREATE/etc. will specify the event for which the trigger needs to be fired.
4.ON clause will specify on which object the above-mentioned event is valid. For example, this will be the table name on which the DML event may occur in the case of DML Trigger.
5.Command “FOR EACH ROW” will specify the ROW level trigger.
6.WHEN clause will specify the additional condition in which the trigger needs to fire.
7.The declaration part, execution part, exception handling part is same as that of the other PL/SQL blocks. Declaration part and exception handling part are optional.

: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.

1.:NEW – It holds a new value for the columns of the base table/view during the trigger execution
2.:OLD – It holds old value of the columns of the base table/view during the trigger execution

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.

Table_1 is emp table and
Table_2 is department 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
Step 2: Populating the table with sample values
Step 3: Creating view for the above created table
Step 4: Update of view before the instead-of trigger
Step 5: Creation of the instead-of trigger
Step 6: Update of view after instead-of trigger

Step 1) Creating table ’emp’ and ‘dept’ with appropriate columns

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 Table ’emp’ creation.
Table ‘dept’ creation.

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.

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
Inserting data into ‘dept’ table.
Inserting data into ’emp’ table.

Output PL/SQL procedure completed

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

CREATE VIEW msk_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;
/

Output
View created

SELECT * FROM msk_emp_view;

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

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

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

Code Explanation

Update the location of “XXX” to ‘FRANCE’. It raised the exception because the DML statements are not allowed in the complex view.
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."

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

Code Explanation
Creation of INSTEAD OF trigger for ‘UPDATE’ event on the ‘msk_emp_view’ view at the ROW level. It contains the update statement to update the location in the base table ‘dept’.
Update statement uses ‘:NEW’ and ‘: OLD’ to find the value of columns before and after the update.

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.”

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

SELECT * FROM msk_emp_view;

Code Explanation:
Update of the location of “XXX” to ‘FRANCE’. It is successful because the ‘INSTEAD OF’ trigger has stopped the actual update statement on view and performed the base table update.
Verifying the updated record.

Output:
PL/SQL procedure successfully completed

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	FRANCE

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.

1.BEFORE STATEMENT – level
2.BEFORE ROW – level
3.AFTER ROW – level
4.AFTER STATEMENT – level

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

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:

The above syntax shows the creation of ‘COMPOUND’ trigger
Declarative section is common for all the execution block in the trigger body.
These 4 timing blocks can be in any sequence. It is not mandatory to have all these 4 timing blocks. We can create a COMPOUND trigger only for the timings which are required.

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

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: Creation of compound trigger. It is created for timing BEFORE ROW- level to populate the salary with default value 5000. This will change the salary to default value ‘5000’ before inserting the record into the table.
Insert the record into ’emp’ table.
Verifying the inserted record.

Output: Trigger created
PL/SQL procedure successfully completed.

EMP_NAME	EMP_NO	SALARY	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

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: The first syntax shows how to enable/disable the single trigger.
The second statement shows how to enable/disable all the triggers on a particular table.

In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events

1.A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
2.A database definition (DDL) statement (CREATE, ALTER, or DROP).
3.A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).


Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers

Triggers can be written for the following purposes
1.Generating some derived column values automatically
2.Enforcing referential integrity
3.Event logging and storing information on table access
4.Auditing
5.Synchronous replication of tables
6.Imposing security authorizations
7.Preventing invalid transactions

Example Program

To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

When the above code is executed at the SQL prompt, it produces the following result
Trigger created.


The following points need to be considered here
OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers.
If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.
The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table.

Triggering a Trigger

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result

Old salary: 
New salary: 7500 
Salary difference:

Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result

Old salary: 1500 
New salary: 2000 
Salary difference: 500