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.
