TRIGGERS IN PL/SQL

-A trigger is a PL/SQL block  or a procedure associated with a table, view, schema or the database.
-Executes implicitly whenever a particular event occurs
There are basically two types of trigger.
a) Application Trigger
b) Database Trigger

Application Trigger :
Fires whenever an event occurs with a particular application.

Database Trigger :
Fires whenever a data event (such as DML ) or system event (such as logon or shutdown ) occurs on a schema or database.

- Database Triggers can be defined on tables and  on views. If a DML operation is issued on a view, the INSTEAD OF trigger 
defines what action to take place. If these actions include DML operations on tables, then any trigger on the base tables are fired.

When to create Triggers:
- To perform related actions
-Centralize global operations

When not to create Triggers:
- Where functionality is already built into the oracle server.
-That duplicates the other triggers
-Create stored procedures and invoke them in a trigger, if the pl/sql is very lengthy.
-The excessive  use of triggers can result in complex  interdependencies, which may be difficult to maintain in large applications.

Parts of a TRIGGER:

a) Trigger timing
- For table : BEFORE,AFTER
-For View  : INSTEAD OF 

b) Triggering Event : INSERT, UPDATE OR DELETE

c) Table Name  : On table, VIEW

d) Trigger Type : ROW or STATEMENT

e) WHEN clause :  Restricting condition

f) Trigger Body  : complete PL/SQL block

DML TRIGGER COMPONENTS :

TRIGGER TIMING : WHEN THE TRIGGER SHOULD FIRE ?

A) BEFORE : Execute the trigger body before the triggering DML event on the table.

         -Used in most of the rollback cases.

         -To initialize global variables or flags and to validate complex business rules.

           

B) AFTER  : Execute the trigger body after the triggering DML event on the table.

          -To complete the triggering statement before executing the triggering action.

          -To perform different actions on  the same triggering statement if a BEFORE trigger        already present.

C) INSTEAD OF : Execute the trigger body instead of the triggering statement .

            This is used for views that are not otherwise modifiable.

            -Used to provide a transparent way of modifying views that can't be modified                    directly through   SQL DML statements ( e.g. complex views)

              - You can write INSERT, UPDATE and  DELETE  statements against the  view. 

          -The INSTEAD OF trigger works invisibly in the background performing the                       action coded in the   trigger body directly on the underlying tables.