《数据库应用技术项目化教程》第八章触发器和事务

第八章触发器和事务学习目标掌握创建、删除触发器；事务处理的过程13了解触发器的概念、触发器的常见应用；事务的调用2理解索引的概念和分类；事务的隔离级别掌握了解熟悉

触发器是一种与表操作（INSERT、UPDATE、DELETE）有关的数据库对象。触发器定义了一系列操作，这一系列操作称为触发程序。当触发器所在表上出现INSERT、UPDATE以及DETETE操作时，将激活触发器，即表的操作事件触发表上的触发程序的执行。

触发器基于一个表创建，但是可以针对多个表进行操作，所以触发器可以用来对表实施复杂的完整性约束。8.1触发器概念和创建触发器8.1.1触发器概述触发器具有以下优点：（1）触发器自动执行。当对表进行INSERT、UPDATE以及DETETE操作，试图修改表中的数据时，相应操作的触发器立即自动执行。（2）触发器可以通过数据库中相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。（3）触发器可以实现用CHECK约束实现不了的复杂约束。与CHECK约束相比，触发器可以引用其他表中的列。（4）触发器可以维护冗余数据，实现外键级联选项等。8.1触发器概念和创建触发器8.1.2触发器的优点CREATETRIGGER语句语法格式如下：CREATETRIGGER触发器名AFTER/BEFOREINSERT/UPDATE/DELETEON表名FOREACHROWBEGINSQL语句;END;8.1触发器概念和创建触发器8.1.3创建触发器需要说明的是：（1）触发器是数据库对象，因此创建触发器时，需要指定该触发器属于哪一个数据库。（2）触发器是在表是创建的。这个表必须是基表，不能是临时表，也不能是视图。（3）MySQL的触发事件有三种：INSERT、UPDATE及DELETE。

INSERT：将新记录插入表时激活触发程序。

UPDATE：更改表中的记录时激活触发程序。

DELETE：从表中删除记录叶激活触发程序。（4）触发器的触发时间有两种：BEFORE和AFTER。

BEFORE表示在触发事件发生之前执行触发程序，AFTER表示在触发事件发生之后执行触发程序。8.1.4创建触发器8.1触发器概念和创建触发器（5）FOREACHROW表示行级触发器。

目前，MySQL仅支持行级触发器，不支持语句级别的触发器。FOREACHROW表示INSERT、UPDATE及DELETE操作影响的每一条记录都会执行一次触发程序。（6）触发程序中的SELECT语句不能产生结果集。（7）触发程序中可以使用old关键字与new关键字。①当向表中插入新记录时，在触发程序中可以使用new关键字表示新记录。当需要访问新记录中的某个字段时，可以使用“new.字段名”进行访问。②当从表中删除某条旧记录时，在触发程序中可以使用old关键字表示旧记录。当需要访问旧记录中的某个字段时，可以使用“old.字段名”进行访问。8.1触发器概念和创建触发器8.1.4创建触发器③当修改表中的某条记录时，在触发程序中可以使用new关键字表示修改后的记录。使用old关键字表示修改前的记录。当需要访问修改后的记录中的某个字段时，可以使用“new.字段名”进行访问。当需要访问旧记录中的某个字段时，可以使用“old.字段名”进行访问。④old记录是只读的，在触发程序中只能引用它，但不能更改它。在BEFORE触发程序中，可使用“SETnew.字段名=值”更改new记录的值。但在AFTER触发程序中，不能使用“SETnew.字段名=值”更改new记录的值。⑤对于INSERT操作，只有new是合法的。对于DELETE操作，只有old是合法的。对于UPDATE操作，new和old都是合法的。8.1.4创建触发器8.1触发器概念和创建触发器一、使用触发器实现检查约束使用触发器实现检查约束，在向学生信息表tb_xsxxb表插入记录时，XBDM字段的值或者为空，或者必须是'1'或'2'。如果XBDM字段的值不满足要求，则将XBDM的值改成为空（NULL）。对应的SQL语句如下：DELIMITER//CREATETRIGGERtr_insertBEFOREINSERTONtb_xsxxbFOREACHROWBEGINIF(new.XBDMISNULL||new.XBDMNOTin(1,2))THENSETnew.XBDM=NULL;ENDIF;END;//8.1触发器概念和创建触发器8.1.5触发器的使用实例8-1使用触发器实现检查约束，在对学生信息表tb_xsxxb修改记录时，修改的记录XBDM字段的值或者为空，或者是1或2。如果XBDM字段的值不满足要求，则记录不能修改。对应的SQL语句如下：DELIMITER//CREATETRIGGERtr_updateBEFOREUPDATEontb_xsxxbFOREACHROWBEGINIF(new.XBDMISNULL||new.XBDMNOTIN(1,2))THENSETnew.XBDM=old.XBDM;ENDIF;END;//8.1触发器概念和创建触发器实例8-28.1.5触发器的使用二、使用触发器维护冗余数据使用触发器实现：当学生信息表tb_xsxxb中的性别代码修改为1时，则将该条记录的学号、姓名、性别代码字段值放到tb_xsxxb_ns表中。DELIMITER//CREATETRIGGERtr_update_nsAFTERUPDATEontb_xsxxbFOREACHROWBEGINIF(new.XBDM=1)THEN

