trigger

5.6触发器触发器是用户定义在关系表上的一类由事件驱动的特殊过程一旦定义，任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在DBMS核心层进行集中的完整性控制。类似于约束，但比约束更加灵活，可以实施比FOREIGNKEY约束、CHECK约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。5.6.1触发器的类型5.6.2触发器中的inserted表和deleted表5.6.3创建触发器5.6.4使用触发器实施特殊业务规则5.6.5管理触发器5.6应用触发器5.6.1触发器的类型触发器（TRIGGER）是一种实施复杂数据完整性的特殊存储过程，如使用外键约束所不能实现的复杂参照完整性。它在数据操作语句执行时自动生效。通过触发器可实现跨数据库的参照完整性。SQLServer2005包括两大类触发器：DML触发器和DDL触发器。1．DDL触发器DDL触发器是SQLServer2005的新增功能，当服务器或数据库中发生数据定义语言（DDL）事件时触发。2．DML触发器当数据库中发生数据操作语言（DML）事件时，如在指定的表或视图中执行INSERT、UPDATE或DELETE语句时，触发DML触发器。5.6.1触发器的类型DML触发器又分为以下几种类型：（1）AFTER触发器。在执行了INSERT、UPDATE或DELETE语句之后触发AFTER触发器，其只能在表上指定。被INSERT语句触发的触发器称为INSERT型AFTER触发器，被DELETE语句触发的触发器称为DELETE型AFTER触发器，被UPDATE语句触发的触发器称为UPDATE型AFTER触发器。（2）INSTEADOF触发器。执行INSTEADOF触发器代替触发动作进行激活，但同一种操作只能定义一种触发器。INSTEADOF触发器可以定义在表或视图上。（3）CLR触发器。CLR触发器可以是AFTER触发器或INSTEADOF触发器，还可以是DDL触发器。3．DML触发器的特点DML触发器的特点如下：（1）强制比CHECK约束更为复杂的数据完整性。（2）实现数据库中多个表的级联更改。例如，表A的触发器A中包含对表B的数据操作A，而该操作A（插入、删除或修改）会导致表B中的触发器B被触发，触发器B指定的操作B又会影响表C的数据。（3）一个表中可包含多个同类DML触发器，并允许采取不同的操作响应同一个修改语句。（4）维护非规范化数据，实现特殊业务。（5）使用自定义的错误消息，捕获破坏数据完整性的操作并返回自定义的错误提示信息。当触发器被触发，SQLServer为每个触发器在服务器内存中创建两个临时表：inserted表和deleted表。这是两个逻辑表（不会存储在数据库文件中），由系统自动维护而不允许用户直接对它进行修改。触发器工作完成后，这两个表也随之被删除。它们的结构总是与对应的触发器表的结构相同。5.6.2触发器中的inserted表和deleted表1．inserted表用于存储触发器表中INSERT和UPDATE语句所影响的记录的副本。在一个插入或更新的操作事件处理中，新插入的记录被同时添加到触发器表和inserted表中。2．deleted表deleted表用于存储触发器表中DELETE和UPDATE语句所影响的记录的副本。在触发器表中执行DELETE或UPDATE操作事件时，记录从触发器表中删除，同时传输（复制）到deleted表中暂存起来。因此，deleted表和触发器表通常没有相同的数据行，因为此时记录已经从触发器表中被删除。•提示：一个UPDATE操作可以分解为一个DELETE操作加一个INSERT操作。因此，UPDATE型触发器被触发时会同时创建deleted表和inserted表。触发器的定义包含以下四个要素：（1）名称：有一个符合标志符命名规则的触发器名称。（2）定义的目标：触发器必须定义在表或视图上，而AFTER触发器只能定义在表上（触发器表）。（3）触发条件：分别由INSERT、UPDATE或DELETE操作事件语句触发。（4）触发逻辑：被触发后会执行哪些操作（处理逻辑）。5.6.3创建触发器DBMS的完整性控制机制（续）例2：在“教授工资不得低于1000元”的约束中D约束作用的对象为工资Sal属性O插入或修改职工元组时ASal不能小于1000C职称=′教授′(A仅作用于职称=‘教授’的记录)P拒绝执行该操作1．CREATETRIGGER语句的语法使用CREATETRIGGER语句创建触发器，基本语法如下：CREATETRIGGERtrigger_nameONtable_name|view_name[WITHENCRYPTION]FOR|AFTER|INSTEADOF[INSERT][,][DELETE][,][UPDATE]AS(sql_statement)5.6.3创建触发器其中：•trigger_name：触发器名称，必须符合标识符的命名规则，且在当前数据库中唯一。•table_name|view_name：指定创建触发器的表或视图，即触发器表或视图。•[INSERT][,][DELETE][,][UPDATE]：指明在触发器表中执行哪些操作时会激活对应类型的触发器。必须指定一个选项，选项顺序任意，选项之间用逗号分隔。•AS：用来指定触发器要执行的操作序列。•sql_statement：触发器要执行的操作序列。创建UPDATE触发器在数据库JXGL的表student_info中创建触发器student_info_update，实现当一名学生由一个班调到另一个班后，自动更新表class_info中相应班级的学生数（stud_num）。代码如下：USEJXGLCREATETRIGGERstudent_info_update--指定触发器名称ONstudent_info--指定触发器表FORUPDATE--指定触发条件ASDECLARE@oldclass_idvarchar(8),@newclass_idvarchar(8)SELECT@oldclass_id=class_idFROMdeletedSELECT@newclass_id=class_idFROMinsertedIF(@oldclass_id@newclass_id)BEGINUPDATEclass_infoSETstud_num=stud_num-1WHEREclass_id=@oldclass_idUPDATEclass_infoSETstud_num=stud_num+1WHEREclass_id=@newclass_idENDUSEJXGLCREATETRIGGERstudent_info_update--指定触发器名称ONstudent_info--指定触发器表FORUPDATE--指定触发条件ASIF(UPDATE(class_id))BEGINUPDATEclass_infoSETstud_num=stud_num-1FROMclass_info,deletedWHEREclass_info.class_id=deleted.class_idUPDATEclass_infoSETstud_num=stud_num+1FROMclass_info,insertedWHEREclass_info.class_id=inserted.class_idEND例题1、设置一个针对student表的插入触发器，当插入某个学生记录，系别这项为空时，设定为CS。2、对sc创建一个一次只删除一条记录的触发器，否则报错“此删除操作可能会删除多次选课数据!!!”3、级联更新：当修改course表中cno列的值时，同时修改sc表中的cno列的值，并且一次只能更新一行，否则报错“此删除操作可能会删除多门课程数据!!!”例题4、创建触发器，当输入某个同学选课成绩时，如果是课程学分超过3，其成绩自动提升10%。5、为SC表建立一个插入触发器insertSC，当添加一门课程的成绩时自动输出此门课程的最高分。1、设置一个针对student表的插入触发器，当插入某个学生记录，系别这项为空时，设定为CS。CREATETRIGGERINS_STUDENTONSTUDENTFORINSERTASDECLARE@SNOCHAR(5),@SDEPTCHAR(2)SELECT@SNO=SNO,@SDEPT=SDEPTFROMINSERTEDIF(@SDEPTISNULL)UPDATESTUDENTSETSDEPT='CS'WHERESNO=@SNO//触发操作：INSERTINTOSTUDENTVALUES('98021','钱横',20,'男',NULL);//验证结果：SELECT*FROMSTUDENTWHERESNO='98021';2、对sc创建一个一次只删除一条记录的触发器，否则报错“此删除操作可能会删除多次选课数据!!!”createtriggerdel_sconscfordeleteasdeclare@row_cntintselect@row_cnt=count(*)fromdeletedif@row_cnt1beginprint'此删除操作可能会删除多次选课数据!!!'rollbacktransactionend//触发操作：deletefromscwheresno='98001'SELECTRAISERROR('此删除操作可能会删除多次选课数据!!!',16,1)3、级联更新：当修改course表中cno列的值时，同时修改sc表中的cno列的值，并且一次只能更新一行，否则报错“此删除操作可能会删除多门课程数据!!!”createtriggercourse_updateoncourseForupdateAsdeclare@row_cntintselect@row_cnt=count(*)fromdeletedIf@row_cnt1beginprint'此删除操作可能会修改多门课程数据!!!'rollbacktransactionendelsebeginifupdate(cno)beginDECLARE@OLDCNOCHAR(2)DECLARE@NEWCNOCHAR(2)SELECT@OLDCNO=CNOFROMDELETEDSELECT@NEWCNO=CNOFROMINSERTEDUpdatescsetcno=@NEWCNOWherecno=@OLDCNOendend//触发操作：①第一种更新多行：updatecoursesetccredit=1whereccredit=3②第二种更新单行，完成级联更新updatecoursesetcno='02'wherecno='2'//验证结果：SELECT*FROMCOURSE;SELECT*FROMSCWHERECNO='02'4、创建触发器，当输入某个同学选课成绩时，如果是课程学分超过3，其成绩自动提升10%。CREATETRIGGERsc_insertONscFORINSERTASDECLARE@SNOCHAR(5),@CNOCHAR(2),@CCREDITINTSELECT@SNO=SNO,@CNO=CNOFROMINSERTEDSELECT@CCREDIT=CCREDITFROMCOURSEWHERECNO=@CNOIF(@CCREDIT3)UPDATESCSETGRADE=GRADE*1.1WHERESNO=@SNOANDCNO=@CNO//触发操作：INSERTINTOSCVALUES('98002','5',80);//验证结果:SELECT*FROMSCWHERESNO='98002'ANDCNO='5';5、为SC表建立一个插入触发器insertSC，当添加一门课程的成绩时自动输出此门课程的最高分。CREATETRIGGERinsertSCONSCFORINSERTASDECLARE@MAXGRADEINTSELECT@MAXGRADE=MAX(SC.GRADE)FROMSC,