What are MySQL Triggers?

MySQL triggers are special stored programs that automatically execute in response to certain events on a particular table. These events can be INSERT, UPDATE, or DELETE operations. Triggers allow you to enforce business rules, maintain data integrity, and automate database tasks.

Triggers: Practical Examples

Understanding Trigger Components

A MySQL trigger consists of the following key components:

• Trigger Name: A unique identifier for the trigger.
• Trigger Time: Determines when the trigger is executed. Options are BEFORE and AFTER.
• Trigger Event: The operation that activates the trigger. Options are INSERT, UPDATE, and DELETE.
• Trigger Table: The table the trigger is associated with.
• Trigger Body: The SQL code that is executed when the trigger is activated. This can be a single statement or a block of statements.

Syntax

 CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body (SQL statements)
END; 

trigger_time can be BEFORE or AFTER. trigger_event can be INSERT, UPDATE, or DELETE. FOR EACH ROW specifies that the trigger will be executed for each row affected by the event.

NEW and OLD Keywords

Inside the trigger body, you can access the new and old versions of the row being affected. These are referenced using the NEW and OLD keywords.

• NEW: Refers to the new row being inserted or the new values of the row being updated. Available in BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, and AFTER UPDATE triggers.
• OLD: Refers to the old row being deleted or the original values of the row being updated. Available in BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE triggers.

Real-World Examples of Triggers

Example 1: Automatically Updating Related Tables

Scenario: We have two tables: orders and order_summary. When a new order is placed (inserted into the orders table), we want to update the order_summary table to reflect the total number of orders and total revenue.

Table Structures:

 CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE order_summary (
    total_orders INT DEFAULT 0,
    total_revenue DECIMAL(20, 2) DEFAULT 0.00
);

-- Initial data in order_summary
INSERT INTO order_summary (total_orders, total_revenue) VALUES (0, 0.00); 

Trigger Code:

 CREATE TRIGGER after_order_insert
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
    UPDATE order_summary
    SET total_orders = total_orders + 1,
        total_revenue = total_revenue + NEW.amount;
END; 

Explanation:

• The trigger after_order_insert is activated AFTER an INSERT operation on the orders table.
• FOR EACH ROW means the trigger executes for each row inserted into the orders table.
• The trigger body updates the order_summary table, incrementing total_orders by 1 and adding the amount from the new order (NEW.amount) to total_revenue.

Testing:

 INSERT INTO orders (customer_id, order_date, amount) VALUES (1, '2023-10-26', 100.00);

SELECT * FROM order_summary;  -- Observe the updated values 

Cleanup

 DROP TRIGGER IF EXISTS after_order_insert; 

Example 2: Preventing Invalid Data Entries

Scenario: We have a table employees with a salary column. We want to ensure that no employee's salary is ever set to a negative value.

Table Structure:

 CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(255),
    salary DECIMAL(10, 2)
); 

Trigger Code:

 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;

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

Explanation:

• The trigger before_employee_insert is activated BEFORE an INSERT operation on the employees table. We also need a similar trigger for UPDATE operations.
• FOR EACH ROW means the trigger executes for each row being inserted/updated.
• The trigger body checks if the new salary (NEW.salary) is less than 0.
• If the salary is negative, the SIGNAL SQLSTATE '45000' statement raises a generic user-defined exception. The SET MESSAGE_TEXT provides a descriptive error message. This prevents the invalid data from being inserted or updated.

Testing:

 -- Attempt to insert an employee with a negative salary
INSERT INTO employees (employee_name, salary) VALUES ('Test Employee', -100.00);  -- This will result in an error

-- Attempt to update an existing employee's salary to a negative value
INSERT INTO employees (employee_name, salary) VALUES ('Existing Employee', 50000.00);
UPDATE employees SET salary = -200.00 WHERE employee_name = 'Existing Employee'; -- This will result in an error 

Cleanup

 DROP TRIGGER IF EXISTS before_employee_insert;
DROP TRIGGER IF EXISTS before_employee_update; 

Example 3: Auditing Changes to Data

Scenario We want to track changes made to a product table, recording who made the change and when.

Table Structures

 CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE product_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    old_product_name VARCHAR(255),
    new_product_name VARCHAR(255),
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(255) -- To store the user who made the change (requires proper user authentication setup)
); 

Trigger Code

 CREATE TRIGGER after_product_update
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit (
        product_id,
        old_product_name,
        new_product_name,
        old_price,
        new_price,
        updated_at,
        updated_by
    ) VALUES (
        OLD.product_id,
        OLD.product_name,
        NEW.product_name,
        OLD.price,
        NEW.price,
        NOW(),
        USER() -- Captures the current user's username
    );
END; 

Explanation:

• The trigger after_product_update fires after each update to the products table.
• It captures the old and new values of product attributes (name, price) along with the timestamp and the user who performed the update.
• The USER() function retrieves the MySQL username of the user who executed the update statement.

Testing:

 -- Insert a sample product
INSERT INTO products (product_name, price) VALUES ('Widget', 19.99);

-- Update the product's price
UPDATE products SET price = 24.99 WHERE product_name = 'Widget';

-- Check the audit table
SELECT * FROM product_audit; 

Cleanup

 DROP TRIGGER IF EXISTS after_product_update; 

Important Considerations When Using Triggers

• Performance: Triggers can impact performance, especially if they contain complex logic. Carefully design triggers to be as efficient as possible.
• Complexity: Overusing triggers can make a database schema difficult to understand and maintain.
• Debugging: Debugging triggers can be challenging. Use logging and careful testing to identify and resolve issues.
• Cascading Triggers: Be aware of the potential for cascading triggers (where one trigger activates another), which can lead to unexpected behavior.