Sunday, September 28, 2014

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


No comments:

Post a Comment

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery