-

ACTIVE DATABASES & TRIGGERS


INTRODUCTION

A database with a set of triggers is known as an active database. Due to the overall complexity involved in understanding the impacts of different triggers, maintaining these databases is extremely tough. Before executing the query in such a database, the DBMS checks to see if the specific trigger provided in the query that updates the database is activated.
Source: Database

Multiple triggers can be activated within a single statement at once. If the trigger is active, the condition section is executed first, followed by the action part if the stated condition is true. 

In this case, the DBMS randomly executes each of the triggers. When an action phase of a trigger is executed, it might either activate other triggers or the same trigger that initiated the action. The term "recursive trigger" refers to a sort of trigger that triggers itself. Although the DBMS executes such trigger chains in a predetermined manner, it has an impact on the idea of comprehension.


Let’s Understand the concept more thoroughly...

ACTIVE DATABASES

An active database is one that has an event-driven structure, which is typically implemented in the process of Event Condition Action rules which can respond to events both inside and outside the database.

Source: Active Databases

An active database system (ADBS) monitors events of interest and, when they arise, initiates a prompt response.

It's commonly used for security monitoring, alerting, information gathering, and authorization.


ADVANTAGES OF ACTIVE DATABASE



CHARACTERISTICS OF ACTIVE DATABASE


  • It has all of the principles of a traditional database, such as data modeling capabilities, query language, and so on.
  • It includes all typical database tasks such as data definition, data manipulation, storage management, and so on.
  • It detects the occurrence of events.
  • It must be able to assess situations and carry out actions. This implies that rule execution must be implemented.
  • It assists in the development and administration of ECA rules.

TRIGGERS


A trigger is a database administrator-defined procedure that is automatically invoked by the database management system (DBMS) in reaction to database changes (DBA). A database with a set of associated triggers is generally called an active database.

Source: Triggers
PARTS OF TRIGGER-


Parts

Description

Event

An event is a database change that activates the trigger. A triggering event is the SQL statement that fires a trigger. 
An INSERT, UPDATE, or DELETE statement are the example for a specific table that can be its triggering event.

Restriction 

A trigger restriction specifies a Boolean statement that must be TRUE for the trigger to activate. If the trigger restriction is FALSE, the trigger action will not be executed. A trigger restriction is an option for triggers which are activated for each row. Its goal is to govern the execution of a trigger on a conditional basis. A trigger restriction is provided using a WHEN clause. It is an optional component of trigger.

Action

A trigger action is the technique  that contains the SQL statements and PL/SQL code that will be invoked when a triggering statement is delivered and the trigger restriction is TRUE.


TYPES OF TRIGGERS

Different types of triggers are classified into three categories - 

A. Level triggers-

Level trigger has two types i.e. Row Level Trigger and Statement Level Trigger that are explained below -


  1. Row Level Trigger


  • A row-level trigger triggers once for each row affected by the triggering event. Row level trigger uses a FOR EACH ROW clause in its triggering statement always.

  • Data-related operations including data auditing and data validation derive from row-level triggers.


Syntax for creating row level trigger

2. Statement Level Trigger 


Whenever a triggering event occurs on a database, a statement-level trigger is triggered, irrespective of how many rows are affected.


Key points to remember  -

  • Statement level triggers are default triggers that can be created once a trigger is created without requiring use for each row statement.

  • Statement level trigger fires for only once for each triggering event.

  • It also fires once if no rows are impacted.


Syntax of creating a statement-level trigger

DEMO-

CODE:









OUTPUT:



B. Event Triggers - 


Event triggers are categorized into three types that are DDL, DML and database triggers. Operations performed by them are listed below - 


  1. DDL triggers


  • SQL Server's DDL triggers are triggered by various data definition language (DDL) events such as Create, Alter, Drop, Grant, etc,. DDL triggers are only activated once the DDL statements that set them off have been executed.

  • INSTEAD OF triggers cannot be used with DDL triggers.

  • Events that affect local or global temporary tables and stored procedures do not activate DDL triggers.


  1. DML triggers

Source: DML Triggers

  • DML triggers are a form of stored method that takes place automatically whenever a data manipulation language (DML) event occurs that affects the table or view specified in the trigger.
  • INSERT, UPDATE, and DELETE statements are examples of DML events.
3. DATABASE triggers
  • Database triggers are triggered by the execution of any database operation, such as LOGON, LOGOFF, SHUTDOWN, SERVERERROR, and so on.
DEMO-

CODE:

OUTPUT (before updating):

To get the salary information:

CODE:

OUTPUT:

C. Timing Triggers -   

 

  1. Before trigger

  • A Before Trigger is a sort of trigger which executes automatically before a specific operation on the table actually occurs.

  • Before triggers are used to validate data before it is accepted into the table and to evaluate values before they are deleted from the table.


  1. After trigger

  • An after trigger is a sort of trigger that fires automatically after a specific action on the table.

  • After triggers are used to update data in a table after a change occurs.


DEMO-

CODE:

OUTPUT:

ADVANTAGES


Some of the advantages of using MySQL triggers are listed below:


  1. Trigger can assist us in automating data changes.

  2. Triggers can provide a way to check the database's data integrity.

  3. It is useful for identifying errors in database layers.

  4. Facilitates data auditing.

  5. It allows us to reuse queries that have already been written.

  6. Plays a major role in the detection of database faults.

  7. Triggers can be used to log an event and can also store the information on the table.


DISADVANTAGES


The disadvantages of using MySQL triggers are listed below:


  1. Tends to increase the site's overhead expenses.

  2. It only provides expanded validations; not all validations are available through SQL triggers.

  3. If we activate the incorrect trigger by accident, we risk losing the original data.

  4. It takes a long time to troubleshoot difficulties caused by triggers.

  5. Even if there is a minor inaccuracy in the query, it can generate logical issues in the application.


USES OF TRIGGER

  1. To establish data consistency, duplicate data to separate files.
  2. It's useful for keeping track of the changes to a table.
  3. Triggers are used to perform automatic actions when another action is triggered.
  4. Enforces referential integrity, Synchronous replication of tables and Preventing invalid transactions.


By-

CS-A, Batch-1, Group SY4

Authors:

Anikka Pandey

Shreya Ambekar

Amey Joshi

Somesh Alkanthi

Aishwarya Kale

Comments

Post a Comment