MySQL Triggers: What They Are and How to Use Them

What is a trigger?

A database is an organised store of information. MySQL is a popular database program. Data sits in tables, like sheets in a spreadsheet. A trigger is a saved action that fires when something happens to a table. It is like a motion light. You do nothing, and the light turns on when someone walks by.

When does a trigger fire?

You pick two things:

• Event: INSERT (new row), UPDATE (changed row) or DELETE (removed row).
• Time: BEFORE or AFTER the event.

Inside a trigger, NEW means the new values and OLD means the old values.

Example: save a log of price changes

Say you have a table called products and a table called price_log. You want each price change saved automatically.

1. Log in to your control panel and open phpMyAdmin, a web tool for databases.
2. Click your database on the left.
3. Click the SQL tab.
4. Paste the code below.
5. Click Go.

CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
INSERT INTO price_log (product_id, old_price, new_price)
VALUES (OLD.id, OLD.price, NEW.price);

This makes a trigger. Each time a product row is updated, one row is added to price_log with the old and new price.

If your trigger has many lines, phpMyAdmin may need you to change the delimiter box under the SQL area. A delimiter is the mark that ends a command. Set it to $$ and use BEGIN ... END$$.

See your triggers

SHOW TRIGGERS;

This lists the triggers in the current database.

Remove a trigger

DROP TRIGGER log_price_change;

This deletes the trigger. Your data stays, but the automatic action stops.

Warning: A wrong trigger can change data by mistake. Back up your database before you test. Try it on a copy first.

Good to know

• Triggers can slow down heavy tables, so keep them short.
• Some shared hosting plans limit trigger rights. If you get a permission error, ask Hostvento support.
• Name triggers clearly, so you know what they do later.

Quick recap

• A trigger runs by itself when a row is inserted, updated or deleted.
• Choose BEFORE or AFTER, and the event.
• NEW and OLD hold the row values.
• Create with CREATE TRIGGER, remove with DROP TRIGGER.
• Back up first.