数据库学习笔记——21 使用触发器

1 触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

• 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；

• 每当订购一个产品时，都从库存数量中减去订购的数量；

• 无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）：

• DELETE ；

• INSERT ；

• UPDATE 。

其他MySQL语句不支持触发器。

2 创建和使用触发器

在创建触发器时，需要给出4条信息：

• 唯一的触发器名；

• 触发器关联的表；

• 触发器应该响应的活动（ DELETE 、 INSERT 或 UPDATE ）；

• 触发器何时执行（处理之前或之后）。

保持每个数据库的触发器名唯一
仅支持表 ——只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

触发器用 CREATE TRIGGER 语句创建：

（1）创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;
例如：

CREATE TRIGGER del_vendor AFTER DELETE ON vendors FOR EACH ROWINSERT INTO sqllogs VALUES(NULL,old.vend_name,CURRENT_USER,CONCAT(CURRENT_DATE,'  ',CURRENT_TIME));

例子中sqllogs表的建表语句：

CREATE TABLE sqllogs(
thread_id INT NOT NULL auto_increment,
thread_name CHAR(50),
oper_user CHAR(50) NOT NULL,
oper_date datetime NOT NULL, 
PRIMARY KEY (thread_id)
)ENGINE=INNODB;

DELETE 触发器在 DELETE 语句执行之前或之后执行。需要知道以下两点：

• 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；

• OLD 中的值全都是只读的，不能更新。

（2）创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件ON 表名 FOR EACH ROWBEGIN
        执行语句列表END;

下面是一个简单的例子：

CREATE TRIGGER customer_state BEFORE UPDATE ON customers FOR EACH ROWBEGINSET new.cust_contact = LOWER(new.cust_state);SET new.cust_state = UPPER(new.cust_state);END;

UPDATE 触发器在 UPDATE 语句执行之前或之后执行。需要知道以下几点：

• 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；

• 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；

• OLD 中的值全都是只读的，不能更新。

验证：

UPDATE customers SET cust_state='jn' WHERE cust_id='1000000008';SELECT * FROM customers;

再看一个INSERT的例子：

CREATE TRIGGER cust_vend_state BEFORE INSERT ON vendors FOR EACH ROWBEGINSET new.vend_state=UPPER(new.vend_state);SET new.vend_zip='250001';END;

INSERT 触发器在 INSERT 语句执行之前或之后执行。需要知道以下几点：

• 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；

• 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改被插入的值）；

• 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT执行之后包含新的自动生成值。

INSERT INTO vendors VALUES('JNAN03','王美丽','齐鲁软件园','JiNan','jn','25000','CN') ;SELECT * FROM  vendors;

3 删除触发器

为了删除一个触发器，可
使用 DROP TRIGGER 语句，如下所示：

DROP TRIGGER cust_vend_state;
或者DROP TRIGGER IF EXISTS del_vendor;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

4 关于触发器的进一步介绍

我们再介绍一些使用触发器时需要记住的重点。

• 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。

• 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果 INSERT 、 UPDATE 或 DELETE 语句能够执行，则相关的触发器也能执行。

• 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。

• 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

• 遗憾的是，MySQL触发器中不支持 CALL 语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

作者：Love零O
链接：https://www.jianshu.com/p/f18a72c32b10