文章目录
第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执行之前包含0,在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;