《MySQL必知必会》学习笔记——使用触发器、管理事务处理

第25章 使用触发器

1. 触发器

在本章之前的MySQL语句都是在需要时被执行，存储过程也是如此。但是，有时我们想要某条（某些）语句在事件发生时自动执行，这就需要用到触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

• DELETE;
• INSERT;
• UPDATE。

即，表有更改时可以使用触发器，其他语句不支持使用触发器。

2. 创建触发器

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

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

下面是一个例子：

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

这里创建了一个名为newproduct的触发器，在INSERT语句成功执行之后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

只有表才支持触发器，视图不支持（临时表也不支持）。

注意：每个表每个事件每次只允许一个触发器。

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

3. 删除触发器

删除触发器可使用DROP TRIGGER，如下所示：

DROP TRIGGER newproduct;

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

4. 使用触发器

4.1 INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行：

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

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

上面的这个触发器在每次对orders表插入新行时，返回新的订单号。

4.2 DELETE触发器

DELETE触发器在INSERT语句执行之前或之后执行：

• 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行；
• OLD中的值全都是只读的，不能更新。

下面的例子演示使用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;

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

4.3 UPDATE触发器

UPDATE触发器在INSERT语句执行之前或之后执行：

• 在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);

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

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

第26章 管理事务处理

1. 事务处理

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。下面是关于事务处理需要知道的几个术语：

• 事务（transaction）：指一组MySQL语句；
• 回退（rollback）：指撤销指定SQL语句的过程；
• 提交（commit）：指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）：指事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）。

2. 控制事务处理

管理事务处理的关键在于将SQL语句分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

MySQL使用下面的语句来标识事务的开始：

START TRANSACTION;

2.1 使用ROLLBACK

MySQL使用ROLLBACK命令来回退MySQL语句：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT、CREATE和DROP操作。

2.2 使用COMMIT

在事务处理模块中，提交不会自动进行。为进行明确的提交，使用COMMIT语句，如下所示：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

最后的COMMIT保证仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

当COMMIT或ROLLBACK语句执行后，事务会自动关闭。

2.3 使用保留点

为了支持回退部分事务处理，必须能在事务处理模块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

可以使用SAVEPOINT创建占位符：

SAVEPOINT delete1;

每个保留点都取标识它的唯一名字。回退可如下进行：

ROLLBACK TO delete1;

可以在MySQL代码中设置任意多的保留点，越多越好。保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。

2.4 更改默认的提交行为

默认的MySQL行为是自动提交所有更改，为指示MySQL不自动提交更改，需要使用以下语句：

SET autocommit = 0;