第12章 触发器PPT幻灯片.ppt

第12章触发器,1,第12章触发器,案例1,李四晋级后，薪水也需调整,触发器可以完成自动调整,2,第12章触发器,案例2,删除学生时，一并删除学生的成绩信息；,触发器可以完成自动判断,3,12.1触发器概述,触发器就像一枚地雷，被特定事件触发时，就会发生一系列动作。触发器实际上就是一种特殊类型的存储过程只有在系统执行某些特定的T-SQL语句或操作时，触发器才会自动执行。,4,12.1触发器概述功能,触发器的常用功能如下：1完成更复杂的数据约束2检查所做的SQL所作的操作是否允许3修改其它数据表里的数据4调用更多的存储过程5返回自定义的错误信息6更改原本要操作的SQL语句7防止数据表结构被更改或数据表被删除8审核和控制服务器会话,5,12.1触发器概述,触发器的类型1DML触发器（select、insert、update、delete触发）DML触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。2DDL触发器（create、drop、alter触发）DDL触发器是在响应数据定义语言事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。3登录触发器登录触发器将为响应LOGON事件而激发存储过程。与SQLServer实例建立用户会话时将引发此事件。,6,12.2DML触发器,DML触发器的类型1After触发器：这类触发器是在记录已经改变之后,才会被激活执行，它主要是用于记录变更后的处理或检查。2InsteadOf触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作，而去执行触发器本身所定义的操作。,7,12.2DML触发器两个特殊的系统表,为了保存DML操作之前的数据和操作之后的数据，系统提供了两个特殊的表。SQLServer为每个DML触发器都定义了两个特殊的表，一个是INSERTED表，一个是DELETED表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。这两个表的结构与触发器所在数据表的结构完全一致，当触发器的工作完成之后，这两个表也将会从内存中删除。,8,12.2DML触发器两个特殊的系统表,对于插入记录操作来说，INSERTED表里存放的是要插入的数据；对于更新记录操作来说，INSERTED表里存放的是更新后的记录。对于更新记录操作来说，DELETED表里存放的是更新前的记录；对于删除记录操作来说，DELETED表里存入的是被删除的旧记录。,9,12.2DML触发器用T-SQL语言创建触发器,1创建AFTER触发器的语法如下：CREATETRIGGERschema_name.trigger_nameONtable|viewWITHENCRYPTION|EXECUTEASFOR|AFTERINSERT,UPDATE,|EXTERNALNAME,10,12.2DML触发器用T-SQL语言创建触发器,【例12.1】修改Teacher（教师）表中的数据时，通过触发器向客户端显示一条消息。CREATETRIGGERteacher_update_messageONTeacherAFTERUPDATEASBEGINRAISERROR(注意：有人修改教师表中的数据！,16,10)ENDGO,11,12.2DML触发器用T-SQL语言创建触发器,【例12.2】删除Teacher（教师）表中的记录时，通过触发器删除CourseTeacher（课程教师）表中和该教师相关的记录。CREATETRIGGERteacher_delete_courseONTeacherAFTERDELETEASBEGINDELETEFROMCourseTeacherWHEREteacherIDin(SELECTteacherIDfromdeleted)ENDGO,12,12.2DML触发器用T-SQL语言创建触发器,创建INSTEADOF触发器InsteadOf触发器是在SQLServer服务器接到执行SQL语句请求后，激活InsteadOf触发器，至于该SQL语句本身的操作，系统直接忽略。,13,12.2DML触发器用T-SQL语言创建触发器,创建INSTEADOF触发器的语法代码如下：CREATETRIGGERschema_name.trigger_nameONtable|viewWITHENCRYPTION|EXECUTEASINSTEADOFINSERT,UPDATE,|EXTERNALNAME,和After触发器相比，仅将此处改为InsteadOf,14,12.2DML触发器用T-SQL语言创建触发器,【例12.3】当有人试图修改课程表中的数据时，利用触发器跳过修改数据的SQL语句（防止数据被修改），并向客户端显示一条消息。CREATETRIGGERcourse_updateONcourseINSTEADOFUPDATEASBEGINRAISERROR(警告：你无权修改课程表中的数据！,16,10)ENDGO,15,12.2DML触发器用T-SQL语言创建触发器,对触发器进行测试：SELECT*FROMCourseWHEREcourseID=2UPDATECourseSETtotalperiod=80WHEREcourseID=2SELECT*FROMCourseWHEREcourseID=2在“结果”对话框里显示了两个完全一样的记录说明UPDATE语句没有发生作用。,16,12.2DML触发器查看触发器,查看DML触发器有两种方式：通用ManagementStudio来查看选中某一触发器，然后双击即可查看该触发器的信息。利用系统存储过程来查看（1）系统存储过程sp_help可以了解如触发器名称、类型、创建时间等基本信息，其语法格式为：sp_help触发器名例如：sp_helpteacher_insert,17,12.2DML触发器查看触发器,（2）sp_helptext系统存储过程sp_helptext可以查看触发器的文本信息，其语法格式为：sp_helptext触发器名例如：sp_helptextteacher_insert,18,12.2DML触发器修改触发器,修改触发器的语法代码如下：ALTERTRIGGERONtable|viewWITHENCRYPTION|EXECUTEASFOR|AFTER|INSTEADOFINSERT,UPDATE,|EXTERNALNAME分析上述语法代码可以发现，修改触发器语法中所涉及到主要参数和创建触发器的主要参数几乎一样，在此不再赘述。,19,12.2DML触发器修改触发器,1.禁用DML触发器使用AlterTable语句禁用DML触发器的语法如下：Altertable数据表名Disabletrigger触发器名或ALL如果要禁用所有触发器，用“ALL”来代替触发器名。2启用DML触发器使用AlterTable语句也可以启用触发器，其语法如下：Altertable数据表名Enabletrigger触发器名或ALL如果要启用所有触发器，用“ALL”来代替触发器名。,20,12.2DML触发器修改触发器,1.删除DML触发器用SQL语句也可删除触发器，删除触发器的语法代码如下所示：DropTrigger触发器名例子删除触发器teacher_updateDroptriggerteacher_update,21,22,表1Student表结构,表2Course表结构,小测验,表3SC表结构,23,1.创建一个视图vw_stu_avg：查询每个学生的平均成绩，要求列出学生的姓名、学号、平均成绩。2.创建一个唯一索引，该索引依赖于数据表Student的Sname字段。3.创建一个名为pro_get_avg的存储过程，该存储过程能够根据用户指定的Cname（课程名）求出该课程的平均成绩。执行该存储过程。,24,回顾,1.触发器的定义，触发器和存储过程的区别。2.触发器的功能。3.触发器的分类。4.触发器有哪几种临时表，里面存什么内容。5.DML触发器的分类。6.DML触发器中After触发器的创建。7.DML触发器中Insteadof触发器的创建。,12.3DDL触发器,DDL触发器像常规触发器一样，DDL触发器将激发存储过程以响应事件。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。而是为响应以CREATE、ALTER和DROP开头的语句。,25,12.3DDL触发器,一般来说，在以下几种情况下可以使用DDL触发器：（1）防止数据库架构进行某些修改。（2）防止数据库或数据表被误操作删除。（3）要记录数据库架构中的更改或事件。仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。DDL触发器只能作为AFTER触发器使用。,26,12.3DDL触发器,创建DDL触发器的语法代码如下：CREATETRIGGERONALLSERVER|DATABASEWITH,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNALNAME;:=ENCRYPTIONEXECUTEASClause:=assembly_name.class_name.method_name,27,12.3DDL触发器,【例12.4】建立用于保护CJGL数据库中的数据表不被删除的触发器。具体操作步骤如下：（1）启动“SQLServerManagementStudio”，在“对象资源管理器”下选择“数据库”，定位到“CJGL”数据库。（2）单击“新建查询”按钮，在弹出的“查询编辑器”的编辑区里输入以下代码：CREATETRIGGERdisable_drop_tableONDATABASEFORDROP_TABLEASBEGINRAISERROR(对不起，不能删除CJGL数据库中的数据表,16,10)ROLLBACKENDGO（3）单击“执行”按钮，生成触发器。,28,12.3DDL触发器,测试DDL触发器的功能，具体操作步骤如下：（1）启动“SQLServerManagementStudio”，在“对象资源管理器”下选择“数据库”，定位到“CJGL”数据库。（2）单击“新建查询”按钮，在弹出的“查询编辑器”的编辑区里输入以下代码：DROPTABLETeacher（3）单击“执行”按钮，运行结果如图所示。,29,12.3DDL触发器,查看DDL触发器1作用在当前SQLServer服务器上的DDL触发器所在位置选择所在SQLServer服务器，定位到“服务器对象”，“触发器”，在“摘要”对话框里就可以看到所有的作用在当前SQLServer服务器上的DDL触发器2作用在当前数据库中的DDL触发器所在位置选择所在SQLServer服务器，找到特定的“数据库”，定位到“可编程性”，然后在“数据库触发器”树形目录中可以看到所有的当前数据库中的DDL触发器,30,12.4登录触发器,登录触发器将为响应LOGO