# Using MySQL triggers

> Create MySQL triggers that run automatically before or after data is inserted, updated or deleted, with a worked example.

Source: https://helpcenter.ruachost.com/developers/databases/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.

## **More Information**

- [MySQL Triggers Documentation](https://dev.mysql.com/doc/refman/8.4/en/triggers.html)
- [CREATE TRIGGER Statement](https://dev.mysql.com/doc/refman/8.4/en/create-trigger.html)