INSERTINTOtb_xsxxb_ns(xh,xm,xbdm)VALUES(new.xh,new.xm,new.xbdm);ENDIF;END;//8.1触发器概念和创建触发器实例8-3(1)创建触发器tr_1，实现学生信息表tb_xsxxb中的学号XH字段和tb_xsxxb_ns表中的XH字段的外键CASCADE级联选项，当学生信息表tb_xsxxb中删除某个学生时，

tb_xsxxb_ns表中也删除对应学生信息。DELIMITER//CREATETRIGGERtr_1BEFOREDELETEONtb_xsxxbFOREACHROWBEGIN

IF(EXISTS(SELECT*FROMtb_xsxxb_nsWHEREXH=old.XH))THEN

DELETEFROMtb_xsxxb_nsWHEREXH=old.XH;ENDIF;END;//8.1触发器概念和创建触发器实例8-4(2)创建触发器tr_2，实现学生信息表tb_xsxxb中学号XH字段和tb_xsxxb_ns表中学号XH字段的外键SETNULL级联选项，当学生信息表tb_xsxxb中修改某个的学生学号时，tb_xsxxb_ns中对应学号的XBDM设为NULL。DELIMITER//CREATETRIGGERtr_2BEFOREUPDATEONtb_xsxxbFOREACHROWBEGIN

IF(EXISTS(SELECT*FROMtb_xsxxb_nsWHEREXH=old.XH))THEN

UPDATEtb_xsxxb_nsSETXBDM=NULLWHEREXH=old.XH;ENDIF;END;//8.1触发器概念和创建触发器实例8-5（1）使用SHOWTRIGGERS命令查看触发器的定义。

使用“SHOWTRIGGERS\G”命令可以查看当前数据库中所有触发器的信息。使用“SHOWTRIGGERSLIKE模式\G”命令查看与模式模糊匹配的触发器的信息。

查看前面stu表上创建的触发器的信息。

对应的SQL语句如下：

SHOWTRIGGERSLIKE'tb_xsxxb%'\G

注意：当使用一个含有SHOWTRIGGERS的LIKE子句时，待匹配的表达式（expr）会与触发器定义时所在的表的名称相比较，而不与触发器的名称相比较。8.1触发器概念和创建触发器8.1.6查看触发器的定义8.1触发器概念和创建触发器（2）使用SHOWCREATETRIGGER命令查看触发器的定义。

使用“SHOWCREATETRIGGER触发器名”命令可以查看指定名称的触发器的定义。（3）通过查询information_schema数据库中的triggers表，可以查看触发器的定义。MySQL中所有触发器的定义都存放在information_schema数据库里的triggers表中，查询triggers表时，可以查看所有数据库中所有触发器的详细信息，查询语句如下：SELECT*FROMinformation_schema.triggers\G8.1.6查看触发器的定义如果某个触发器不再使用，可以使用DROPTRIGGER语句将其删除。DROPTRIGGER语句语法如下：

DROPTRIGGER触发器名;8.1触发器概念和创建触发器8.1.7删除触发器

