Database Triggers

A database trigger is a stored procedure that is fired when an INSERT,UPDATE, or DELETE statement is issued against the associated table in the data base. The name trigger is appropriate, as these are triggered (fired) when ever the above mentioned commands are executed. A trigger defines an action database related event occurs. A trigger cab include SQL and PL/SQL statements to execute it as a unit and it can invoke other stored procedures. Trigger may be use to provide referential integrity, to enforce complex business rules, or to audit changes to the data. The code with in a trigger, called as the trigger body which is made up of the PL/SQL statement code block.
A trigger is automatically executed without any action required by the user. A stored procedure on other hand needs to be explicitly invoked. This is the main difference between the trigger and the stored procedure.A trigger type is defined by the type of the triggering transaction and by the level at which the trigger is executed. Oracle has the following type of the triggers depending on the different application:
1 ). Row Level Trigger .
2 ). Statement Level Trigger .
3 ). Before Trigger .
4 ). After Trigger .
Row Level Trigger:
Row level trigger is execute once for each row in the transaction. The commands of row level trigger are executed on all rows that are affected by the command that enables the trigger.F or example, if an UPDATE statement updates multiple rows of a table, a row trigger fired once for each row affected by UPDATE statement.
Statement Level Trigger:
These type of triggers are triggered only once for each transaction. For example, when an update command update 15 rows , the command contained in the trigger and executed only once, and not with every processed row.
Before And After Trigger :
Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering action or event.
Now the uses of these type of database triggers are as the follow :
1 ). Triggers are used to derive column values automatically .
2 ). They are used to enforce complex integrity constraints .
3 ). To enforce the complex business rules .
4 ). To customize complex security authorization .
5 ). To maintain the replicate tables in the database .
6 ). To audit the data modification.