Sunday, 28 September 2014

Triggers

What
is Database Trigger?

Database Trigger is a statement
or set of statements which would be executed due to the modification in a
database. The modification would mean Insertion, Deletion, or Updating of
records.

Purpose
of Triggers

To maintain database integrity.

To safeguard a database from inconsistency,
especially in a large database.

To prevent invalid transactions.

What
do we need to have a Trigger?

Event-Condition-Action
Model

We need to,

·Specify “When you need your trigger
gets executed?”

·Specify “What actions to be taken
while executing Triggers?” In other words, “you need to specify the actions
that need to be caused by the execution of Trigger”.

The above said requirements for
having a trigger is called Event-Condition-Action model.

Event
–
The event which causes the Trigger to be executed

Condition
– The condition which need to be satisfied by the event to trigger an action

Action
– The actual modification to be done on database due to the Event and
Condition.

Type
of Triggers

Row-level Trigger
– it is fired each time a row (record) is affected. For example, if a DELETE
statement deletes 10 rows in a table row-level is fired once for every row. If the
DELETE statement affects no rows, then no trigger action executed.

Statement-level Trigger
– it is fired once for a statement, regardless of the number of statements get
affected. 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. For example, if a DELETE statement deletes 10 rows in a table,
statement-level trigger executes the action only once.

Before Trigger
– it executes the trigger action before the triggering statement.

After Trigger
– it executes the trigger action statements after the triggering statement.

Syntax:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE
| AFTER | INSTEAD OF }

{INSERT
[OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON
table_name

[REFERENCING OLD AS o NEW AS n]

[FOR
EACH ROW]

WHEN
(condition)

PL/SQL
block

Example

CREATE TRIGGER reorder

AFTER UPDATE OF balance ON Account /* Triggering Statement / EVENT. WHEN to
execute */