触发器教学案例

触发器的工作过程教学案例SQLServer为每个触发器都创建了两个专用临时表：•INSERTED表和DELETED表•两个表用户不能进行修改，只能在触发器程序中查询表中的内容。•触发器执行完毕后，与该触发器相关的这两个表也会被删除。触发器的工作过程当执行INSERT语句时，INSERTED表存放要向表中插入的所有行。当执行DELETE语句时，DELETED表存放要从表中删除的所有行。当执行UPDATE语句时，旧的行被移动到DELETED表，而新的行插入到INSERTED表。触发器的工作过程INSERT触发器的工作过程•在定义了INSERT触发器的表上执行INSERT语句•新行被同时增加到触发器表和inserted表中，inserted表保存了插入行的副本•触发器动作被执行INSERT触发器的工作方式示例：在定义了INSERT触发器的表上执行INSERT语句insertintostudentvalues('0701004','张明','男','1987-02-24','0701')INSERT语句插入的行被记录下来stu_idstu_namestu_sexstu_birthdayclass_id0701001李玉女1987-05-0607010701002鲁敏女1988-06-280701……………0701004张明男1987-02-240701inserted0701004张明男1987-02-240701触发器代码CREATETRIGGERstu_insert1ONstudentAFTERINSERTASdeclare@classidchar(6)select@classid=class_idfrominsertedUPDATEclassSETclass_num=class_num+1whereclass_id=@classidUPDATEclassSETclass_num=class_num+1whereclass_id=@classidclass_idclass_nameclass_num0701软件开发30702计算机办公应用20703网络技术10704张明数字媒体44思考一次性插入若干条同一专业的学生一次性插入若干条不同专业的学生问：触发器如何定义？一次性插入若干条相同专业的学生触发器参考定义CREATETRIGGERstu_insert2ONstudentAFTERINSERTASdeclare@classidchar(6)select@classid=class_idfrominsertedUPDATEclassSETclass_num=class_num+(selectcount(*)frominserted)whereclass_id=@classidDELETE触发器的工作过程•在定义了DELETE触发器的表上执行DELETE语句•DELETE语句删除的行被记录下来，放入deleted表，deleted表是保存了被删除行的副本•触发器动作被执行DELETE触发器的工作方式注：当行添加到deleted表后，将不再存在于数据库表中示例1：在定义了DELETE触发器的表上执行DELETE语句deletefromstudentwherestu_id='0701004'DELETE语句删除的行被记录下来stu_idstu_namestu_sexstu_birthdayclass_id0701001李玉女1987-05-0607010701002鲁敏女1988-06-280701……………0701004张明男1987-02-240701deleted0701004张明男1987-02-240701触发器代码CREATETRIGGERstu_delete1ONstudentAFTERDELETEASdeclare@classidchar(6)select@classid=class_idfromdeletedUPDATEclassSETclass_num=class_num-1whereclass_id=@classidUPDATEclassSETclass_num=class_num-1whereclass_id=@classidclass_idclass_nameclass_num0701软件开发40702计算机办公应用20703网络技术10704张明数字媒体43示例2：在定义了DELETE触发器的表上执行DELETE语句deletefromstudentwherestu_sex='男'andclass_id='0704'DELETE语句删除的行被记录下来deleted0704002周晓男1989-04-2707040704003易国梁男1988-11-2607040704004季风男1987-09-210704触发器代码CREATETRIGGERstu_delete2ONstudentAFTERDELETEASdeclare@classidchar(6)select@classid=class_idfromdeletedUPDATEclassSETclass_num=class_num-(selectcount(*)fromdeleted)whereclass_id=@classidUPDATEclassSETclass_num=class_num-(selectcount(*)fromdeleted)whereclass_id=@classidclass_idclass_nameclass_num0701软件开发30702计算机办公应用20703网络技术10704数字媒体41思考一次性删除若干条不同专业的学生问：触发器如何定义？一次性删除若干条不同专业的学生触发器参考定义CREATETRIGGERstu_delete3ONstudentAFTERDELETEASUPDATEcSETclass_nun=class_num-del_numFROMclassascINNERJOIN(selectclass_id,count(stu_id)asdel_numfromdeletedgroupbyclass_id)asdONc.class_id=d.class_id•UPDATE语句分为两个步骤：DELETE步骤捕获数据的前像，INSERT步骤捕获数据的后像•当在定义了触发器的表上执行UPDATE语句的时候，原行（前像）被移到deleted表中，而更新的行（后像）则插入inserted表中UPDATE触发器的工作方式示例1：在定义了update触发器的表上执行update语句updatestudentsetclass_id='0703'wherestu_id='0704004'stu_idstu_namestu_sexstu_birthdayclass_id0701001李玉女1987-05-0607010701002鲁敏女1988-06-280701……………0704004季风男1987-09-210704deleted0704004季风男1987-09-2107040703inserted0704004季风男1987-09-210703触发器代码CREATETRIGGERstu_deleteONstudentAFTERUPDATEASdeclare@classid1char(6),@classid2char(6)select@classid1=class_idfromdeletedselect@classid2=class_idfrominsertedUPDATEclassSETclass_num=class_num-1whereclass_id=@classid1UPDATEclassSETclass_num=class_num+1whereclass_id=@classid2UPDATEclassSETclass_num=class_num-1whereclass_id=@classid1UPDATEclassSETclass_num=class_num+1whereclass_id=@classid2class_idclass_nameclass_num0701软件开发40702计算机办公应用20703网络技术10704数字媒体423监视对特定列的更新•语法：IFUPDATE(列名)•允许触发器监测特定列，以对特定列的更新作出反应。例如发出不允许对列更新的错误信息，或者对新更新的列值进行处理UPDATE触发器的工作方式UPDATESCORESETSCORE=98WHEREStu_id='0701001'CREATETRIGGERsc_updateONscoreFORUPDATEASIFUPDATE(score)BEGINTRANSACTIONRAISERROR(‘学生成绩不能修改',10,1)ROLLBACKTRANSACTION错误信息：学生成绩不能修改示例2：在定义了update触发器的表上执行update语句