什么是触发器?
如果想要某条语句(或某些语句)在事件发生时自动执行,这里就需要使用触发器。
触发器:是MySQL响应以下任意语句而自动执行的一条(或多条)MySQL语句。
- DELETE
- INSERT
- UPDATE
其他MySQL语句不支持触发器。
创建触发器
在创建触发器时,需要给出4条信息:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(DELETE、INSERT或UPDATE);
- 触发器何时执行(处理之前或之后)。
触发器必须在每个表中唯一,但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的。但是,最好保持每个数据库的触发器名唯一。
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
使用INSERT语句添加一行或多行到products中,对每个成功的插入,都显示一条Product added消息。
仅支持表:只有表才支持触发器,视图不支持(临时表也不支持)。
每个表每个事件每次只允许一个触发器。因此,每个表最多支持6个触发器(每条INSERT、UPDATE、DELETE之前和之后)。单一触发器不能与多个事件或多个表关联。
触发器失败:如果BEFORE触发器失败,则MySQL将不执行请求的操作。此外,如果BEFORE触发器或语句本身失败,MySQL将不执行AFTER触发器(如果有的话)。
删除触发器
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器,必须先删除它,然后再重新创建。
触发器的类型
INSERT触发器
- 在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问问被插入的行;
- 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
- 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。
# 这条语句将输出插入orders表中每一行的order_num(这个自增值)
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
此触发器必须按照AFTER INSERT执行,因为在BEFORE INSERT语句执行之前,新的order_num还没有生成。
通常,BEFORE用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据)。
DELETE触发器
- 在DELETE触发器代码内,可引用一个名为OLD的虚拟表,访问被删除的行;
- OLD中的值全都是只读的,不能更新。
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO achive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
使用BEGIN END块的好处是触发器能容纳多条SQL语句。
UPDATE触发器
- 在UPDATE触发器代码中,可引用一个名为OLD的虚拟表访问以前的值,引用一个名为NEW的虚拟表访问新更新的值;
- 在BEFORE UPDATE出发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
- OLD中的值全都是只读的,不能更新。
CREATE TRIGGER updatevendor BRFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
任何数据净化都需要在UPDATE语句之前进行。
关于触发器的进一步说明
- 与其他DBMS相比,MySQL 5中支持的触发器相当初级;
- 创建触发器可能可能需要特殊的安全访问权限,到那时,触发器的执行是自动的。如果INSERT、UPDATE、DELETE语句能够执行,则相关的触发器也能执行;
- 应该用触发器来保证数据的一致性。在触发器中执行这种类型的处理的有点是它总是进行这种处理,而且是透明地进行,与客户机无关;
- 触发器的一种非常有意义的使用时创建审计跟踪。使用触发器,把更改记录到另一个表很容易;
- MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。