Triggers in Oracle

A Trigger is a stored PL/SQL program unit, which gets implicitly fired by Oracle Database when a triggering event occurs. A Trigger defines an action, which should get executed, when some database related event occurs.

There are different types of triggers.

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• Compound Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

DML Triggers are associated with a specific database table. The purpose of this trigger is to perform a specific service when a specified DML operation (INSERT, UPDATE, DELETE) occurs on a table.

Unlike the stored procedures or functions which have to be explicitly invoked, these triggers implicitly gets fired or executed whenever the table is affected by any DML operation.

A  Trigger has 3 basic parts:-

Triggering event or Statement:- An SQL statement which causes a trigger to be fired. Every triggering event is associated with trigger timing i.e., when a trigger should execute. According to trigger timing, triggers can be either before or after triggers.

Triggering events can be 

• Before INSERT or After INSERT
• Before UPDATE or After UPDATE
• Before DELETE or After DELETE

Trigger constraint:-This is optional. A Boolean expression which must be TRUE for the trigger to be fired. We can add triggering constraint by using a WHEN clause in trigger.

Trigger action:- Is a PL/SQL code which contains what needs to be done when a trigger is fired.

Structure of Trigger:-
CREATE [OR REPLACE] TRIGGER  <trigger_name> BEFORE | AFTER | INSTEAD OF
DELETE | [OR] INSERT | [OR] UPDATE [ OF <column> [, <column>...]] ON <table>

-- This section is called Triggering event

[ FOR EACH ROW [ WHEN  <condition>] ]

-- When clause will be triggering constraint
BEGIN

-- This PL/SQL block is Triggering action

/*
 PL/SQL Block
 */
...
END;

Row Trigger:- In Row Trigger, the trigger body is executed depending on how many rows were affected by the DML statement. If the DML statement did not affect any rows, the trigger body executes 0 (ZERO) times.

These row triggers will have two pseudo records to get data from triggering event (INSERT, UPDATE, DELETE).

OLD:- references the OLD row before UPDATE or DELETE

NEW:- references the NEW row after INSERT or UPDATE

Row triggers will be used if trigger action depends on the data provided by triggering statement.

Before Trigger:- Before Trigger executes trigger action i.e. PL/SQL code before executing triggering statement INSERT,  UPDATE or  DELETE.

Triggering Constraint:-
If you want any trigger to get fired only on a specific condition, then we can give that condition as Triggering Constraint.Triggering constraint is achieved by adding a WHEN clause in the trigger.

Statement Trigger:- In a statement trigger, the trigger body is executed only once for the statement, irrespective of how many number of rows are affected by the DML statement. 

• FOR EACH ROW clause decides whether it is a row level or statement trigger.
• Statement triggers are useful in complex security check.
• Unlike row triggers, there are no pseudo records associated with statement triggers.

To View the trigger details use below query:

SELECT trigger_name, trigger_type, triggering_event,

    table_name, referencing_names,

    status, trigger_body

FROM   user_triggers

WHERE  trigger_name = 'XX_TRG_NAME';

Drop Trigger using below command:-

DROP TRIGGER <trigger_name>;