Database Trigger - Overview

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