所谓的事务（Transaction）是指由用户定义的一系列数据库更新操作，这些操作，要么都执行，要么都不执行，是一个不可分割的逻辑工作单元。这里的更新操作主要指对数据库内容产生修改作用的操作，比如：insert、delete、update等操作。8.2.1事务概述8.2事务原子性（Atomicity）事务的原子性是指事务不可分割。例如：张三和李四各有100元，张三借给李四50元，李四还给张三40元……无论两者之间如何借还，两者的总金额200元不变。一致性（Consistency）事务在完成时，必须使所有的数据都保持一致状态，即所有的数据都要发生更改，以保证数据的完整性。例如：张三和李四各有100元，张三借给李四50元，但张三和李四各自余额都需要改变，以保证数据的完整性。隔离性（Isolation）两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时、运行中间某一时刻的数据。例如：在进行微信支付时（T1），同时查询微信余额（T2），则支付必须在查询前进行，或查询在支付后进行，不能同时进行。持久性（Durability）一旦事务被提交之后，数据库的变化就会被永远保留下来，即使运行数据库软件的机器后来奔溃也是如此。例如：微信支持一旦完成，支付的记录数据就会被永久地保留下来了，即使数据库系统关闭也不会丢失数据。8.2.2事务的四个特性8.2事务事务处理首先要开启事务，使用的语句如下：

BEGIN/STARTTRANSACTION（等价）;开启事务之后，就开始执行事务内的SQL语句，当SQL语句执行完毕后，必须使用语句提交事务，使用的语句如下：

COMMIT/COMMITWORK（等价）;开启事务之后，事务中的SQL语句只有在提交事务之后才能运行生效。在提交事务之前，还可以使用语句取消事务（回滚语句），使用的语句如下：

ROLLBACK/ROLLBACKWORK（等价）;8.2.3事务的使用8.2事务

在手动提交的方式下，启动一个事务，在学院代码表tb_xydmb中插入两条记录，具体语句如下：BEGIN;INSERTINTOtb_xydmbvalues('06','机电学院');INSERTINTOtb_xydmbvalues('07','化工学院');mysql>select*fromtb_xydmb;执行结果如下所示：实例8-68.2事务但是退出数据库重新登陆后，再次进行查询。结果如下：从以上结果可以看出，事务中的记录插入操作最终并未完成，这是因为事务处理未经提交（COMMIT）就已经退出数据库了，由于采用的是手动提交模式，当前操作被自动取消了。8.2事务实例8-6在下面语句中，在事务处理结束后加入COMMIT语句，就可以最终完成整个事务的提交，具体代码如下：BEGIN;INSERTINTOtb_xydmbvalues('06','机电学院');INSERTINTOtb_xydmbvalues('07','化工学院');COMMIT;8.2事务实例8-6

在进行事务处理时，如果事务尚未提交时发现某些操作不合理，可以通过事务的回滚命令ROLLBACK

来取消当前事务，把数据库恢复到事务处理之前的状态（即相当于撤销操作）。8.2.4事务的回滚8.2事务MySQL数据库访问过程中采用的是并发访问方式，在多个线程同时开启事务访问数据库时，可能会出现脏读、不可重复读以及幻读等情况，具体如下所示：脏读：脏读就是一个事务读取另一个没有提交的事务的数据，从而读取了无效数据。它是指当前一个事务正在访问数据，并且对数据进行了修改，然而这些修改还没有被提交到数据库，同时另外一个事务也正在访问和使用了这个数据，造成读出的数据有误。不可重复读：是指在一个事务内，对同一数据进行两次相同查询，所得返回结果不同。这是因为这个事务还没有结束时，别的事务也可以访问这个同一数据。幻读：是指在同一事务中，两次对数据行数进行查询，所得的结果不一致。它和不重复读有些类似，只是造成幻读的在于本事务处理没有结束时，其他事务对同一数据集合的增加或者删除操作造成记录数的变化。8.2.5事务的隔离级别8.2事务为了避免以上情况的发生，MySQL设置了事务的4种隔离级别，由低到高分别READUNCOMMITTEDREADCOMMITTEDREPEATABLEREADSERIALIZABLE（设置语句：setsessiontransactionisolationlevel隔离级别;）（查看语句：select@@tx_isolation;）能够有效地防止脏读（DirtyRead）、重复读（RepeatableRead）以及幻读（PhantomRead）等情况。READUNCOMMITTEDREADUNCOMMITTED就是“读未提交”，在该级别下的事务可以读取另一个未提交事务的数据，它是事务中的最低级别。该级别在实际应用中容易出现脏读等情况，因此很少应用。READCOMMITTEDREADCOMMITTED就是“读提交”，顾名思义，就是在该级别下的事务只能在其他事务已经提交的情况下读取数据