创建触发器的语法如下:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
注意:触发器只能创建在永久表(Permanent Table)上,不能对临时表(Temporary Table)创建触发器。
其中 trigger_time 是触发器的触发时间,可以是 BEFORE 或者 AFTER,BEFORE 的含义指在检查约束前触发,而 AFTER 是在检查约束后触发。
而 trigger_event 就是触发器的触发事件,可以是 INSERT、UPDATE 或者 DELETE。
对同一个表相同触发时间的相同触发事件,只能定义一个触发器。例如,对某个表的不同字段的 AFTER 更新触发器,在使用 Oracle 数据库的时候,可以定义成两个不同的 UPDATE 触发器,更新不同的字段时触发单独的触发器,但是在 MYSQL 数据库中,只能定义成一个触发
器,在触发器中通过判断更新的字段进行对应的处理。 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。
现在触发器还只支持行级触发的,不支持语句级触发。 在样例数据库中,为 film 表创建了 AFTER INSERT 的触发器,具体如下:
DELIMITER $$
CREATE TRIGGER ins_film
AFTER INSERT ON film FOR EACH ROW BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END;
$$
delimiter ;
插入 film 表记录的时候,会向 film_text 表中也插入相应的记录。
mysql> INSERT INTO film VALUES
-> (1001,'ACADEMY DINOSAUR',
-> 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian
Rockies',
-> 2006,1,NULL,6,'0.99',86,'20.99','PG','Deleted Scenes,Behind the
Scenes','2006-02-15 05:03:42');
Query OK, 1 row affected (0.05 sec)
mysql> select * from film_text where film_id=1001 \G
*************************** 1. row ***************************
film_id: 1001
title: ACADEMY DINOSAUR
description: A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in
The Canadian Rockies
1 row in set (0.00 sec)
对于 INSERT INOT…ON DUPLICATE KEY UPDATE…语句来说,触发触发器的顺序可能会造成疑惑。下面对 film 表分别创建了 BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 触发器,然后插入记录,观察触发器的触发情况:
--创建 BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 触发器:
mysql> create table tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_film_bef
-> BEFORE INSERT ON film FOR EACH ROW BEGIN
-> INSERT INTO tri_demo (note) VALUES ('before insert');
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER ins_film_aft
-> AFTER INSERT ON film FOR EACH ROW BEGIN
-> INSERT INTO tri_demo (note) VALUES ('after insert');
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER upd_film_bef
-> BEFORE update ON film FOR EACH ROW BEGIN
-> INSERT INTO tri_demo (note) VALUES ('before update');
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER upd_film_aft
-> AFTER update ON film FOR EACH ROW BEGIN
-> INSERT INTO tri_demo (note) VALUES ('after update');
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)
--插入记录已经存在的情况:
mysql> select film_id,title from film where film_id = 1001;
+---------+------------------+
| film_id | title |
+---------+------------------+
| 1001 | ACADEMY DINOSAUR |
+---------+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO film VALUES
-> (1001,'Only test',
-> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',
-> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')
-> ON DUPLICATE KEY
-> UPDATE title='update record';
Query OK, 2 rows affected (0.05 sec)
mysql> select * from tri_demo;
+----+---------------+
| id | note |
+----+---------------+
| 1 | before insert |
| 2 | before update |
| 3 | after update |
+----+---------------+
3 rows in set (0.00 sec)
--插入新记录的情况:
mysql> delete from tri_demo;
Query OK, 3 rows affected (0.00 sec)
mysql> select film_id,title from film where film_id = 1002;
Empty set (0.00 sec)
mysql> INSERT INTO film VALUES
-> (1002,'Only test',
-> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',
-> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')
-> ON DUPLICATE KEY
-> UPDATE title='update record';
Query OK, 1 row affected (0.05 sec)
mysql>
mysql> select * from tri_demo;
+----+---------------+
| id | note |
+----+---------------+
| 4 | before insert |
| 5 | after insert |
+----+---------------+
2 rows in set (0.00 sec)
从上面的例子可以知道,对于有重复记录,需要进行 UPDATE 操作的 INSERT,触发器触发的顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE;对于没有重复记录的 INSERT,就是简单的执行 INSERT 操作,触发器触发的顺序是 BEFORE INSERT、AFTER INSERT。对于那些实际执行 UPDATE 操作的记录,仍然会执行 BEFORE INSERT 触发器的内容,在设计触发器的时候一定要考虑这种情况,避免错误地触发了触发器。