Updated March 23, 2023

Introduction to Oracle Triggers

Oracle Triggers are nothing but chunk of code in a PL/SQL, which is saved in the Oracle database also can be reused and repurposed whenever the user needs it. This can be used in both DDL (Data Definition Language) and DML (Data Manipulation Language) codes. The Parameters allowed in triggers can be classified into four types, namely ‘trigger_name’ for created a trigger with certain name, ‘trigger_time’ for mentioning the time to trigger the event, ‘trigger_event’ for defining the type of event, and ‘tbl_name’ to add the table name for which the trigger is being created and used for.

Syntax of Trigger

So, after a little introduction to Trigger. Let us now get to know the syntax of the trigger.

Below we can see the syntax of how to create a trigger:

CREATE [OR REPLACE] TRIGGER trigger_name trigger_time trigger_event ON tbl_name [ FOR EACH ROW] Declare ----- Variable declarations BEGIN ----trigger body EXCEPTION ----exception handling code END;

Parameters

trigger_name: It is the name of the trigger we want to give while creating a trigger.
trigger_time: It states whether the trigger will be triggered BEFORE or AFTER the event.
trigger_event: It states the event whether it is INSERT event or DELETE event or UPDATE event.
tbl_name: The table name that the trigger is created on.

So, after getting to know the syntax of a trigger. Let us now understand how a trigger works.

How does a trigger work in oracle?

Triggers are like procedures that are implicitly executed when an INSERT, DELETE, UPDATE statement is fired on a table whether by a SQL statement or by a user-defined procedure. There is but a fundamental difference between a procedure and trigger which is in case of procedure it is explicitly executed explicitly by a user, application or trigger whereas a trigger is executed implicitly when an UPDATE, DELETE or INSERT statement are executed. To understand let us, for example, there is an AFTER INSERT trigger which is implicitly triggered after an insert statement is executed on a particular table. So whatever statements are present in the body of the trigger get executed after a row is inserted in that particular table. So, whenever a row is inserted in that table the trigger is fired implicitly.

One important note that we should know is that we cannot create a BEFORE trigger on a view.

So, now we are going to understand the trigger execution a bit more with the help of some practical examples.

Examples

The first example that we are going to witness:

1. Before Insert

This means that Oracle will fire the trigger before there is an actual insert operation executed on the table.

In this example, we are going to look into a trigger that will be triggered before we are doing an insert operation on the employees’ table. Let us look at the code below for a better understanding.

Code:

CREATE OR REPLACE TRIGGER before_insert_employee BEFORE INSERT ON employee FOR EACH ROW BEGIN INSERT INTO VEHICLE VALUES('VH010','Honda','100','Kanpur'); END;

If we look at the above example we can see that this trigger is applied on the employee table and it will be fired whenever a BEFORE INSERT operation happens on the employee table. So, whenever an insert command is executed by any user. This trigger will fire just before the insert operation is executed and it will first insert the given data into the Vehicle table before data is inserted in the employee table.

2. After Insert

In this type of trigger, the trigger will be fired after the execution of the INSERT statement.

In this example, we are going to look into a trigger that will be triggered after an insert operation on the employee table. Let us look at the code below for better understanding.

Code:

CREATE OR REPLACE TRIGGER after_insert_employee AFTER INSERT ON employee FOR EACH ROW BEGIN INSERT INTO VEHICLE VALUES(:new.vehicle_id, :new.vehicle_name, :new.sale, :new.city); END;

In the above example, the trigger is applied to the employee table and it will be fired after an insert operation is executed in the employee table. If you see the code whenever an insert operation is successfully executed on an employee table, this trigger will be implicitly fired to insert the new values into the vehicle table.

3. After Delete

In this type of trigger, the trigger will be fired after a delete operation is executed on the concerned table.

In this example below, we are going to look at the trigger execution of a trigger which is fired after we delete a row in the employee table. This trigger will get fired for each row deletion in the employee table. Let us look at the example below.

Code:

CREATE OR REPLACE TRIGGER after_delete_employee AFTER DELETE ON employee FOR EACH ROW BEGIN delete from vehicle where vehicle_id = :old.vehicle_id; END;

If we see the code of the above example, we can see that the trigger is applied to the employee table. Hence it will get fired after every delete operation on the employee table. In our case, after a delete operation is executed on the employee table, this trigger will delete a record from the vehicle table based on the vehicle_id.

4. Drop A Trigger

As the name suggests in this example we are going to see with the help of an example of how to drop a trigger that has been created and stored in the database. In the below example we are going to drop the after delete trigger. Since we did not discuss the syntax of the drop trigger. Let us look at the syntax first.

Code:

DROP TRIGGER trigger_name; Now below is an example for the same DROP TRIGGER after_delete_employee;

As we can see we just append the trigger name with the DROP statement to drop a trigger from the database.

Conclusion

In this article, we learned about oracle triggers with their syntaxes. We got to know the working of triggers and we also discussed a few examples to broaden our knowledge on different types of triggers that can be used/are used in the industry to solve business problems.