一、触发器

1、概念相关

如果想要某条语句在事件发生时自动执行那么就要用到触发器，触发器是数据库表的属性,是一个特殊的存储过程，但是触发器不需要CALL语句调用，也不需要手动启动。它由事件触发，事件包括INSERT，UPDATE和DELETE语句，当表中出现这些特定事件时，将激活该对象 。
除此之外的其他MySQL语句不支持触发器。

2、创建触发器

触发器创建的四个要素

• 唯一的触发器名称
• 触发器关联的表
• 触发器应该响应的活动
• 触发器何时执行

触发器创建使用CREATE TRIGGER语句

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

此时报错：Not allowed to return a result set from a trigger
解决方法：加上 into @arg，因为从MySQL5以后不支持触发器返回结果集

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

CREATE TRIGGER用来创建名为newproduct的触发器，触发器在一个操作发生之前或之后执行，这列给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行，这个触发器还指定FOR EACH ROW，因此代码对插入的每行都执行。该例中，文本Product added将对每个插入的行显示一次。

触发器只在操作对象为表时才能使用，视图和临时表都不支持。
触发器按照每个表每个事件每次地定义，每个表每个时间每次只允许一个触发器。因此每个表最多支持6个触发器(每条INSERT、UPDATE、DELETE语句的之前和之后)。单一触发器不能与多个事件或多个表关联，所以如果需要对I一个INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

3、删除触发器

DROP TRIGGER newproduct;

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

4、使用触发器

现在来看所支持的每种触发器类型和它们之间的差别。

4.1、INSERT触发器

在INSERT触发器代码内，可以引用名为NEW的虚拟表，访问被插入的行，且该表在BEFORE INSERT触发器中，其表内的值允许更改。

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

在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。

--输入
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);
--输出
+---------+
|order_num|
+---------+
|  20010  |
+---------+

4.2、DELETE触发器

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

在任意订单被删除前将执行此触发器，它使用一条SELECT语句将OLD中的值，保存到另一个结构相同的表archive_orders中。

多语句触发器：触发器deleteorder中使用BEGIN和END语句标记触发器体，在此例中并不是必须的，使用BEGIN END 块的好处是可以容纳多条SQL语句。

4.3、UPDATE触发器

在UPDATE触发器代码中，你可以引用一个OLD的虚拟的表，访问以前的行。可以引用名为NEW的虚拟表，访问新更新的值。在BEFORE UPDATE触发器中，NEW中的值可能也被更新。OLD中的值都是只读的， 不能更新。
下面例子使更新的州名字都是大写：

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

任何数据净化都需要在UPDATE语句之前进行，每次更新一行时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)来替换。

二、事务处理

1、概念和ACID原则

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的SQL语句要么完全执行要么完全不执行，只有InnoDB数据库引擎才支持事务处理。

举一个例子来进行说明：你去银行取钱，从ATM机取出钱和银行卡上扣钱必须要满足要么这两者全部执行，要么都不执行，不能你在银行取了200块钱，而银行卡上的的钱还是原来的那么多，是吧？虽然你可能希望这个事情发生。如果你希望这件事发生，但是也有可能是你银行卡上的钱扣了，但是，自动取款机出现故障，没有吐钱，这样你是不是比较郁闷。

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

事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务必须满足4个条件（ACID）

• 原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库得影响！
• 一致性（Consistency）：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！
• 隔离性（Isolation）：如果多个事务并发执行，应象各个事务独立执行一样！
• 持久性（Durability）：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

在使用事务和事务处理，涉及到以下一个术语，这里解释一下：

1、事务(transaction) 指一组SQL语句。

2、回退(rollback) 指撤销指定的SQL语句的过程。

3、提交(commit) 指将未存储的SQL语句结果写入到数据库中。

4、保留点(savepoint) 指事务处理过程中设置的临时占位符，你可以对它发布回退(与回退整个事务处理不同)。

2、控制事务处理

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

2.1、使用ROLLBACK

ROLLBACK语句用来撤销MySQL语句

SELECT * FROM ordertotals；
START TRANSACTION;
DELETE FROM ordertotals；
SELECT * FROM ordertotals;
ROLL BACK;
SELECT * FROM ordertotals;

首先执行一条SELECT语句以显示该表不为空，然后开始一个事务处理，用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。再使用ROLLBACK语句回退START TRANSACION后的所有语句。最后一条SELECT语句证明该表不为空，回退成功。

不能回退的语句：事务处理用来管理INSERT、UPDATE、DELETE语句。不能回退CREATE和DROP操作，事务处理块中可以使用这两条语句，但如果你执行回退，它们不会撤掉。

2.2、使用COMMIT

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

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

该例中，从系统中完全删除订单20010，因为涉及更新两个数据库表，所以应该使用事务处理来保证订单不被部分删除，最后的COMMIT语句仅在不出错时写出更改。如果，第一条DELETE起作用，而第二条失败。则DELETE不会提交。

2.3、使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理，但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。如果需要回退，可以回退到某个占位符。
为了创建占位符，可以使用SAVEPOINT delete1;
每个保留点都取标识它的唯一名字，以便在回退时知道要回退到何处。为了回到delete1可以ROLLBACK TO delete1

2.4、更改默认的提交行为

默认的MySQL行为是自动提交所有更改，即任何时候你执行一条SQL语句，该语句实际上都是针对表执行的，而且所做的更改即刻生效。为了指示MySQL不自动提交，可使用：

SET autocommit = 0；

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0指示MySQL不自动提交更改。

最后

以上就是平常导师为你收集整理的MySQL必知必会——触发器、事务处理一、触发器二、事务处理的全部内容，希望文章能够帮你解决MySQL必知必会——触发器、事务处理一、触发器二、事务处理所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。