Triggers in PLSQL

A trigger is a PL/SQL block or a PL/SQL procedure associated with a table view schema or the database.
The code in the trigger executes implicitly whenever a particular event occurs.

A set of pl/sql statements stored permanently in database and "automatically" activated when ever an event raising statement ( DML ) is executed.

They are stored in "User_Triggers" system table.

They are used to impose Business rules / user defined restrictions on Table columns.

They are also activated when Tables are manipulated by other users or by other application s/w tools.

They provide high security on tables.

Trigger Parts:  4 Parts

Two types of triggers:

1. Application trigger fires whenever an event occurs with in a particular application
2. Database Trigger Fires whenever a data event (Such as DML ) or system event (such as log on or shut down) occurs on a schema or database.

Trigger Parts:  4 Parts

INSTEAD of triggers are used to provide a transparent way to modifying views that
cannot be modified directly through SQL, DML statements because the view is not 
modifiable.
INSTEAD of triggers provide writing of Insert, Update and Delete statements against the view.
The INSTEAD if trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.

INSTEAD of trigger execute the trigger body instead of the triggering statement.

Statement Triggers

In this type of triggers, the trigger body executes once for the triggering event.

This is the default.

Statement trigger fires once, even if no rows are affected at all.

Row Trigger

In this type, the trigger body executes once for each row affected by the triggering event.

Row trigger is not executed if the triggering event affects no rows.

A view cannot be modified by normal DML if the view query contains set operators, group functions, group by, connect By, start with clauses or joins.

Triggering Event : Indicates when to activate the trigger .

     Before -- insert / update / delete

     After   --  insert / update / delete  

Trigger Restriction: ( optional )

 Used to stop the activation of trigger based on condition.  If condition is True trigger is active.

 Syntax:

Create or replace Trigger < Trigger name >  before/after insert or update or delete -- 1

 [ of <columns> ] on < Table name >  [ for each row -- 2

 when (< condition >) -- 3   * if condition is True  trigger is executed

 declare                                   

   < variable declaration >; ]

 begin

   < exec stmts >;

 [ exception

   < exec stmts >; ]

 end;

Advantage of Database Triggers:

 - Automatic execution of code based on event .

 - Used to impose user defined restrictions on tables 

 - Provides Security on Tables while Manipulating data from any where .

Trigger Vs Sub Programs:

Triggers are automatically activated by " DML " statements.

Procedures & Functions has to be Explicitly invoked by user.

Procedures and functions can be used in triggers.

Trigger cannot be used in any program.

Recursive trigger :

·         This is a trigger that contains a DML operation changing the very same table.

Cascading Trigger:

·         The action of one trigger cascades to another trigger, causing this second trigger to fire.

·         Oracle  server allows up to 32 triggers to cascade at any one time.

·         This number can be changed by changing the value of the OPEN  - CORSORS. Data bases initialization parameter. ( default value is 50 ).

·         * A triggering statement should contain