Triggers and Active data bases in DBMS

Triggers and active databases are closely related concepts in the domain of DBMS. Let's delve into what each of them means and how they are interconnected.

Triggers

A trigger is a predefined action that the database automatically executes in response to certain events on a particular table or view. Triggers are typically used to maintain the integrity of the data, automate data-related tasks, and extend the database functionalities.

There are various types of triggers based on when they are executed:

BEFORE: Trigger is executed before the triggering event.
AFTER: Trigger is executed after the triggering event.
INSTEAD OF: Trigger is used to override the triggering event, primarily for views.

They can also be categorized by the triggering event:

INSERT: Trigger is executed when a new row is inserted.
UPDATE: Trigger is executed when a row is updated.
DELETE: Trigger is executed when a row is deleted.

Here's the basic syntax for creating a trigger in SQL, using MySQL as an

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name FOR EACH ROW 
trigger_body;

trigger_name: Name of the trigger.
trigger_time: BEFORE, AFTER, or INSTEAD OF.
trigger_event: INSERT, UPDATE, or DELETE.
table_name: The name of the table associated with the trigger.
trigger_body: The set of SQL statements to be executed.

Key Features of Triggers

1. Automatic Execution: Triggers run automatically in response to data modification events. You don't have to explicitly call them.
2. Event-Driven: They are defined to execute before or after INSERT, UPDATE, and DELETE events.
3. Transitional Access: Triggers can access the "old" (pre-modification) and "new" (post-modification) values of the rows affected.

Example of a Trigger

Suppose we have an `Employees` table and we want to maintain an `AuditLog` table that keeps a record of salary changes for employees.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(255),
    Salary DECIMAL(10, 2)
);

CREATE TABLE AuditLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    ChangeDate DATETIME
);

Now, let's create a trigger that automatically inserts a record into the `AuditLog` table whenever there's an update to the `Salary` column in the `Employees` table.

mysql> DELIMITER //
mysql> CREATE TRIGGER AfterSalaryUpdate 
AFTER UPDATE ON Employees
FOR EACH ROW 
BEGIN
    IF OLD.Salary != NEW.Salary THEN
        INSERT INTO AuditLog (EmployeeID, OldSalary, NewSalary, ChangeDate) 
        VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary, NOW());
    END IF;
END;
//
mysql> DELIMITER ;

How the Trigger Works

- The trigger is named `AfterSalaryUpdate`.
- It activates `AFTER` an `UPDATE` on the `Employees` table.
- It compares the old and new salary values. If there's a change (`OLD.Salary != NEW.Salary`), it inserts a new record into the `AuditLog` table with the details of the change and the current date and time (`NOW()`).

Active Databases

An active database is a database that uses triggers and other event-driven functionalities. The term "active" signifies that the DBMS reacts automatically to changes in data and predefined events. Triggers are a primary mechanism that makes a database "active."

Key Features of Active Databases

1. Event-Condition-Action (ECA) Rule: This is the foundational concept of active databases. When a specific event occurs, the database checks a particular condition, and if that condition is met, an action is executed.
2. Reactive Behavior: The database can react to changes without external applications or users having to intervene, thanks to the ECA rules.
3. Flexibility: Active databases provide more flexibility in data management and ensure better data integrity and security.

Why are Active Databases Important?

• Integrity Maintenance: Active databases can enforce more complex business rules that can't be enforced using standard integrity constraints.
• Automation: They can automate certain tasks, reducing manual interventions.
• Alerts: They can notify users or applications when specific conditions are met.

Relation between Triggers and Active Databases

Triggers are what give an active database its "active" nature. The ability of the database to react to events automatically is primarily because of triggers that execute in response to these events.

In essence, while "trigger" refers to the specific procedural code blocks that run in response to events, "active database" refers to the broader capability of a DBMS to support and use such event-driven functionalities.