SQL 第12章 触发器

第12章触发器的创建和使用12.1概述12.2创建触发器12.3触发器实施数据完整性实例12.4查看、修改和删除触发器12.5使用触发器的注意事项返回目录212.1概述12.1.1触发器的概念及分类触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQLServer约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。3触发器（Trigger）是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行UPDATE、INSERT或DELETE语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。触发器是与表紧密联系在一起的，是在特定表上进行定义的，这个特定表也被称为触发器表。触发器和一般的存储过程又有一些不同，它不可以像调用存储过程一样由用户直接调用执行。触发器与表是密不可分的，触发器是不能离开表而独立存在的，触发器主要用于保护表中的数据，实现数据的完整性。对表中数据的操作有三种基本类型，数据插入、修改、删除，因此，触发器也有三种类型：INSERT、UPDATE、DELETE。当向触发器表中插入数据时，如果该触发器表有INSERT类型的触发器，INSERT触发器就被触发执行。同样的道理，UPDATE触发器会被数据更新触发执行、DELETE触发器会被数据删除触发执行。412.1.2触发器的工作原理根据对触发器表操作类型的不同，SQLServer为执行的触发器创建一个或两个专用的临时表：inserted表或者deleted表。注意，inserted表和deleted表的结构总是与被该触发器作用的表的结构相同，而且只能由创建它们的触发器引用。它们是临时的逻辑表，由系统来维护，不允许用户直接对它们进行修改。它们存放于内存中，并不存放在数据库中。触发器工作完成后，与该触发器相关的这两个表也会被删除。1.INSERT触发器的工作原理当一个记录插入到表中时，INSERT触发器自动触发执行，相应的插入触发器创建一个inserted表，新的记录被增加到该触发器表和inserted表中。如上所述，inserted表是个逻辑表，保存了所插入记录的拷贝，它允许用户参考初始的INSERT语句中的数据，触发器可以检查inserted表，以确定该触发器里的操作是否应该执行和如何执行。52.DELETE触发器的工作原理当从表中删除一条记录时，DELETE触发器自动触发执行，相应的删除触发器创建一个deleted表，deleted表是个逻辑表，用于保存已经从表中删除的记录，该deleted表允许用户参考原来的DELETE语句删除的已经记录在日志中的数据。应该注意：当被删除的记录放在deleted表中的时候，该记录就不会存在于数据库的表中了。因此，deleted表和数据库表之间没有共同的记录。3.UPDATE触发器的工作原理修改一条记录就等于插入一条新记录，删除一条旧记录。进行数据更新也可以看成由删除一条旧记录的DELETE语句和插入一条新记录的INSERT语句组成。当在某一个触发器表的上面修改一条记录时，UPDATE触发器自动触发执行，相应的更新触发器创建一个deleted表和inserted表，表中原来的记录移动到deleted表中，修改过的记录插入到了inserted表中。触发器可以检查inserted表和deleted表以及被修改的表，以确定是否修改了数据行和应该如何执行触发器的操作。本章首页612.2触发器的创建12.2.1使用T-SQL语句创建触发器1.创建触发器的SQL语句语法：创建触发器的一般语法如下。CREATETRIGGERtrigger_nameONtable_name[WITHENCRYPTION]FOR{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement●trigger_name：要创建的触发器名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。●table_name：指定所创建的触发器与之相关联的表名。必须是一个现存的表。7●WITHENCRYPTION：加密创建触发器的文本。●FOR{[INSERT][,][DELETE][,][UPDATE]}：指定所创建的触发器将在发生哪些事件时被触发，也即指定创建触发器的类型。“INSERT”，表示创建插入触发器；“DELETE”，表示创建删除触发器；“UPDATE”表示创建更新触发器。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，以逗号分隔这些选项。sql_statement：指定触发器执行的SQL语句。触发器只能在当前数据库中创建，并且一个触发器只能作用在一个表上。在同一条CREATETRIGGER语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。值得注意的是，在SQLServer2000中也可以对视图建立触发器，只要将视图名称作为table_name用在创建语法中就可以了。但是，对视图建立触发器有一些限制，详细信息请参考SQLServer2000联机丛书。82.创建INSERT触发器下面以为表student建立一个插入触发器为例，介绍创建INSERT触发器的方法。当某个班级增加一名学生，即向表student中插入一行数据时，需要更改该学生所在班级的记录，以增加该班级的学生总人数。下例使用INSERT触发器自动完成这个工作。【例12-1】为student表建立INSERT触发器以自动更新class表学生人数。USEjwglGO/*如果存在同名的触发器，则删除之*/IFEXISTS(SELECTnameFROMsysobjectsWHEREtype=‘TR‘ANDname='student_insert')DROPTRIGGERstudent_insertGO9CREATETRIGGERstudent_insertONstudentFORINSERTASDECLARE@NumOfStudentTINYINTSELECT@NumOfStudent=c.student_numFROMclassc,insertediWHEREc.class_id=i.class_idIF(@NumOfStudent0)BEGINUPDATEclassSETstudent_num=student_num+1FROMclassc,insertediWHEREc.class_id=i.class_idENDELSE10BEGINUPDATEclassSETstudent_num=(SELECTCOUNT(s.student_id)FROMstudents,insertediWHEREs.class_id=i.class_id)FROMclassc,insertediWHEREc.class_id=i.class_idENDGO下面来测试它的运行情况。1.首先查询一个班级的当前人数，如“g99403”。执行如下代码：SELECT*FROMclassWHEREclass_id=‘g99403’系统显示如下运行结果：class_idmonitorclassroomstudent_numg99403成佳洱教学楼212511从上面执行结果可以看见，当前g99403班级当前的学生人数是5人。2.现在，使用上一章建立的添加学生记录的存储过程spAddStudent添加一个名字为程涛的学生记录。执行如下代码：EXECspAddStudent'g9940306','程涛','男','1981-9-22','g99403','1999-9-1','南京市御道街2号'3.再一次使用前面的语句查询g99403班级的信息，可以看到下面的结果显示：class_idmonitorclassroomstudent_numg99403成佳洱教学楼2126上面执行结果显示，class表中该班级记录的student_num字段已经自动更新为6了。12class表student_num列自动更新的原因是：在student表上执行了INSERT操作后，触发了插入触发器student_insert，该触发器自动对学生人数进行累加（或重新统计）。从上述例子可以看出，通过触发器可以维护数据完整性。在上述定义插入触发器时，使用了inserted专用临时表，它保存所插入行的一个拷贝。下面我们来具体看看上面例子完整的执行过程。•①通过存储过程spAddStudent，系统对student表进行了INSERT操作，执行了插入一行数据的操作。系统检查被插入新值的正确性（如：约束等），如果正确，则将新行插入到student表，同时建立临时表inserted并在这个表中也插入新行。13•②执行插入触发器中的语句。首先定义了一个变量@NumOfStudent来检查class表中对应班级的学生人数，如果学生人数为0（这种情况可能发生在新添加一个班级等）则重新统计该班级的学生人数，否则直接把该班级的学生人数加1（当然也可以不用判断，而在每次触发时都重新统计学生人数）。•③如果整个过程中某个环节出现错误，或执行到ROLLBACK语句，系统将回滚整个操作，包括对student表学生记录的插入和对class表student_num的修改。从事务角度来说，触发器与触发它的语句（如：INSERT语句）可看作同一个事务的不同部分。在执行了插入操作后我们看到结果显示了两次“（所影响的行数为1行）”的提示。第一次表示插入操作成功，第二次表示由插入数据操作引发的执行INSERT触发器操作对student_num列修改成功。143.创建DELETE触发器当从某个班级删除一名学生，即从表student中删除一行数据时，需要更改该学生所在班级的学生总人数。对student_insert的代码稍加修改就可以得到能自动完成这个工作的删除触发器。【例12-2】为student表建立DELETE触发器，在删除学生记录时自动更新class表中相应班级的学生人数。USEjwglGO/*如果存在同名的触发器，则删除之*/IFEXISTS(SELECTnameFROMsysobjectsWHEREtype='TR'ANDname='student_delete')DROPTRIGGERstudent_deleteGO15CREATETRIGGERstudent_deleteONstudentFORDELETEASDECLARE@NumOfStudentTINYINTSELECT@NumOfStudent=c.student_numFROMclassc,deleteddWHEREc.class_id=d.class_idIF(@NumOfStudent0)BEGINUPDATEclassSETstudent_num=student_num-1FROMclassc,deleteddWHEREc.class_id=d.class_idEND16ELSEBEGINUPDATEclassSETstudent_num=(SELECTCOUNT(s.student_id)FROMstudents,deleteddWHEREs.class_id=d.class_id)FROMclassc,deleteddWHEREc.class_id=d.class_idENDGO在class表中student_num列的值自动减1的原因是：在student表上执行删除学生记录的操作时，触发了student表上的删除触发器，该触发器将class表上的student_num字段值减1。在上述定义触发器时，使用了deleted逻辑表。deleted表是一个逻辑表,在执行删除触发器语句时,表中被删除的行存放在deleted表中。