各种-教学新07mysql触发器与派生表

MySQL数据库基础与实例教程

之视图触发器123

本章首先讲解了视图以及触发器的管理及使用，然后结合“选课系统”分别介绍视图以及触发器在该系统中的应用。4触发器视图临时表派生表（DerivedTable）5视图、子查询、临时表、派生表内容一览123触发器主要用于监视某个表的insert、update以及delete等更新操作，这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行，从而实现数据的自动维护。使用触发器实现检查约束准备工作使用触发器维护冗余数据45使用触发器模拟外键级联选项查看触发器的定义

触发器67触发器主要用于监视某个表的insert、update以及delete等更新操作，这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行，从而实现数据的自动维护。删除触发器使用触发器的注意事项

触发器

触发器使用createtrigger语句可以创建一个触发器，语法格式如下。createtrigger触发器名触发时间触发事件on表名foreachrowbegin触发程序end

准备工作MySQL的触发事件有三种：insert：将新记录插入表时激活触发程序，例如通过insert、loaddata和replace语句，可以激活触发程序运行。

准备工作MySQL的触发事件有三种：update：更改某一行记录时激活触发程序，例如通过update语句，可以激活触发程序运行。delete：从表中删除某一行记录时激活触发程序，例如通过delete和replace语句，可以激活触发程序运行。

准备工作触发器的触发时间有两种：before与after。before表示在触发事件发生之前执行触发程序。after表示在触发事件发生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置六种类型的触发器。

准备工作foreachrow表示行级触发器。目前MySQL仅支持行级触发器，不支持语句级别的触发器（例如createtable等语句）。foreachrow表示更新（insert、update或者delete）操作影响的每一条记录都会执行一次触发程序。

准备工作触发程序中可以使用old关键字与new关键字。当向表插入新记录时，在触发程序中可以使用new关键字表示新记录，当需要访问新记录的某个字段值时，可以使用“new.字段名”的方式访问。

准备工作触发程序中可以使用old关键字与new关键字。当从表中删除某条旧记录时，在触发程序中可以使用old关键字表示旧记录，当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。

准备工作触发程序中可以使用old关键字与new关键字。当修改表的某条记录时，在触发程序中可以使用old关键字表示修改前的旧记录、使用new关键字表示修改后的新记录。当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。当需要访问修改后的新记录的某个字段值时，可以使用“new.字段名”的方式访问。

准备工作触发程序中可以使用old关键字与new关键字。old记录是只读的，可以引用它，但不能更改它。在before触发程序中，可使用“setnew.col_name=value”更改new记录的值。

准备工作前面曾经提到，MySQL可以使用复合数据类型set或者enum对字段的取值范围进行检查约束，使用复合数据类型可以实现离散的字符串数据的检查约束，对于数值型的数不建议使用set或者enum实现检查约束，可以使用触发器实现。

使用触发器实现检查约束冗余的数据需要额外的维护，维护冗余数据时，为了避免数据不一致问题的发生（例如：剩余的学生名额+已选学生人数≠课程的人数上限），冗余的数据应该尽量避免交由人工维护，建议冗余的数据交由应用系统（例如触发器）自动维护。

使用触发器维护冗余数据对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、setnull或者noaction（restrict），外键约束关系可以交由InnoDB存储引擎自动维护。

使用触发器模拟外键级联选项可以使用下面四种方法查看触发器的定义。1．使用showtriggers命令查看触发器的定义

查看触发器的定义可以使用下面四种方法查看触发器的定义。2．查询information_schema数据库中的triggers表，可以查看触发器的定义MySQL中所有触发器的定义都存放在information_schema数据库下的triggers表中，查询triggers表，可以查看所有数据库中所有触发器的详细信息，查询语句如下：select*frominformation_schema.triggers\G

查看触发器的定义可以使用下面四种方法查看触发器的定义。3．使用“showcreatetrigger”命令可以查看某一个触发器的定义。例如使用“showcreatetriggerorganization_delete_before_trigger\G”命令可以查看触发器organization_delete_before_trigger的定义。

查看触发器的定义可以使用下面四种方法查看触发器的定义。4．成功创建触发器后，MySQL自动在数据库目录下创建TRN以及TRG触发器文件，以记事本方式打开这些文件，可以查看触发器的定义。

查看触发器的定义可以使用droptrigger语句将该触发器删除，语法格式如下。droptrigger触发器名

删除触发器1．触发程序中如果包含select语句，该select语句不能返回结果集。2．同一个表不能创建两个相同触发时间、触发事件的触发程序。3．触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句，如starttransaction、commit、rollback或者setmit=0等语句。

使用触发器的10条注意事项4．MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致更新操作性能降低。5．在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用触发器可以保证更新操作与触发程序的原子性，此时触发程序和更新操作是在同一个事务中完成。

使用触发器的10条注意事项6．InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系时，但可以使用触发器实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系。

使用触发器的10条注意事项7．使用触发器维护InnoDB外键约束的级联选项时，数据库开发人员究竟应该选择after触发器还是before触发器？答案是：应该首先维护子表的数据，然后再维护父表的数据，否则可能出现错误。

使用触发器的10条注意事项8．MySQL的触发程序不能对本表进行更新语句（例如update语句）。触发程序中的更新操作可以直接使用set命令替代，否则可能出现错误信息，甚至陷入死循环。9．在before触发程序中，auto_increment字段的new值为0，不是实际插入新记录时自动生成的自增型字段值。使用触发器的10条注意事项10．添加触发器后，建议对其进行详细的测试，测试通过后再决定是否使用触发器。

使用触发器的10条注意事项派生表与视图一样，一般在from子句中使用，其语法格式如下（粗体字代码为派生表代码）。….from(select子句)派生表名….派生表必须是一个有效的表，因此它必须遵守以下规则：每个派生表必须有自己的别名。派生表中的所有字段必须要有名称，字段名必须唯一。

派生表（DerivedTable）子查询一般在主查询语句中的where子句或者having子句中使用。视图通常在主查询语句中的from子句中使用。

子查询、视图、临时表、派生表视图本质是一条select语句，执行的是某一个数据源某个字段的查询操作，如果视图的“主查询”语句是update语句、delete语句或者insert语句，且“主查询”语句执行了该数据源该字段的更新操作，那么主查询语句将出错。原因非常简单：在对某个表的某个字段操作时，查询操作（select语句）不能与更新操作（update语句、delete语句或者insert语句）同时进行。

子查询、视图、临时表、派生表与视图相似，临时表一般在from子句中使用。临时表与视图的区别在于：临时表本质也是一条select语句，执行的是某一个数据源某个字段的查询操作，但由于临时表会先执行完毕，并且将查询结果集提前置放到服务器内存。因此“临时表”的“主查询”语句（例如update、delete或者insert语句）执行字段的更新操作时，不会产生“ERROR1443(HY000)”错误。

子查询、视图、临时表、派生表派生表与临时表的功能基本相同，它们之间的最大区别在于生命周期不同。

临时表如果是手工创建，