Table of Contents
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;
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;
DROP TRIGGER after_delete_employee;
Oracle DISABLE Trigger
For testing and debugging purposes, you may want to disable a trigger.
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER after_delete_employee DISABLE;
Oracle ENABLE Trigger
The ALTER TRIGGER ENABLE statement is used to enable a previously disabled trigger.
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER after_delete_employee ENABLE;