MySQL Triggers

Introduction

In this chapter, we will learn about triggers in MySQL. A trigger is a database object that is automatically executed or fired when certain events occur in a table. Triggers can help enforce business rules, validate input data, maintain audit trails, and synchronize tables. We will cover the syntax for creating triggers, examples of their usage, and important considerations for using triggers in MySQL.

What is a Trigger?

A trigger is a special kind of stored procedure that automatically executes in response to specific events on a particular table. These events can be INSERT, UPDATE, or DELETE operations.

Types of Triggers

MySQL supports two types of triggers based on the timing of the execution:

1. BEFORE Trigger: Executes before the event.
2. AFTER Trigger: Executes after the event.

Syntax

The basic syntax for creating a trigger is:

CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON table_name FOR EACH ROW
BEGIN
    -- trigger body
END;

Example

Here is an example of how to create a trigger that logs changes to an employees table:

1. Create the Audit Table:

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(10),
    action_time DATETIME
);

2. Create the Trigger:

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (OLD.employee_id, 'update', NOW());
END;

In this example, the trigger before_employee_update logs any updates to the employees table into the employee_audit table before the update occurs.

Full Example

Let’s go through a complete example where we create a database, tables, and triggers to demonstrate their usage.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees and Audit Tables:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    action VARCHAR(10),
    action_time DATETIME
);

4. Create Triggers:

Before Update Trigger:

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (OLD.employee_id, 'update', NOW());
END;

After Insert Trigger:

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.employee_id, 'insert', NOW());
END;

5. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES
('Rahul', 'Sharma', 1, 50000.00),
('Priya', 'Singh', 2, 60000.00),
('Amit', 'Kumar', 3, 55000.00),
('Neha', 'Verma', 1, 58000.00),
('Sahil', 'Mehta', 4, 62000.00);

6. Update an Employee’s Salary:

UPDATE employees SET salary = 65000.00 WHERE employee_id = 2;

7. Query the Employee Audit Table:

SELECT * FROM employee_audit;

Output

In this example, the before_employee_update trigger logs updates to the employees table, and the after_employee_insert trigger logs inserts into the employees table.

Using Triggers for Data Validation

You can use triggers to enforce data validation rules.

Example: Ensure Salary is Non-Negative

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

Important Considerations

• Trigger Performance: Triggers can affect performance, especially if they contain complex logic or if they fire frequently. Use them judiciously.
• Nested Triggers: MySQL does not support triggers that call other triggers. Be cautious of creating circular dependencies.
• Error Handling: Use the SIGNAL statement to raise custom errors within triggers for data validation and business rule enforcement.
• Permissions: Ensure appropriate permissions are set for users to create and execute triggers.

Conclusion

Triggers in MySQL are powerful tools for automating tasks, enforcing business rules, and maintaining data integrity. This chapter covered the syntax for creating triggers, provided examples of their usage, and discussed important considerations.