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 hosting.com 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.
