-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 ?
-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.
-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.
