MySQL CREATE TRIGGER

Introduction

Triggers are special types of stored procedures that automatically execute in response to certain events on a particular table in a database. In MySQL, triggers can be defined to run when an INSERT, UPDATE, or DELETE operation occurs on a specified table. They are powerful tools for enforcing business rules, maintaining data integrity, and automating processes without requiring application-level code.

In this guide, you'll learn:

• What MySQL triggers are and why they're useful
• How to create triggers using the CREATE TRIGGER statement
• How to use different trigger timing and event combinations
• Best practices and real-world applications of triggers

Understanding MySQL Triggers

A trigger is a database object that is associated with a table and activates when a particular event (such as an insert, update, or delete operation) occurs for that table. When the trigger is activated, it can execute one or more SQL statements defined in its body.

Trigger Components

A MySQL trigger consists of:

1. Name: A unique identifier for the trigger
2. Timing: BEFORE or AFTER to specify when the trigger should execute
3. Event: INSERT, UPDATE, or DELETE operation that activates the trigger
4. Table: The table the trigger is associated with
5. Body: The SQL statements to execute when the trigger is activated

Trigger Timing

MySQL supports two trigger timings:

• BEFORE: Executes the trigger before the triggering event (insert, update, delete)
• AFTER: Executes the trigger after the triggering event has completed

CREATE TRIGGER Syntax

The basic syntax for creating a trigger in MySQL is:

sql

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
[trigger_body]

Where:

• trigger_name is the name of your trigger
• {BEFORE | AFTER} determines when the trigger executes
• {INSERT | UPDATE | DELETE} specifies which operation activates the trigger
• table_name is the name of the table the trigger is associated with
• FOR EACH ROW indicates that the trigger will execute once for each row affected by the triggering statement
• trigger_body contains the SQL statements to execute when the trigger activates

Creating Your First Trigger

Let's start with a simple example. Suppose we have two tables: products and product_audit. We want to track changes to product prices by recording the old and new prices in the audit table whenever a price is updated.

First, let's create the tables:

sql

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE product_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now, let's create a trigger that will log price changes:

sql

CREATE TRIGGER price_update_audit
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO product_audit (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
END;

This trigger will:

1. Execute after an update operation on the products table
2. Check if the price has changed
3. If so, insert a new record into the product_audit table with the old and new prices

Testing the Trigger

Let's insert a product and then update its price to see the trigger in action:

sql

-- Insert a product
INSERT INTO products (product_name, price, stock) 
VALUES ('Laptop', 999.99, 50);

-- Check the inserted product
SELECT * FROM products;

This will output:

| product_id | product_name | price   | stock |
|------------|--------------|---------|-------|
| 1          | Laptop       | 999.99  | 50    |

Now, let's update the price:

sql

-- Update the product price
UPDATE products 
SET price = 1099.99 
WHERE product_id = 1;

-- Check the updated product
SELECT * FROM products;

Updated products table:

| product_id | product_name | price   | stock |
|------------|--------------|---------|-------|
| 1          | Laptop       | 1099.99 | 50    |

Let's check our audit table:

sql

-- Check the audit table
SELECT * FROM product_audit;

The audit table will show:

| audit_id | product_id | old_price | new_price | change_date        |
|----------|------------|-----------|-----------|---------------------|
| 1        | 1          | 999.99    | 1099.99   | 2023-10-10 14:25:30 |

Our trigger worked! It automatically recorded the price change in the audit table.

Working with OLD and NEW References

In the trigger body, MySQL provides two special references:

• OLD: Contains the row values before the update or delete operation
• NEW: Contains the new values that will be inserted or updated

These references are only relevant in specific contexts:

Event	OLD available?	NEW available?
INSERT	No (NULL)	Yes
UPDATE	Yes	Yes
DELETE	Yes	No (NULL)

Creating Different Types of Triggers

Let's explore different trigger timing and event combinations with practical examples.

BEFORE INSERT Trigger

A BEFORE INSERT trigger is useful for validating or modifying data before it's inserted.

sql

CREATE TRIGGER validate_stock_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.stock < 0 THEN
        SET NEW.stock = 0;
    END IF;
END;

This trigger ensures that the stock value is never negative. If someone tries to insert a product with negative stock, it will automatically set the stock to 0.

AFTER INSERT Trigger

An AFTER INSERT trigger is perfect for actions that should occur after a successful insert.

sql

CREATE TABLE inventory_status (
    status_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    status VARCHAR(20),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER update_inventory_status
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO inventory_status (product_id, status)
    VALUES (NEW.product_id, 
           CASE 
               WHEN NEW.stock > 20 THEN 'In Stock'
               WHEN NEW.stock > 0 THEN 'Low Stock'
               ELSE 'Out of Stock'
           END);
END;

This trigger automatically creates an inventory status record whenever a new product is added.

BEFORE UPDATE Trigger

A BEFORE UPDATE trigger can validate or modify data before an update is applied.

sql

CREATE TRIGGER capitalize_product_name
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.product_name = UPPER(NEW.product_name);
END;

This trigger converts product names to uppercase before they're updated in the database.

BEFORE DELETE Trigger

A BEFORE DELETE trigger can perform checks before allowing a delete operation.

sql

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL
);

CREATE TRIGGER prevent_product_deletion
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    DECLARE order_count INT;
    
    -- Check if product exists in any orders
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE product_id = OLD.product_id;
    
    -- Prevent deletion if product has orders
    IF order_count > 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Cannot delete product with existing orders';
    END IF;
END;

This trigger prevents the deletion of products that have associated orders.

Compound Triggers

You can create multiple triggers for the same table, timing, and event. However, it's often better to combine related logic into a single trigger for clarity and performance.

sql

CREATE TRIGGER product_update_actions
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- Log price changes
    IF OLD.price != NEW.price THEN
        INSERT INTO product_audit (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
    
    -- Update inventory status on stock change
    IF OLD.stock != NEW.stock THEN
        INSERT INTO inventory_status (product_id, status)
        VALUES (NEW.product_id, 
               CASE 
                   WHEN NEW.stock > 20 THEN 'In Stock'
                   WHEN NEW.stock > 0 THEN 'Low Stock'
                   ELSE 'Out of Stock'
               END);
    END IF;
END;

Real-World Applications of Triggers

1. Audit Trails

As demonstrated in our first example, triggers are excellent for maintaining audit trails of data changes without requiring application-level code.

sql

-- Create an employee audit table
CREATE TABLE employee_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    action_type VARCHAR(10) NOT NULL,
    action_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    action_user VARCHAR(50) DEFAULT USER(),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2)
);

