Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain about triggers and active databases in DBMS
A trigger is a procedure which is automatically invoked by the DBMS in response to changes to the database, and is specified by the database administrator (DBA). A database with a set of associated triggers is generally called an active database.
Parts of trigger
A triggers description contains three parts, which are as follows −
Event − An event is a change to the database which activates the trigger.
Condition − A query that is run when the trigger is activated is called as a condition.
Action −A procedure which is executed when the trigger is activated and its condition is true.
Use of trigger
Triggers may be used for any of the following reasons −
To implement any complex business rule, that cannot be implemented using integrity constraints.
Triggers will be used to audit the process. For example, to keep track of changes made to a table.
Trigger is used to perform automatic action when another concerned action takes place.
Types of triggers
The different types of triggers are explained below −
Statement level trigger − It is fired only once for DML statement irrespective of number of rows affected by statement. Statement-level triggers are the default type of trigger.
Before-triggers − At the time of defining a trigger we can specify whether the trigger is to be fired before a command like INSERT, DELETE, or UPDATE is executed or after the command is executed. Before triggers are automatically used to check the validity of data before the action is performed. For instance, we can use before trigger to prevent deletion of rows if deletion should not be allowed in a given case.
After-triggers − It is used after the triggering action is completed. For example, if the trigger is associated with the INSERT command then it is fired after the row is inserted into the table.
Row-level triggers − It is fired for each row that is affected by DML command. For example, if an UPDATE command updates 150 rows then a row-level trigger is fired 150 times whereas a statement-level trigger is fired only for once.
Create database trigger
To create a database trigger, we use the CREATE TRIGGER command. The details to be given at the time of creating a trigger are as follows −
- Name of the trigger.
- Table to be associated with.
- When trigger is to be fired: before or after.
- Command that invokes the trigger- UPDATE, DELETE, or INSERT.
- Whether row-level triggers or not.
- Condition to filter rows.
- PL/SQL block is to be executed when trigger is fired.
The syntax to create database trigger is as follows −
CREATE [OR REPLACE] TRIGGER triggername
{BEFORE|AFTER}
{DELETE|INSERT|UPDATE[OF COLUMNS]} ON table
[FOR EACH ROW {WHEN condition]]
[REFERENCE [OLD AS old] [NEW AS new]]
BEGIN
PL/SQL BLOCK
END.
10K+ Views
To Continue Learning Please Login