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 CREATE fail
• on a heavy load, others operations can occurs between the DROP and the CREATE, use a LOCK TABLES myTable WRITE; first to avoid data inconsistency and UNLOCK TABLES; after the CREATE to release the table