Oracle PL/SQL Trigger: Instead of & Compound Types

What is Trigger in PL/SQL?

TRIGGERS are stored PL/SQL programs that are fired by the Oracle engine automatically when DML statements like insert, update, and delete are executed on the table, or when some events occur. The code to be executed in the 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 a trigger is to maintain the integrity of information on the database.

Benefits of Triggers

Following are the benefits of triggers.

• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• 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 in the topics that follow. (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 a combination of the above parameters.

How to Create Trigger

Below is the syntax for creating a trigger. The screenshot below shows this trigger creation syntax in Oracle.

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:

• The above syntax shows the different optional statements that are present in trigger creation.
• BEFORE/AFTER will specify the event timings.
• INSERT/UPDATE/LOGON/CREATE/etc. will specify the event for which the trigger needs to be fired.
• The ON clause will specify the object on which 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 a DML trigger.
• The command “FOR EACH ROW” will specify the ROW level trigger.
• The WHEN clause will specify the additional condition in which the trigger needs to fire.
• The declaration part, execution part, and exception handling part are the same as those of the other PL/SQL blocks. The declaration part and the 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 row-level trigger to hold these values. We can use these clauses to refer to the old and new values inside the trigger body.

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

This clause should be used based on the DML event. The below table specifies which clause is valid for which DML statement (INSERT/UPDATE/DELETE).

INSTEAD OF Trigger

An “INSTEAD OF trigger” is a special type of trigger. It is used only in DML triggers. It is used when any DML event is going to occur on a complex view.

Consider an example in which a view is made from three base tables. When any DML event is issued over this view, it will become invalid because the data is taken from three different tables. So in this case, an 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 tables, where Table_1 is the emp table and Table_2 is the department table.

Then we are going to see how the INSTEAD OF trigger is used to issue an UPDATE of the location detail on this complex view. We are also going to see how :NEW and :OLD are useful in triggers. The example is done in the following steps:

• Step 1: Creating tables ’emp’ and ‘dept’ with appropriate columns
• Step 2: Populating the tables with sample values
• Step 3: Creating a view for the above created tables
• Step 4: Update of the view before the INSTEAD OF trigger
• Step 5: Creation of the INSTEAD OF trigger
• Step 6: Update of the view after the INSTEAD OF trigger

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

The screenshot below shows the creation of the ’emp’ and ‘dept’ base tables in Oracle.

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

• Code line 1-7: Table ’emp’ creation.
• Code line 8-12: Table ‘dept’ creation.

Output:

Table Created

Step 2) Now, since we have created the tables, we will populate them with sample values.

The screenshot below shows the sample rows being inserted into the ‘dept’ and ’emp’ 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,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Explanation

• Code line 13-19: Inserting data into the ‘dept’ table.
• Code line 20-26: Inserting data into the ’emp’ table.

Output:

PL/SQL procedure completed

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

The screenshot below shows the complex view being created and then queried.

CREATE VIEW guru99_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 guru99_emp_view;

Code Explanation

• Code line 27-32: Creation of the ‘guru99_emp_view’ view.
• Code line 33: Querying guru99_emp_view.

Output:

View created

Step 4) Update of the view before the INSTEAD OF trigger.

The screenshot below shows the update attempt on the complex view and the resulting error.

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

Code Explanation

• Code line 34-38: Update the location of “XXX” to ‘FRANCE’. It raised an exception because DML statements are not allowed directly on 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 encountered while updating the view in the previous step, in this step we are going to use an “INSTEAD OF trigger.”

The screenshot below shows the creation of the INSTEAD OF trigger.

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

Code Explanation

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

Output:

Trigger Created

Step 6) Update of the view after the INSTEAD OF trigger. Now the error will not appear, as the “INSTEAD OF trigger” will handle the update operation of this complex view. When the code is executed, the location of employee XXX will be updated to “France” from “Japan.”

The screenshot below shows the successful update through the INSTEAD OF trigger and the refreshed view.

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

SELECT * FROM guru99_emp_view;

Code Explanation:

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

Output:

PL/SQL procedure successfully completed

Compound Trigger

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

• BEFORE STATEMENT – level
• BEFORE ROW – level
• AFTER ROW – level
• AFTER STATEMENT – level

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

The screenshot below shows the compound trigger syntax with its four timing sections.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
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 a ‘COMPOUND’ trigger.
• The declarative section is common for all the execution blocks in the trigger body.
• These four timing blocks can be in any sequence. It is not mandatory to have all four timing blocks. We can create a COMPOUND trigger only for the timings that 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.

The screenshot below shows the compound trigger example and its output.

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:

• Code line 2-10: Creation of the compound trigger. It is created for the timing BEFORE ROW level to populate the salary with the default value 5000. This will change the salary to the default value ‘5000’ before inserting the record into the table.
• Code line 11-14: Insert the record into the ’emp’ table.
• Code line 16: Verifying the inserted record.

Output:

Trigger created

PL/SQL procedure successfully completed.

Enabling and Disabling Triggers

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

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

FAQs