MySQL中的触发器

1.定义：

　　触发器和存储过程相似，都是嵌入到 MySQL 中的一段程序。触发器是由事件来触发某个操作。当数据库执行这些事件时，就会激活触发器来执行相应的操作。这些事件称为触发条件，在MySQL中，有UPDATE，INSERT，和DELETE。

在MySQL中，目前只有UPDATE，INSERT，和DELETE这三种操作，才会触发触发器，其他操作都不支持触发器。这和Oracle中的触发器有很大的区别，要注意区分。
在MySQL5.7.2版本之前，触发器的每个表每次事件，都只允许一个触发器。因此，每个表最多值允许有6个触发器。如果一个表有两个事件，INSERT和DELETE，那么就要分别创建一个触发器。从MySQL版本5.7.2+开始，可以为同一触发事件和操作时间定义多个触发器。
只有表才支持触发器，视图和临时表不支持。

2.语法：

CREATE TRIGGER TRIGGER_NAME
BEFORE/AFTER 
UPDATE/INSERT/DELETE ON TABLE_NAME 
FOR EACH ROW
#触发器主体
[BEGIN]
SQL操作
[END];

创建触发器：和创建存储过程是一样，用的都是CREATE 语句，但要注意，这里没有 OR REPLACE。
TRIGGER_NAME：触发器名称。在MySQL中，要保证每个表中的触发器是唯一的，每一个数据库内的触发器可以不唯一，这就说明了一个数据库内的两个表可以有相同的触发器。这种行为在其他的数据库内是不被允许的。但是为了格式上的规范，还是要尽可能的保证TRIGGER_NAME的唯一性。
BEFORE/AFTER：触发器被触发的时刻。你如果希望触发器在触发事件之前发生，就使用BEFORE，如果希望在触发事件之后发生，就使用AFTER。
UPDATE/INSERT/DELETE：触发事件，触发条件。在MySQL中，只有这三种DDL操作支持触发器，其他操作都不被允许。
FOR EACH ROW：受触发事件影响的每一行都要激活触发器的动作。这里指的是行级触发，在MySQL里，FOE EACH ROW不可省略，不支持语句级触发。
触发器主体：既可以是单独的一条SQL语句，也可以是由BEGIN...END组成的复杂结构块。

# 创建一个触发器T1，在对表customer做插入操作时，就会触发T1，之后会在日志表note中插入一条数据
CREATE TRIGGER T1
AFTER 
INSERT ON customer
FOR EACH ROW 
# 触发器主体
INSERT INTO note(日期,目标,操作) VALUES(NOW(),'customer','insert');

3.删除触发器

　　和删除表和存储过程类似，都是使用DROP语句来删除触发器。

#删除触发器T1
DROP TRIGGER IF EXISTS T1;

触发器不能够更新和修改，想要修改一个触发器的内容，只能先将触发器删除，在创建一个新的触发器。

4.查询触发器

#在已知数据库内，查询触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = '触发器名'
#查询所有的触发器
SELECT * FROM information_schema.TRIGGERS（WHERE TRIGGER_SCHEMA = '数据库名'）

5.触发器类型OLD和NEW的使用

　　触发器在DDL操作之前或者之后触发，总会对目标表做出一定的改动，有时我们需要知道目标表改变前后的值，这就用到了OLD和NEW的用法。

顾名思义，OLD就是改变之前的值，是UPDATE之前或者是要被DELETE或者已经被DELETE的值。

　　而NEW,则是UPDATE之后的新值，即将INSERT或者已经INSERT的值。

　　具体实例：

#创建customer表
CREATE TABLE `customer` 
(`CUST_ID` int(11) NOT NULL AUTO_INCREMENT,
`CUST_NAME` varchar(10) NOT NULL,
`CUST_TEL` varchar(10) DEFAULT NULL,
PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#向表中插入数据

#创建一个update触发器
CREATE TRIGGER T2
AFTER 
UPDATE ON customer
FOR EACH ROW 
#old和new的使用方法：old.columnname/new.columnname（列名）
#将更新前后的值，赋值给两个变量
SELECT OLD.CUST_ID,NEW.CUST_ID INTO @OLD_ID,@NEW_ID;

#触发触发器T2
UPDATE customer SET CUST_ID = '10000' WHERE CUST_NAME = 'AAA';

#查询OLD和NEW
SELECT @OLD_ID,@NEW_ID;

6.触发器的限制

　　MySQL触发器覆盖标准SQL定义中的所有功能，但在应用程序使用中，它们也存在一些限制：

单一的触发器不可以与多个表或者多个事件相关联，如果想要对一个表进行多个触发操作，可以多创建几个触发器。
MySQL触发器中不能使用SHOW，LOAD TABLE，LOAD DATA，BACKUP DATABASE， RESTOREFLUSH，RETURN语句。
不能使用隐式或显式提交或回滚的语句，例如COMMIT，ROLLBACK，START TRANSACTION， LOCK / UNLOCK TABLES，ALTER，CREATE，DROP，RENAME。
不能使用PREPARE和EXECUTE之类的准备语句。
不能出现动态SQL。

MySQL中的触发器

文章评论