DbSchema | MySQL - How to Create a Trigger?

What is a Trigger?

A trigger in MySQL is a set of actions automatically executed in response to certain events that occur on a database table. You don't need to manually execute these actions, because the trigger runs automatically when an event like an INSERT, UPDATE, or DELETE occurs on a table.
Triggers are great for automating tasks, enforcing business rules, and maintaining data integrity.

Common Trigger Events

• INSERT: Executes when a new row is inserted into a table.
• UPDATE: Executes when a row is updated in a table.
• DELETE: Executes when a row is deleted from a table.

You can create triggers that run:

• Before the event (BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE).
• After the event (AFTER INSERT, AFTER UPDATE, AFTER DELETE).

Example 1: Logging Updates in a Table

Imagine you have a products table and want to keep track of any updates to product prices. Every time the price of a product is updated, you want to insert a record into a product_price_log table to log the changes (old price, new price, and the timestamp).

Step 1: Create the products Table

CREATE TABLE company.products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

Step 2: Create the product_price_log Table

CREATE TABLE company.product_price_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create the Trigger

This trigger will fire before an update occurs to the price column in the products table. It logs the old and new prices in the product_price_log table.

DELIMITER $$

CREATE TRIGGER before_product_price_update
BEFORE UPDATE ON company.products
FOR EACH ROW
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO company.product_price_log (product_id, old_price, new_price)
        VALUES (OLD.product_id, OLD.price, NEW.price);
    END IF;
END $$

DELIMITER ;

Explanation:

• BEFORE UPDATE: The trigger runs before the UPDATE operation is performed on the products table.

• OLD and NEW: OLD refers to the values before the update, NEW refers to the values after the update.

• Condition: The trigger only logs a change if the price has actually been modified (OLD.price <> NEW.price).

Step 4: Test the Trigger

1. Insert Sample Data:

   INSERT INTO company.products (product_id, product_name, price) VALUES (1, 'Sample Product', 20.00);

2. Update the price:

   UPDATE company.products SET price = 25.00 WHERE product_id = 1;

3. Query the Log:

   SELECT * FROM company.product_price_log;

See the results in DbSchema, a SQL client for designing and manage your MySQL database!

• If you want to dive deeper into MySQL triggers and database management, check out the MySQL Documentation. For solutions to common MySQL issues, you can also visit Stack Overflow.

• Once you’re familiar with the basics of MySQL triggers, you might want to explore the tools that make it easier to write and execute SQL queries. For a detailed guide on a free SQL editor, check out our free SQL editor guide.