A trigger in a Database Management System (DBMS) is a special type of stored procedure that is automatically executed or ‘fired’ in response to a specific event or data modification (INSERT, UPDATE, or DELETE) on a particular table. Triggers are used to maintain data integrity, enforce complex business rules, and automate tasks within the database.
How Do Triggers Work?
You can think of a trigger as a set of instructions that you tell the database to follow automatically whenever a certain action happens. For example, you can create a trigger that automatically creates an audit record in a separate `audit_log` table every time a row is deleted from the `employees` table. The trigger is defined on a specific table and is associated with one of the three DML (Data Manipulation Language) events: INSERT, UPDATE, or DELETE.
Triggers are “event-driven.” They are not called directly by a user; they are invoked by the DBMS itself when the triggering event occurs.
Types of Triggers
Triggers can be classified based on when they are executed in relation to the triggering event. The two main timing types are BEFORE and AFTER triggers.
| Type | When It Fires | Common Use Cases | Key Characteristics |
|---|---|---|---|
| BEFORE Triggers | Executes *before* the triggering DML operation (INSERT, UPDATE, DELETE) is performed on the table. | Validating input data before it is saved, modifying data before it is inserted (e.g., converting a name to uppercase), or preventing certain operations based on a condition. | Can be used to change the new data values before they are stored. If a BEFORE trigger fails, the main DML operation is cancelled. |
| AFTER Triggers | Executes *after* the triggering DML operation has been successfully completed. | Creating audit trails, logging historical data, updating summary tables (e.g., updating total sales in another table after a new order is inserted). | Cannot modify the data that has just been inserted or updated. They are ideal for actions that need to happen after the main data change is confirmed. |
Triggers can also be classified by their level of granularity:
- Row-Level Trigger: Fires once for each row that is affected by the DML statement. If an UPDATE statement changes 10 rows, a row-level trigger will fire 10 times.
- Statement-Level Trigger: Fires only once for each DML statement, regardless of how many rows it affects.
A Simple Trigger Example (SQL)
Let’s consider a scenario where we want to maintain a log of salary updates for an `employees` table. We can create an AFTER UPDATE trigger to automatically insert the old salary into a `salary_audit` table.
Tables:
- `employees` (emp_id, emp_name, salary)
- `salary_audit` (audit_id, emp_id, old_salary, change_date)
Trigger Code (MySQL Syntax):
DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit(emp_id, old_salary, change_date)
VALUES(OLD.emp_id, OLD.salary, NOW());
END$$
DELIMITER ;
Explanation:
- `CREATE TRIGGER before_employee_update`: Defines a new trigger named `before_employee_update`.
- `BEFORE UPDATE ON employees`: Specifies that the trigger should fire before any UPDATE operation on the `employees` table.
- `FOR EACH ROW`: Makes it a row-level trigger.
- `BEGIN…END`: Contains the code that the trigger will execute.
- `INSERT INTO salary_audit…`: This is the action. It inserts a new row into the `salary_audit` table.
- `OLD.emp_id`, `OLD.salary`: The `OLD` keyword allows you to access the values of the row *before* the update. `NOW()` is a function that gets the current timestamp.
Now, whenever someone executes `UPDATE employees SET salary = 60000 WHERE emp_id = 101;`, this trigger will automatically fire before the update happens, saving the previous salary in the audit table.
Advantages and Disadvantages of Triggers
Advantages:
- Enforcing Data Integrity: They can enforce complex business rules that are difficult to implement with simple constraints.
- Automation: They automate tasks, reducing the need for application-level code to perform related actions.
- Auditing: Excellent for creating detailed audit logs of data changes.
Disadvantages:
- Complexity: They can be hard to debug as they execute implicitly. Nested triggers can create a complex execution chain.
- Performance Overhead: A poorly written trigger can slow down DML operations on a table, impacting database performance.
Understanding triggers is a key concept for database developers and administrators, just as understanding the three levels of security is for a cybersecurity expert. This database feature provides a powerful mechanism for maintaining a robust and intelligent database system.
Frequently Asked Questions (FAQs)
What is a trigger in DBMS?
A trigger is a stored program in a database that automatically executes when a specific event, such as an INSERT, UPDATE, or DELETE, occurs on a table. It’s used to automate actions and enforce data integrity.
What are the different types of triggers?
The main types are BEFORE triggers (which run before the data modification) and AFTER triggers (which run after the modification). They can also be categorized as row-level triggers (run for each affected row) or statement-level triggers (run once per statement).
What is the use of a trigger?
Triggers are used for various purposes, including creating audit trails to log data changes, validating complex data before insertion, maintaining data consistency across related tables, and enforcing business rules that cannot be handled by standard constraints.
What is the difference between a trigger and a stored procedure?
A trigger is executed automatically (implicitly) in response to a DML event on a table. A stored procedure must be executed manually (explicitly) by being called by a user or an application. Triggers cannot be called directly.
Can a trigger call a stored procedure?
Yes, the code within a trigger can execute a `CALL` statement to invoke a stored procedure. This is a common practice to reuse code and keep the logic within the trigger itself simple and clean.