Stats

289 Contributors: 6 Thursday, November 17, 2016
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap

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 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

Related Examples