Active Databases and Triggers

4 min readJun 9, 2022

Introduction

Databases are a collection of organized information that can easily be accessed, managed and updated. Database systems are very important to startups, business because they communicate information related to your sales transactions, product inventory, customer profiles and marketing activities.

Active databases and Triggers are interrelated to each other because they can be defined as a database with a set of associated triggers is generally called an active database.

Active Databases

Active databases are very difficult to maintain because of the complexity that arises in understanding the effect of these triggers. In such a database, DBMS initially verifies whether the particular trigger specified in the statement that modifies the database is activated or not, prior to executing the statement.

If the trigger is active then DBMS executes the condition part and then executes the action part only if the specified condition is evaluated to true. It is possible to activate more than one trigger within a single statement.

Triggers

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 trigger’s description contains three parts, which follows the ECA model in which E stands for Event which is a change to the database that activates the trigger. C stands for Condition which is defined as a query that is run when the trigger is activated is called as a condition. Final A stands for Action which is a procedure that is executed when the trigger is activated and its condition is true.

Types of triggers

There are four types of triggers explained as follows;

Statement level trigger − It is fired only once for DML statements irrespective of the number of rows affected by the 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.

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

Features of Active Database

It possesses all the concepts of a conventional database i.e. data modelling facilities, query language etc.
It supports all the functions of a traditional database like data definition, data manipulation, storage management etc.
It detects event occurrences.
It must be able to evaluate conditions and to execute actions.
It means that it has to implement rule execution.

Features of trigger

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

Advantages of Active Databases

Enhances traditional database functionalities with powerful rule processing capabilities.
Enable a uniform and centralized description of the business rules relevant to the information system.
Avoids redundancy of checking and repair operations.
Suitable platform for building large and efficient knowledge base and expert systems

Advantages of Triggers

Triggers check the integrity of data.
Counteracting invalid exchanges.
They are useful for inspecting the data.
Triggers handle errors.

Summary

Most of the modern relational databases include active databases in the form of database triggers. Active databases are those types of databases that have a mechanism to automatically respond to events in the system which also includes event driven architecture.

Authors

Sarvesh Amrute
Farhan Aga
Ananya Kumar
Akash Prasad