MySQL 笔记8 触发器

绝地灬酷狼 2023-07-18 15:30 24阅读 0赞

参考：《MySQL必知必会》Ben Forta著，第25章 使用触发器

### 触发器 ###

考虑如下场景，  
\- 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；  
\- 每当订购一个产品时，都从库存数量中减去订购的数量；  
\- 无论何时删除一行，都在某个存档表中保留一个副本。  
上述例子的共同之处 => 需要在某个表发生更改时自动处理 => 触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：  
DELETE  
INSERT  
UPDATE  
其它MySQL语句不支持触发器。

### 创建触发器 ###

创建触发器时，需要给出4条信息：  
\- 唯一的触发器名  
\- 触发器关联的表  
\- 触发器应该响应的活动（DELETE、INSERT或UPDATE）  
\- 触发器何时执行（处理之前或之后）  
注意：  
1.只有表才支持触发器，视图、临时表不支持。  
2.触发器按每个表每个事件每次地定义 =>   
   每个表每个事件每次只允许一个触发器（每个表最多支持6个触发器）  
   单一触发器不能与多个事件或多个表关联

【例】每次往表products插入数据，执行SELECT 'Product added'

mysql> CREATE TRIGGER newproduct AFTER INSERT ON products
        -> FOR EACH ROW SELECT 'Product added';
    ERROR 1415 (0A000): Not allowed to return a result set from a trigger
    mysql>

报错原因：MYSQL5以后，不允许触发器返回任何结果  
解决方法：使用 INTO @变量名，将结果赋值到变量中，再用SELECT调用

mysql> CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @prompt;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> SELECT @prompt;
    +---------+
    | @prompt |
    +---------+
    | NULL    |
    +---------+
    1 row in set (0.00 sec)
    
    mysql>

通过插入数据触发触发器，

### 删除触发器 ###

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

### 使用触发器 ###

**1. INSERT 触发器**

\- 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；  
\- 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；  
\- 通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）,此项也适用于UPDATE触发器。

【例】插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order\_num中，触发器从NEW.order\_num取得这个值  
说明：此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order\_num还没有生成。

mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
        -> FOR EACH ROW SELECT NEW.order_num INTO @insertmp;
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> INSERT INTO orders(order_date,cust_id)
        -> VALUES(Now(),10001);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> SELECT @insertmp;
    +-----------+
    | @insertmp |
    +-----------+
    |     20010 |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql> INSERT INTO orders(order_date,cust_id) VALUES(Now(),10001);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> SELECT @insertmp;
    +-----------+
    | @insertmp |
    +-----------+
    |     20011 |
    +-----------+
    1 row in set (0.00 sec)
    
    mysql>

**2. DELETE 触发器**

\- 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；  
\- OLD中的值全都是只读的，不能更新。  
\- 使用BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能存档，DELETE本身将被放弃。

【例】orders上创建DELETE触发器，将OLD中的值（要删除的订单）保存到存档表archive\_orders

首先要新建存档表archive\_orders，与表orders结构一样

mysql> SHOW CREATE TABLE orders\G;
    *************************** 1. row ***************************
           Table: orders
    Create Table: CREATE TABLE `orders` (
      `order_num` int(11) NOT NULL AUTO_INCREMENT,
      `order_date` datetime NOT NULL,
      `cust_id` int(11) NOT NULL,
      PRIMARY KEY (`order_num`),
      KEY `fk_orders_customers` (`cust_id`),
      CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=20012 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql>
    mysql> CREATE TABLE `archive_orders` (
        ->   `order_num` int(11) NOT NULL AUTO_INCREMENT,
        ->   `order_date` datetime NOT NULL,
        ->   `cust_id` int(11) NOT NULL,
        ->   PRIMARY KEY (`order_num`),
        ->   KEY `fk_orders_customers` (`cust_id`),
        ->   CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
        -> ) ENGINE=InnoDB AUTO_INCREMENT=20012 DEFAULT CHARSET=latin1;
    ERROR 1022 (23000): Can't write; duplicate key in table 'archive_orders'

外键冲突报错解决：换一个外键名，如下

mysql> CREATE TABLE `archive_orders` (
        ->   `order_num` int(11) NOT NULL AUTO_INCREMENT,
        ->   `order_date` datetime NOT NULL,
        ->   `cust_id` int(11) NOT NULL,
        ->   PRIMARY KEY (`order_num`),
        ->   KEY `fk_ordersbk_customers` (`cust_id`),
        ->   CONSTRAINT `fk_ordersbk_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
        -> ) ENGINE=InnoDB AUTO_INCREMENT=20012 DEFAULT CHARSET=latin1;
    Query OK, 0 rows affected (0.22 sec)
    
    mysql> select * from archive_orders;
    Empty set (0.00 sec)
    
    mysql>

然后，创建DELETE触发器

mysql> DELIMITER //
    mysql> 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 //
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> DELIMITER ;

验证DELETE触发器

mysql> DELETE FROM orders WHERE order_num=20011;
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from archive_orders;
    +-----------+---------------------+---------+
    | order_num | order_date          | cust_id |
    +-----------+---------------------+---------+
    |     20011 | 2020-03-29 15:15:56 |   10001 |
    +-----------+---------------------+---------+
    1 row in set (0.00 sec)
    
    mysql>
    mysql> DELETE FROM orders WHERE order_num=20010;
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from archive_orders;
    +-----------+---------------------+---------+
    | order_num | order_date          | cust_id |
    +-----------+---------------------+---------+
    |     20010 | 2020-03-29 15:15:18 |   10001 |
    |     20011 | 2020-03-29 15:15:56 |   10001 |
    +-----------+---------------------+---------+
    2 rows in set (0.00 sec)
    
    mysql>

**3. UPDATE 触发器**

\- 在UPDATE触发器代码中，引用虚拟表OLD访问UPDATE语句前的值，引用虚拟表NEW访问更新的值；  
\- 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；  
\- OLD中的值全都是只读的，不能更新。

【例】保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）

mysql> CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
        -> FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> UPDATE vendors SET vend_state='ca' WHERE vend_id=1004;
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM vendors WHERE vend_id=1004;;
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    | vend_id | vend_name    | vend_address    | vend_city | vend_state | vend_zip | vend_country |
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    |    1004 | Furball Inc. | 1000 5th Avenue | New York  | CA         | 11111    | USA          |
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql> UPDATE vendors SET vend_state='ny' WHERE vend_id=1004;
    Query OK, 1 row affected (0.02 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT * FROM vendors WHERE vend_id=1004;;
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    | vend_id | vend_name    | vend_address    | vend_city | vend_state | vend_zip | vend_country |
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    |    1004 | Furball Inc. | 1000 5th Avenue | New York  | NY         | 11111    | USA          |
    +---------+--------------+-----------------+-----------+------------+----------+--------------+
    1 row in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql>