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!

Create mysql triggers in DbSchema

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

DbSchema Database Design Tool

The Art of Keeping the Data Together
Desktop app for
DbSchema ER Diagram Features Overview
Visual Design & Schema Diagram

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.