mysql 触发器_数据库知识总结—(十一)使用触发器&事务处理

本节用到的表结构信息如下：

vendors表

products表

如果我们希望某条语句在事件发生时自动执行，应该如何做呢？例如每当订购一个产品时，都从库存数量中减去订购的数量；无论何时删除一行，都在某个存档表中保留一个副本。

这些例子的共同之处是它们都需要在某个表发生更改时自动处理，这就是触发器，触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句：delete; insert; update;其他语句不支持触发器。

1. 创建触发器

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

（一）唯一的触发器名;（二）触发器关联的表;

（三）触发器响应的活动（delete、update、insert）;（四）触发器何时执行

触发器名必须在每个表中唯一，但不是在每个数据库中唯一。

Create 

Create trigger用来创建名为newproduct的新触发器，触发器可在一个操作发生之前或之后执行，after insert表示将在插入语句成功执行后执行。For each row表示对每个插入行执行，即文本product added将对每个插入行显示一次。

只有表才支持触发器，视图（临时表）不支持。触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。如果BEFORE触发器失败则将不执行请求的操作，并且还将不执行AFTER触发器。

2. 删除触发器

Drop 

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

3. 使用触发器

(1) insert触发器

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

（一）在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

（二）在BEFORE INSERT触发器中，NEW中的值也可以被更新

（三）对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在insert执行之后包含新的自动生成值。

Create 

创建一个neworder的触发器，按照after insert on orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中，触发器从NEW.order_num取得这个值并返回它。此触发器必须按照after insert执行，因为在before insert执行之前，新order_num还没有生成。

Insert 

Orders包含3个列，order_date和cust_id必须给出，order_num自动被返回。通常将before用于数据验证和净化。

(2) delete触发器

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

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

（二）OLD中的值全都是只读的，不能更新。

//

在任意订单被删除前将执行此触发器。它使用一条insert语句将old中的值保存到一个archive_orders的存档表中。使用before delete触发器的优点是：如果由于某种原因订单不能存档，delete本身将被放弃。

(3) update触发器

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

（一）在UPDATE触发器代码中，引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；

（二）在BEFORE UPDATE触发器中，NEW中的值可能也被更新

（三）OLD中的值全都是只读的，不能更新

//

任何数据净化都需要在update语句之前进行，像上例每更新一个行时，new.vend_state中的值（将用来更新表行的值）都用upper(vend_state)替换。

4.触发器的一些重要知识

（一）创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

（二）应该用触发器来保证数据的一致性。在触发器中执行这种类型的处理的优点是它总是进行这种处理，与客户机应用无关。

（三）触发器的一种非常有意义的使用是创建审计跟踪。使用触发器把更改记录到另一个表非常容易。

（四）MySQL触发器中不支持CALL语句。即不能从触发器内调用存储过程，所需的存储过程代码需要复制到触发器内。

二． 管理事务处理

1. 事务处理

MySQL支持几种基本的数据库引擎，但是并非所有引擎都支持明确的事务处理管理。MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。关系数据库设计把数据存储在多个表中，使数据更容易操纵、维护和重用。在某种程度上说，设计良好的数据库模式都是关联的。

举orders和orderitems为例，订单存储在orders和orderitems两个表中：orders存储实际的订单，而orderitems存储订购的各项物品。这两个表使用称为主键的唯一ID互相关联，这两个表又与包含客户和产品信息的其他表相关联。我们添加订单的流程如下：首先检查数据库中是否存在相应的客户，若不存在添加客户，然后检索客户的ID；添加一行到orders表中，将其与客户ID关联；再检索orders表中赋予新订单ID，对于订购的每个物品在orderitems表中添加一行，通过检索出来的ID把它与orders表关联。

那么如果由于某种原因数据库产生故障，这些原因可能是超出磁盘空间，安全限制，表锁等。这些故障阻止了这个流程的完成，那么数据库中数据会是什么情况？

如果故障发生在orders行添加之后，orderitems行添加之前。那么情况是数据库中有一个空订单。又如果系统在添加orderitems行之中出现故障，结果是数据库中存在不完整的订单。

如何解决这种问题？这就需要使用事务处理了，事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退以恢复数据库到某个已知且安全的状态。

因此如果在添加行到orders表时出现故障时，则回退；如果在添加新行到orderitems时出现故障，

在使用事务和事务处理时，有几个关键词汇反复出现：

（一）事务（transaction）：指一组SQL语句。

（二）回退（rollback）：指撤销指定SQL语句的过程。

（三）提交（commit）：指将未存储的SQL语句结果写入数据库表。

（四）保留点（savepoint）：指事务处理中设置的临时占位符，可以对它发布回退。

2. 控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。使用下面的语句来标识事务的开始：

Start 

（1） 使用ROLLBACK：使用rollback命令来退回MySQL语句

Select 

该例显示orders表的内容开始，首先显示该订单表中有个订单号为20010的订单，然后开始一个事务处理，用一条delete删除订单号为20010的行，接下来用select验证确实已经删除了这条订单。这时用一条rollback语句回退start transaction之后的所有语句，最后一条select语句显示了订单号为20010行依然存在于订单表中。显然ROLLBACK只能在一个事务处理内使用。

事务处理用来管理INSERT、UPDATE和DELETE语句，但不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果一定要回退，它们将不会被撤销。

（2） 使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。即隐含提交，即提交操作是自动进行的。但是在事务处理块中，提交不会隐含的进行。为进行明确的提交，使用commit语句。

Start 

在这个例子中从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderitems，所以事务处理块来保证订单不被部分删除。Commit语句仅在不出错时写出更改，如果第一条delete起作用，但第二条失败，则delete不会提交。

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

（3） 使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可使用SAVEPOINT语句：

Savepoint 

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。

//回退到delete1。
rollback to delete1;

保留点在事务处理完成自动释放，也可以用release savepoint明确地释放保留点。

（4） 更改默认的提交行为

默认的MySQL行为是自动提交所有更改。换句话说，任何时候执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

set 

autocommit标志决定是否自动提交更改，不管有没有commit语句，当autocommit为0时MySQL不自动提交更改，autocommit标志是针对每个连接而不是服务器的。

总结一下：本节介绍了触发器以及为什么要使用触发器，并列举了几个用于insert，delete，update操作的触发器例子；又介绍了事务处理是必须完整执行的SQL语句块。需要熟练掌握。

参考书籍：《MySQL必知必会》