MySQL TRIGGERS
Syntax
- CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body
- trigger_time: { BEFORE | AFTER }
- trigger_event: { INSERT | UPDATE | DELETE }
- trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
Remarks
Two points need to draw your attention if you already use triggers on others DB :
FOR EACH ROW
FOR EACH ROW is a mandatory part of the syntax
You can't make a statement trigger (once by query) like Oracle do. It's more a performance related issue than a real missing feature
CREATE OR REPLACE TRIGGER
The CREATE OR REPLACE is not supported by MySQL
MySQL don't allow this syntax, you have instead to use the following :
DELIMITER $$
DROP TRIGGER IF EXISTS myTrigger;
$$
CREATE TRIGGER myTrigger
-- ...
$$
DELIMITER ;
Be careful, this is not an atomic transaction :
- you'll loose the old trigger if the
CREATEfail - on a heavy load, others operations can occurs between the
DROPand theCREATE, use aLOCK TABLES myTable WRITE;first to avoid data inconsistency andUNLOCK TABLES;after theCREATEto release the table
