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.

Heroku

The AI PaaS for deploying, managing, and scaling apps.

Heroku tackles the toil — patching and upgrading, 24/7 ops and security, build systems, failovers, and more. Stay focused on building great data-driven applications.

Get Started

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

Explore this practical breakdown on DEV’s open platform, where developers from every background come together to push boundaries. No matter your experience, your viewpoint enriches the conversation.

Dropping a simple “thank you” or question in the comments goes a long way in supporting authors—your feedback helps ideas evolve.

At DEV, shared discovery drives progress and builds lasting bonds. If this post resonated, a quick nod of appreciation can make all the difference.

Okay