MySQL - Triggers

MySQL Triggers: Automating Database Actions

A trigger in MySQL is a named database object that is associated with a table, and it activates automatically when a particular event occurs for the table. These events are typically INSERT, UPDATE, or DELETE operations. Triggers are a powerful tool for enforcing business rules, maintaining data integrity, and creating audit trails.

Note: To create a trigger, you need the TRIGGER privilege for the associated table. By default, the SUPER privilege also allows trigger creation.

Understanding Trigger Activation Time and Event

MySQL triggers are defined by two main characteristics: the activation time and the event.

• Activation Time: This specifies whether the trigger should fire before or after the data modification. The options are BEFORE and AFTER.
• Event: This is the specific data manipulation language (DML) operation that causes the trigger to fire. The options are INSERT, UPDATE, and DELETE.

Combining these gives us six possible trigger types for any given table:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

All MySQL triggers are row-level triggers, meaning they execute once for each row affected by the triggering statement. MySQL does not support statement-level triggers, which would execute only once per statement regardless of the number of rows modified.

Accessing Data Within a Trigger

Inside a trigger’s body, you can access column values of the row being affected using special aliases:

• OLD: Refers to the existing row before an UPDATE or DELETE. It is read-only.
• NEW: Refers to the new row that will be inserted or the updated version of a row. You can read from it in BEFORE and AFTER triggers, and modify its values in a BEFORE trigger (e.g., to sanitize input).

Trigger Event	OLD	NEW
INSERT	N/A	The new row to be inserted
UPDATE	The row before the update	The row after the update
DELETE	The row being deleted	N/A

Practical Example: Creating an Audit Trail

One of the most common uses for triggers is to create an audit log. Let’s say we have an employees table and we want to log all salary changes to an salary_audits table.

First, define the employees table:

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

Next, create the salary_audits table to store the history:

CREATE TABLE salary_audits (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    changed_by VARCHAR(100),
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now, let’s create an AFTER UPDATE trigger on the employees table. This trigger will fire whenever a record is updated. We use DELIMITER because the trigger body contains semicolons.

DELIMITER //

CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Check if the salary was actually changed
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audits(employee_id, old_salary, new_salary, changed_by)
        VALUES(OLD.employee_id, OLD.salary, NEW.salary, USER());
    END IF;
END//

DELIMITER ;

Let’s test it. First, insert a new employee:

INSERT INTO employees (first_name, last_name, salary) VALUES ('Alice', 'Williams', 60000.00);

Now, update Alice’s salary. This action will fire the trigger.

UPDATE employees SET salary = 65000.00 WHERE employee_id = 1;

If you check the salary_audits table, you will see a new record:

SELECT * FROM salary_audits;

-- Output:
+----------+-------------+------------+------------+----------------+---------------------+
| audit_id | employee_id | old_salary | new_salary | changed_by     | changed_at          |
+----------+-------------+------------+------------+----------------+---------------------+
|        1 |           1 |   60000.00 |   65000.00 | user@localhost | 2023-10-27 10:30:00 |
+----------+-------------+------------+------------+----------------+---------------------+

Advantages of Triggers

• Data Integrity: Triggers can enforce complex business rules and integrity constraints that cannot be handled by standard constraints like CHECK or FOREIGN KEY.
• Automation: They run automatically, ensuring that critical actions (like auditing or updating related data) are never forgotten by the application layer.
• Centralized Logic: Business logic is stored and executed within the database, providing a single source of truth and consistency across all applications that access the data.
• Auditing: Excellent for creating detailed logs of data modifications, which is crucial for security and compliance.

Disadvantages and Considerations

• Hidden Logic: Since triggers operate in the background, they can make the database behavior difficult to understand and debug for developers who are unaware of them.
• Performance Overhead: Triggers add overhead to DML operations. A poorly written or overly complex trigger can significantly slow down database performance.
• Cascading Effects: Triggers can activate other triggers (a cascading effect), which can lead to complex and hard-to-trace execution paths.
• Debugging: Troubleshooting triggers can be more challenging than debugging application code, as they don’t provide direct feedback to the client application.

Modern MySQL Trigger Features and Restrictions

Here are some key points about triggers in modern MySQL (version 8.0+):

• Multiple Triggers: Since MySQL 5.7.2, you can define multiple triggers for the same table with the same activation time and event. You can specify the order of execution using FOLLOWS or PRECEDES clauses in the CREATE TRIGGER statement.
• Prohibited Statements: Triggers cannot contain statements that explicitly or implicitly commit or roll back a transaction (e.g., COMMIT, ROLLBACK, START TRANSACTION). They also cannot use CALL statements to invoke stored procedures that return data to the client or that use dynamic SQL.
• No RETURN Statement: Triggers do not return values, so the RETURN statement is not permitted.
• Foreign Key Actions: Triggers are not activated by cascading foreign key actions (ON DELETE CASCADE, ON UPDATE CASCADE).
• Views and Temporary Tables: You cannot create triggers on views or TEMPORARY tables.