-- Create a comprehensive audit trigger
CREATE TRIGGER employee_audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (
        employee_id, 
        action_type,
        old_salary,
        new_salary
    )
    VALUES (
        OLD.employee_id,
        'UPDATE',
        OLD.salary,
        NEW.salary
    );
END;

2. Data Synchronization

Triggers can keep multiple tables synchronized automatically.

sql

-- Assume we have a summary table for product category statistics
CREATE TABLE category_stats (
    category_id INT PRIMARY KEY,
    product_count INT DEFAULT 0,
    avg_price DECIMAL(10, 2),
    total_stock INT DEFAULT 0
);

-- This trigger updates the stats whenever a product is added
CREATE TRIGGER update_category_stats
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    -- If category exists in stats, update it
    IF EXISTS (SELECT 1 FROM category_stats WHERE category_id = NEW.category_id) THEN
        UPDATE category_stats
        SET product_count = product_count + 1,
            avg_price = (avg_price * product_count + NEW.price) / (product_count + 1),
            total_stock = total_stock + NEW.stock
        WHERE category_id = NEW.category_id;
    ELSE
        -- If category doesn't exist, insert it
        INSERT INTO category_stats (category_id, product_count, avg_price, total_stock)
        VALUES (NEW.category_id, 1, NEW.price, NEW.stock);
    END IF;
END;

3. Business Rules Enforcement

Triggers can enforce complex business rules at the database level.

sql

CREATE TRIGGER enforce_credit_limit
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE customer_balance DECIMAL(10, 2);
    DECLARE credit_limit DECIMAL(10, 2);
    
    -- Get customer's current balance and credit limit
    SELECT balance, credit_limit INTO customer_balance, credit_limit
    FROM customers
    WHERE customer_id = NEW.customer_id;
    
    -- Check if order would exceed credit limit
    IF (customer_balance + NEW.order_amount) > credit_limit THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Order exceeds customer credit limit';
    END IF;
END;

Best Practices for Using Triggers

1. Keep triggers simple: Complex trigger logic can make debugging difficult.
2. Document your triggers: Always comment your trigger code and maintain documentation about what triggers exist and what they do.
3. Consider performance: Triggers execute for each affected row, so they can impact performance for large operations.
4. Avoid infinite loops: Be careful when creating triggers that modify tables that could activate other triggers.
5. Don't overuse triggers: They should be used for data integrity, not for business logic that belongs in the application.
6. Include error handling: Use SIGNAL to raise meaningful errors when conditions aren't met.

Managing Triggers

Viewing Existing Triggers

You can view all triggers in a database using:

sql

SHOW TRIGGERS;

Or triggers for a specific table:

sql

SHOW TRIGGERS LIKE 'products';

For more detailed information:

sql

SELECT * FROM information_schema.triggers WHERE trigger_schema = 'your_database_name';

Dropping a Trigger

To remove a trigger:

sql

DROP TRIGGER [IF EXISTS] trigger_name;

For example:

sql

DROP TRIGGER IF EXISTS price_update_audit;

Trigger Limitations in MySQL

There are some limitations to be aware of when working with MySQL triggers:

1. Triggers cannot use CALL statements to invoke stored procedures that return result sets
2. Triggers cannot use statements that explicitly or implicitly begin or end a transaction
3. MySQL does not allow more than one trigger with the same action time and event for a given table
4. Triggers cannot access tables by name in dynamic SQL statements
5. A trigger cannot modify a table that is already being used by the statement that activated the trigger

Summary

MySQL triggers are powerful tools for automating database actions and maintaining data integrity. They allow you to execute SQL code automatically in response to specific events on database tables. Key points to remember:

• Triggers can execute BEFORE or AFTER an INSERT, UPDATE, or DELETE operation
• They use the OLD and NEW references to access row data before and after changes
• Triggers are ideal for audit trails, data validation, and enforcing business rules
• Complex trigger logic should be carefully designed to avoid performance issues

As your database applications grow in complexity, triggers can help ensure data consistency without requiring redundant application code.

Exercises

1. Create a trigger that logs all deletions from the products table to a deleted_products table.
2. Create a BEFORE INSERT trigger that automatically generates a SKU (Stock Keeping Unit) for new products based on their category and an auto-increment number.
3. Create a trigger that prevents updates to product prices that would result in more than a 50% increase or decrease.
4. Implement a trigger system to maintain a running total of inventory value (price * stock) for each category.

Additional Resources

• MySQL Trigger Documentation
• MySQL Stored Procedures
• MySQL Event Scheduler

Understanding how to effectively use triggers will help you build more robust database applications that can enforce business rules at the data layer, maintaining data integrity regardless of which application accesses your database.