Database Trigger

Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view. 

Triggers are commonly used to

1)automatically generate derived column values

2)prevent invalid transactions

3)enforce complex security authorizations

4)enforce referential integrity across nodes in a distributed database

5)enforce complex business rules

6)provide transparent event logging

7)provide sophisticated auditing

8)maintain synchronous table replicates

9)gather statistics on table access 

Cascading Triggers:-

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. 

A trigger has three basic parts:-

A)a triggering event or statement:-

A triggering event or statement is the SQL statement that causes a trigger to be fired. A triggering event can be an INSERT, UPDATE, or DELETE statement on a table.  

B)a trigger restriction:-

A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN. 

C)a trigger action :-

A trigger action is the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.

Types of Triggers:-

There are two types of triggers:- 

Row Triggers: A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not executed at all.  

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. 

Statement Triggers: A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected). For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. 

BEFORE vs. AFTER Triggers

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. These triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement. 

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed.These triggers are used when you want the triggering statement to complete before executing the trigger action. 

You can create 4 types of triggers by combinations in the following execution order:-

BEFORE statement trigger

BEFORE row trigger 

AFTER statement trigger

AFTER row trigger

B)Trigger Execution:-

A trigger can be in either of two distinct modes: 

enabled

An enabled trigger executes its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to TRUE. 

disabled

A disabled trigger does not execute its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to TRUE.