Triggers and Active databases
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
SyntaxCREATE 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
- Automatic Execution: Triggers run automatically in response to data modification events. You don't have to explicitly call them.
- Event-Driven: They are defined to execute before or after INSERT, UPDATE, and DELETE events.
- 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
- 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.
- Reactive Behavior: The database can react to changes without external applications or users having to intervene, thanks to the ECA rules.
- 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.