MySQL Triggers Tutorial

If you’ve worked with any of the commerical databases other than MySQL, you may have already had some experience with triggers.

I’m glad that the MySQL developers finally managed to squeeze in this extremely important feature. I no longer dread trying to defend MySQL from the Microsoft and Oracle developers who have had triggers for so many years.

I’ll use the example off the MySQL website (explained below in detail). I’ll assume you know how to create a table. If not, please read up on that first.

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

DELIMITER ;

First, we need to change the end of line delimiter. We do this because the statements in the trigger need to end in a semicolon, this is explained further below.  This is simple:

DELIMITER |

Next, we specify the action and table for this trigger. In this example, the table is “test1″, and the trigger will execute before any inserts.

CREATE TRIGGER testref BEFORE INSERT ON test1

We specify the following line to make sure if we’re doing a batch update it’ll apply the trigger to each row inserted.

FOR EACH ROW BEGIN

Now we can specify the actions that will occur as the trigger. We use the keywords NEW and OLD to reference the columns in the row. On an INSERT, there are no OLD columns.

INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

We’re done – end the trigger. To finish this single statement (because this entire trigger is really 1 statement, remember we changed our end of statement delimiter) we end it with the |

END;
|

We can now change back the end of line delimiter to a semi-colon.

DELIMITER ;

You will now have a trigger on the table ‘test1′ that occurs on every insert.

A few notes:
As of MySQL 5, triggers on DELETES will not run if you delete via cascading foreign keys.
On an INSERT, you can only use NEW for column values. On a DELETE, you must use OLD.

I will post another tutorial soon on the practical use of triggers in web development. I hope this helps someone.