What is Database Trigger? / Why do we need database trigger? / Overview of Database trigger / Database Trigger Example
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 */
WHEN (NEW.balance <1000) /* Trigger CONDITION */
FOR EACH ROW
BEGIN
INSERT INTO Loan VALUES(loan_no, 1000
- NEW.balance, OLD.bname); /* Trigger ACTION
*/
END;
More on this later ...
