首页 > 代码库 > MYSQL触发器
MYSQL触发器
一、触发器CREATE语法：
【定义】
触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
触发程序与命名tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联。
【语法】
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tb1_name FOR EACH ROW ttigger_stmt
【语法说明】
trigger_name：用户自定义触发器名称。
trigger_time：触发器动作时间。可以使BEFORE或AFTER，可以指明触发器是在激活它的语句之前或之后触发。
trigger_event：指定触发器被激活的语句类型。类型如下：
INSERT：将新行数据插入表时激活触发程序，例如通过INSERT、LOAD DATA和REPLACE语句。
UPDATE：更改某一行数据时激活触发器，例如通过UPDATE语句。
DELETE：删除某一行数据时激活触发器，例如通过DELETE和REPLACE语句。
tb1_name：触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
ttigger_stmt：当触发器被激活时所要执行的操作语句。如果是多条语句，可以使用BEGIN...END复合语句结构。
【示例】
CREATE TABLE tb_test1(al INT);
CREATE TABLE tb_test2(a2 INT);
CREATE TABLE tb_test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE tb_test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY
b4 INT DEFAULT 0
);
DELIMITER $
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 = NEW.a1;
END$
DELIMITER;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
如果将下述值插入表test1，如下所示：
mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
那么4个表中的数据如下：
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
使用别名OLD和NEW，能够引用与触发器相关表中的列。OLD.col_name在更新或删除之前，引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。
激活触发器时，对于触发器引用所有的OLD和NEW列，需要具有SELECT权限，对于作为SET赋值目标的所有NEW列，需要UPDATE权限。
注*：CREATE TRIGGER语句需要SUPER权限。
二：DROP TRIGGER语法
DROP TRIGGER [schema_name] trigger_name
删除触发器。方案名称（schema_name）可选。如果省略schema_name，将从当前方案中舍弃触发器。
注*：CREATE TRIGGER语句需要SUPER权限。
三：其他
触发器不能调用将数据返回客户端的存储程序、也不能使用采用CALL语句的动态SQL（允许存储程序通过参数将数据返回触发程序）。
触发器不能使用以显示或隐试方式开始或节数事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。
使用OLD和NEW关键字，能够访问受触发程序影响的行中的列（OLD和NEW不区分大小写）。在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。
在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列。
用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值。这意味着，你可以使用触发程序来更改将要插入到新行中的值，或用于更新行的值。
在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，不是实际插入新记录时将自动生成的序列号。
在触发程序的执行过程中，MySQL处理错误的方式如下：
· 如果BEFORE触发程序失败，不执行相应行上的操作。
· 仅当BEFORE触发程序（如果有的话）和行操作均已成功执行，才执行AFTER触发程序。
· 如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。
· 对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，
失败之前所作的任何更改依然有效。
MYSQL触发器