Introduction

A trigger is a stored procedure in a database that is called automatically whenever a certain event occurs in the database e.g. A trigger can be triggered when a row is entered into a table or when A trigger is a stored procedure in a database that is called automatically whenever a certain event occurs in the database columns of that table are modified.

What is an Oracle Trigger

Oracle Triggers are just chunks of PL/SQL code that are preserved in the Oracle database and can be reused and repurposed anytime the user requires it. Both DDL (Data Definition Language) and DML (Data Manipulation Language) codes can use this. The parameters allowed in triggers are divided into four categories: ‘trigger name’ for naming the trigger, ‘trigger time’ for specifying the time to trigger the event, ‘trigger event’ for specifying the type of event, and ‘tbl name’ for specifying the table name for which the trigger is being created and used.

Syntax of 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: When creating a trigger, this is the name we want to give it.

trigger time: This specifies whether the event will be triggered BEFORE or AFTER the trigger is triggered.

trigger event: This specifies if the event is an INSERT, DELETE, or UPDATE.

tbl name: The name of the table on which the trigger is built.

Types of Oracle Trigger

Following are the types of Oracle Trigger:

Oracle BEFORE Trigger

Before trigger means before any trigger event at that time this trigger is specified

Let’s take an example to understand this

In this example, we’ll look at a trigger that will be set before we perform an insert action on the workers’ table.

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

Oracle AFTER Trigger

The trigger will be activated once the INSERT statement has been executed in this case.

We’ll look at a trigger that will be activated after an insert action on the employee table in this example. For a better understanding, take a look at the code below.

CREATE OR REPLACE TRIGGER after_insert_employee_details
AFTER INSERT
ON employees
FOR EACH ROW
BEGIN
INSERT INTO VEHICLE VALUES(:new.vehicle_no, :new.vehicle_name, :new.sale, :new.country);
END;

In the prior example, the trigger is assigned to the employees table, and it will fire whenever an insert action is performed on the employee table. If an insert operation on an employee table is successful, this trigger is called implicitly to update the car table with the new values.

Oracle DROP Trigger

As the name implies, we’ll examine how to drop a trigger that has already been created and placed in the database with the aid of an example. We’ll remove the after delete trigger in the example below.

Let’s start with the syntax.

DROP TRIGGER trigger_name;

Example:
DROP TRIGGER after_delete_employee;

Oracle DISABLE Trigger

For testing and debugging purposes, you may want to disable a trigger.

Syntax:

ALTER TRIGGER trigger_name DISABLE;

Example:

ALTER TRIGGER after_delete_employee DISABLE;

Oracle ENABLE Trigger

The ALTER TRIGGER ENABLE statement is used to enable a previously disabled trigger.

Syntax

ALTER TRIGGER trigger_name ENABLE;

Example

ALTER TRIGGER after_delete_employee ENABLE;