Derek Neely

I know I’m probably in the past on this, but I recently discovered MySQL triggers and the great power and convenience behind them. Below, I’m trying to capture some basic examples of how to use them.

Triggers are actions that are performed when another action occurs. Specifically on any INSERT, UPDATE, or DELETE. One brief example would be, when you delete a row/record from one table, you can automatically delete any reference to that record in other tables.

First create two (2) tables:

CREATE TABLE a (a_id int(11) NOT NULL auto_increment, a_value varchar(100) NOT NULL, PRIMARY KEY (a_id))

CREATE TABLE b (b_id int(11) NOT NULL auto_increment, b_value varchar(100) NOT NULL, a_id int(11) NOT NULL, PRIMARY KEY (b_id))

We will use/assume the relationship of 1-to-many (a-to-b). In the b-table we have a column ‘a_id’ which will be a logical foreign key back to the a-table. (I know you can specify foreign keys on the tables but for this tutorial/example that isn’t exactly necessary.)

Now, on to the triggers…

There are 6 trigger events associated as BEFORE or AFTER of INSERT, UPDATE, or DELETE:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

The general syntax for creating a trigger is:

delimiter //
CREATE TRIGGER
[trigger_name]
[BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON
[table_action_perfomed_on]
FOR EACH ROW BEGIN
[sql_statement_of_what_to_do];
END;
//

DELETES: This trigger will run upon a row being deleted.

delimiter //
CREATE TRIGGER
a_table_on_delete
BEFORE DELETE ON
a
FOR EACH ROW BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END;
//

Once you have created your trigger, you can view your triggers using SHOW TRIGGERS:

mysql> show triggers\G;
*************************** 1. row***************************
Trigger: a_table_on_delete
Event: DELETE
Table: a
Statement: BEGIN
DELETE FROM b WHERE a_id=OLD.a_id;
END
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost

Also, be sure to reset your delimter for standard SQL statements.

mysql> delimiter ;