How To Use MySQL Triggers with Examples

MySQL triggers are a powerful feature that allows automatic actions to be taken when certain events occur within a database table. These events can be INSERT, UPDATE, or DELETE operations. Triggers can help maintain data integrity, enforce business rules, and automate repetitive tasks.

In this article, we will look into the concept of MySQL triggers, explain how they work, and provide examples to illustrate their usage.

What Are MySQL Triggers

A trigger is a named MySQL database object that is associated with a table. It is activated or “triggered” by specified events. Triggers can be categorized into two types based on the timing of their activation:

1. BEFORE Triggers: These triggers execute before the triggering event like INSERT, UPDATE, DELETE occurs.
2. AFTER Triggers: These triggers execute after the triggering event occurs.

Triggers are defined using the CREATE TRIGGER statement and can contain SQL statements that execute whenever the trigger is fired.

Why Use Triggers

Triggers can be used for various purposes, including:

1. Ensuring data meets specific criteria before it’s added or modified.
2. Keeping a log of changes made to the data for auditing purposes.
3. Propagating changes from one table to others to maintain consistency.
4. Automating repetitive tasks such as updating summary tables or denormalizing data.

For demonstration purposes of MySQL triggers, we will use the MySQL Workbench interface.

Basic Examples

Following are the basic examples of how to CREATE and DROP MySQL triggers:

CREATE Trigger

Let’s start with basic examples of MySQL triggers. Suppose we have a table employees where we want to log any changes made to employee records in a audit_log table.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    emp_salary DECIMAL(10, 2)
);
CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    action VARCHAR(10),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, let’s creating MySQL triggers that log any changes to an employee’s salary in the audit_log table:

DELIMITER $$
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.emp_salary <> OLD.emp_salary THEN
        INSERT INTO audit_log (emp_id, action, old_salary, new_salary)
        VALUES (OLD.emp_id, 'UPDATE', OLD.emp_salary, NEW.emp_salary);
    END IF;
END$$
DELIMITER ;

In this, MySQL triggers examples:

• The BEFORE UPDATE ON employees part specifies that this trigger will execute before any update operation on the employees table.
• The FOR EACH ROW clause indicates that the trigger will execute for each row that is affected by the update.
• Inside the trigger, we check if the new salary NEW.emp_salary is different from the old salary OLD.emp_salary. If it is, we insert a new record into the audit_log table.

DROP Triggers

Currently, MySQL doesn’t support direct modification of triggers. You have to drop the existing trigger and create a new one with the desired changes. You can’t disable a trigger directly. However, you can achieve this by modifying the table definition or trigger code. Use the DROP TRIGGER statement.

DROP TRIGGER IF EXISTS before_employee_update;

Practical Examples

Following are some real use cases MySQL triggers with examples described below:

Data Validation

Assume we have a products table where we want to ensure that the price of a product cannot be negative. We can use a BEFORE INSERT trigger to validate this:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);
DELIMITER $$
CREATE TRIGGER validate_product_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.product_price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Product price cannot be negative';
    END IF;
END$$
DELIMITER ;

In this example, the trigger validate_product_price ensures that before a new product is inserted, the product_price must be non-negative. If the price is negative, it raises an error using the SIGNAL statement.

Auditing Changes

Let’s extend our employees example to log both insertions and deletions:

DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (emp_id, action, new_salary)
    VALUES (NEW.emp_id, 'INSERT', NEW.emp_salary);
END$$
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (emp_id, action, old_salary)
    VALUES (OLD.emp_id, 'DELETE', OLD.emp_salary);
END$$
DELIMITER ;

In this example:

• The after_employee_insert trigger logs insertions of new employees.
• The before_employee_delete trigger logs deletions of employees.

Best Practices

While triggers are powerful they can impact performance especially if they perform complex operations or are triggered frequently. It is essential to:

• Keep the logic within triggers simple and avoid extensive operations.
• Ensure that any tables accessed within triggers are well-indexed.
• Use consistent naming conventions for triggers to improve readability.
• Document the purpose and behavior of triggers to aid future maintenance.

Conclusion

MySQL triggers are a versatile tool that can help enforce business rules maintain data integrity and automate repetitive tasks. By understanding how to create and manage triggers, you can use their power to enhance your database applications. However, it’s essential to use triggers carefully to streamline the performances.

FAQ