How to Create a Trigger in MySQL?

#mysql #sql #database #dbschema

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

Insert Sample Data:

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

UPDATE company.products
SET price = 25.00
WHERE product_id = 1;
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.