sql必知必会学习记录（二十）- 触发器

亦凉 2022-02-17 13:42 276阅读 0赞

1.  触发器介绍

MySQL语句在需要时被执行，存储过程也是如此。触发器是指某条语句（或某些语句）在事件发生时自动执行。

触发器适用于以下场景：

*  每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
 *  每当订购一个产品时，都从库存数量中减去订购的数量；
 *  无论何时删除一行，都在某个存档表中保留一个副本。

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

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

1.  创建触发器

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

*  唯一的触发器名；
 *  触发器关联的表；
 *  触发器应该响应的活动（DELETE、INSERT或UPDATE）；
 *  触发器何时执行（处理之前或之后）。

触发器用CREATE TRIGGER语句创建。下面是一个简单的例子：

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
    可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

> 触发器失败如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）

1.  删除触发器

DROP TRIGGER newproduct；

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

1.  使用触发器

*  insert 触发器

> 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
> 
> 在BEFOREINSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
> 
> 对于AUTO INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num into @ee;
    
    INSERT INTO orders(order_date,cust_id) VALUES (Now(),10001);
    select @ee;

*  delete触发器

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

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

2）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的存档表中

*  update触发器

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

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
    在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
    OLD中的值全都是只读的，不能更新
    
    
    CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors 
    FOR EACH ROW SET NEW.vend_state =Upper(NEW. vend_state);