MySQL Pocket Reference, 2nd Edition

Triggers

Like a stored procedure, a trigger is processing logic stored in the database. Where a stored procedure executes in response to a specific application request, a trigger executes whenever a particular database event occurs. For any given event, you can define a trigger to execute BEFORE or AFTER the event. The events on which you can build a trigger are:

INSERT

The trigger will execute whenever a row is inserted into the database.

UPDATE

The trigger will execute whenever a row is updated in the database.

DELETE

The trigger will execute whenever a row is deleted from the database.

Trigger definition works much like stored procedure definition in that your logic can be made up of compound SQL nestled inside a BEGIN/END block. The main difference is that your logic is also bounded by a FOR EACH ROW section:

DELIMITER //
CREATE TRIGGER zap_addresses AFTER DELETE ON web_site
FOR EACH ROW BEGIN
DELETE FROM web_address WHERE web_site_id = OLD.web_site_id;
END;

The special identifiers NEW and OLD reference the new and old row values, respectively.