Using MySQL triggers

This guide explains how to create and use MySQL triggers. Triggers are predefined rules attached to a table that activate automatically before or after SQL statements that insert, update, or delete data.

Triggers are useful for automating tasks such as updating values, enforcing rules, or logging changes.

Creating and Using Triggers

You can create triggers on any Ruachost server that uses MySQL.

Step 1: Set Up a Test Database

Create a database for testing (replace username with your account username):

CREATE DATABASE username_test;

Note: Run SQL commands via MySQL CLI or phpMyAdmin.

Select the database:

USE username_test;

Step 2: Create a Table

Create a products table:

CREATE TABLE products (
  prod_id INT NOT NULL AUTO_INCREMENT,
  prod_name VARCHAR(20) NOT NULL,
  prod_cost FLOAT NOT NULL DEFAULT 0.0,
  prod_price FLOAT NOT NULL DEFAULT 0.0,
  PRIMARY KEY(prod_id)
);

Add sample data:

INSERT INTO products (prod_name, prod_cost, prod_price)
VALUES
  ('Basic Widget', 5.95, 8.35),
  ('Micro Widget', 0.95, 1.35),
  ('Mega Widget', 99.95, 140.00);

Step 3: Create a Trigger

This trigger automatically updates the product price when the cost changes:

DELIMITER $$
CREATE TRIGGER `updateProductPrice`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
  IF NEW.prod_cost < OLD.prod_cost THEN
    SET NEW.prod_price = NEW.prod_cost * 1.40;
  END IF;
END$$
DELIMITER ;

Note

DELIMITER $$ prevents MySQL from ending the trigger definition too soon.
DELIMITER ; restores the normal command delimiter.

Step 4: Use the Trigger

Update the cost of a product:

UPDATE products SET prod_cost = 7.00 WHERE prod_id = 1;

Verify the trigger effect:

SELECT * FROM products;

Result

+---------+--------------+-----------+------------+
| prod_id | prod_name    | prod_cost | prod_price |
+---------+--------------+-----------+------------+
|       1 | Basic Widget |       7   |       9.8  |
|       2 | Micro Widget |     0.95  |      1.35  |
|       3 | Mega Widget  |    99.95  |    140     |
+---------+--------------+-----------+------------+

The updateProductPrice trigger automatically adjusted the Basic Widget’s price based on the new cost.