DEV Community

Cover image for How to Create a Trigger in MySQL?
Roxana Maria Haidiner
Roxana Maria Haidiner

Posted on • Originally published at dbschema.com

2 2 2 2

How to Create a Trigger in MySQL?

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)
);
Enter fullscreen mode Exit fullscreen mode




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
);
Enter fullscreen mode Exit fullscreen mode




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 ;

Enter fullscreen mode Exit fullscreen mode




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!

Create a trigger in MySql

  • 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.

Embedded BI Dashboards are 💩 Building them yourself is 💩

Embedded BI Dashboards are 💩 Building them yourself is 💩

Use our developer toolkit to build fast-loading, native-feeling dashboards for your customers (without the sh*t).

Get early access

Top comments (2)

Collapse
 
robertghenciu profile image
Robert •

Do you have another article about creating a materialized view for MySQL?

Collapse
 
roxana_haidiner profile image
Roxana Maria Haidiner •

Not yet. I will write one about Materialized Views...

Heroku

Tired of jumping between terminals, dashboards, and code?

Check out this demo showcasing how tools like Cursor can connect to Heroku through the MCP, letting you trigger actions like deployments, scaling, or provisioning—all without leaving your editor.

Learn More

👋 Kindness is contagious

Dive into this thoughtful piece, beloved in the supportive DEV Community. Coders of every background are invited to share and elevate our collective know-how.

A sincere "thank you" can brighten someone's day—leave your appreciation below!

On DEV, sharing knowledge smooths our journey and tightens our community bonds. Enjoyed this? A quick thank you to the author is hugely appreciated.

Okay