MySQL触发器

MySQL触发器

1、 触发器定义:

触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 ——百度百科

2、 创建触发器的四个要素:

(1) 监视地点(table)

(2) 监视事件(insert|update|delete)

(3) 触发时间(before|after)

(4) 触发事件(insert|update|delete)

备注:保持每个数据库的触发器名唯一。

3、 创建触发器

simple example:

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT "Product added";

分析:CREATE TRIGGER用来创建名为newproduct的触发器。触发器可在一个操作发生之前或之后执行,这里给出了AFTER INSERT,所以此触发器将在INSERT语句成功后执行。FOR EACH ROW(代码对每个插入行执行)。在这个例子中,文本Product added将对每个插入的行显示一次。

4、查看和删除已有的触发器

  • 查看已有触发器: SHOW TRIGGERS;

  • 删除已有触发器: DROP TRIGGER trigger_name;

5、使用触发器

5.1 INSERT触发器

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

  • 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);

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

    example:

     CREATE TRIGGER neworder AFTER INSERT ON orders
     FOR EACH ROW SELECT NEW.order_num INTO @num;

    分析:在插入一个新订单到orders表时,MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。对于orders的每次插入使用这个触发器将总是返回新的订单号。

    测试:

    INSERT INTO orders(order_date,cust_id) VALUES(Now(),10001);

    1580054142579

    注:从MySQL5以后不支持触发器返回结果集

5.2 DELETE触发器

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

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

    example(使用OLD保存将要被删除的行到一个存档表中):

     CREATE TRIGGER deleteorder BEFORE DELETE ON orders
     FOR EACH ROW
     BEGIN
         INSERT INTO archive_orders(order_num,order_date,cust_id)
         VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
     END;

    分析:在任意订单被删除执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_orders的存档中(为实际使用这个例子,你需要用与orders表相同的列创建一个archive_orders的表)。

5.3 UPDATE触发器

  • 在DELETE触发器代码中,可引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;

  • 在BEFORE DELETE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);

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

example(保证州名缩写总是大写,不管UPDATE语句中给出的事大写还是小写):

 CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
 FOR EACH ROW
 SET NEW.vend_state = Upper(NEW.vend_state);

分析:显然,任何数据净化都需要在UPDATE语句之前进行。每次更新一行是,NEW。vend_state中的值(将来用来更新行的值)都用Upper(NEW.vend_state)替换。