第四章数据库完整性--触发器

第四章数据库完整性 触发器 学习重点 掌握触发器的创建 掌握触发器的删除 修改 执行方法 掌握管理和维护触发器的方法 内容提要 概述 触发器的创建与维护 利用企业管理器管理和维护触发器 一 内容回顾 学生表 课程表 成绩表 1 表格内部的完整性约束2 表和表之间的完整性约束3 用户定义的完整性约束 主键 主键 主键 外键 外键 主键特性 唯一 不能为空外键特性 对应的值在主键中必须存在 问题 1 王皓为同学退学 需删除相应记录 如何操作2 张小天通过一门新课考试 如何修改他的已修学分 概述 二 使用触发器的优点 触发器是一种特殊类型的存储过程 自动执行 在对表的数据作了任何修改之后立即被激活 生效 UPDATE INSERT或DELETE 触发器实现级联删除与级联修改 触发器可强制实现复杂的业务规则或要求 触发器还有助于强制数据完整性 三 创建触发器语法 CREATETRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF INSERT UPDATE WITHAPPEND NOTFORREPLICATION AS IFUPDATE column AND OR UPDATE column n IF COLUMNS UPDATED bitwise operator updated bitmask comparison operator column bitmask n sql statement n 触发器实例分析 实例1 创建一个简单触发器 能在对数据表student进行插入 修改或删除后 输出所影响的行数信息 createtriggersimple trionstudentafterinsert delete updateasprint 所影响的行数为 cast rowcountasvarchar 10 行 该触发器何时被触发 当对student执行insertdeleteupdate操作的时候 实例2 创建级联删除触发器为表student创建一个删除触发器 当删除表student中的一个学生信息时 同时将表sc中相关的成绩信息一并删除 方法一 使用外键Createtablesc snochar 8 foreignkeyreferencesstudent sno ondeletecascade cnochar 4 foreignkeyreferencescourse cno ondeletecascade gradeintcheck grade 0andgrade 100 primarykey sno cno 方法二 使用触发器 前提是SC表没有定义和student表相关的外键createtriggerstu delonstudentfordeleteasBeginBegintransactiondeclare stu snochar 8 select stu sno deleted snofromdeleteddeletefromscwheresc sno stu snoCommittranEnd 原子事务处理方法 使用inserted和deleted表 重要 触发器语句中使用了两种特殊的表 deleted表和inserted表 由DBMS自动创建和管理这些表 可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件 然而 不能直接对表中的数据进行更改 Deleted表用于存储DELETE和UPDATE语句所影响的行的复本 在执行DELETE或UPDATE语句时 行从触发器表中删除 并传输到deleted表中 Deleted表和原数据表通常没有相同的行 思考 表结构如何 Inserted表用于存储INSERT和UPDATE语句所影响的行的副本 在一个插入或更新事务处理中 新建行被同时添加到inserted表和原数据表中 Inserted表中的行是原数据表中新行的副本 思考 如何实现级联修改 思考 该触发器 对同时插入或修改多条记录时 有何操作结果 使触发器更具有通用性 Createtriggerstu delonstudentfordeleteasdeletefromscwheresc snoin selectsnofromdeleted 实例3 insertintoscvalues 2006002 c001 80 如何修改他的已修学分 实例3 insertintoscvalues 2006002 c001 80 如何修改他的已修学分 思路 1 触发器被触发的时候 内存中是deleted表还是inserted表 该表的结构如何 2 找到刚插入记录的学号和课程号declare c nochar 4 s nochar 8 select c no cno s no snofrominserted 3 找出该课程的学分declare cditintselect cdit creditfromcoursewherecno c no 4 修改该同学的已修学分updatestudentsetcredit credit cditwheresno s no 实例3 insertintoscvalues 2006002 c001 80 如何修改他的已修学分 Createtrigerupdate creditOnSCAfterinsertAsBegindeclare c nochar 4 s nochar 8 cditintselect c no cno s no snofrominsertedselect cdit creditfromcoursewherecno c noupdatestudentsetcredit credit cditwheresno s noend 实例4 定义一个数据表modify sc 用于存储学生选课表中成绩数据发生变化的情况 其定义T SQL脚本如下 createtablemodify sc student nochar 8 null Course nochar 3 null user namechar 6 null timedatetimenull grade oldintnull grade newintnull 如果希望 当执行updatescsetgrade 90wheresno 2006001 andcno c002 自动在modify sc表中添加一条记录 利用user name 利用Getdate 思路 1 找出被修改的同学的学号2 找出被修改的课程号3 找出修改前的成绩4 找出修改后的成绩5 在modify sc表中插入记录 可以通过下列触发器实现 createtriggermodify sc1onscAFTERUPDATEAsIfupdate grade BeginDeclare grade oldtinyintDeclare grade newtinyintDeclare student nochar 8 Declare course nochar 3 Select grade old selectgradefromdeleted 修改前的成绩Select grade new selectgradefrominserted 修改后的成绩Select student no selectsnofromdeleted 被修改的同学的学号Select course no selectcnofromdeleted 被修改的同学的课程号Insertintomodify scvalues student no course no USER NAME GETDATE grade old grade new END 测试方法如执行 updatescsetgrade 90wheresno 2006001 andcno c002 查看表modify sc中的记录 select frommodify sc删除触发器 ifexists select fromsysobjectswherename modify sc1 andtype tr droptriggermodify sc1 实例5 当在SC表中插入记录或修改学号时 验证学号是否正确 如果不正确 取消插入或修改方法一 将SC表的sno设置为外键方法二 在SC表上必须没有设置外键 用触发器createtriggersc sno checkonscafterinsert updateASifupdate sno beginifNOTEXISTS select fromstudents insertediwheres sno i sno beginROLLBACKTRANSACTIONPrint 学号信息不在学生表中 修改 插入该行不成功 endElseprint 该行已被修改 插入 end 将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点 实例4 使用带有提醒消息的触发器当有人试图在sc表中添加或更改数据时 将向客户端显示一条消息 CREATETRIGGERreminderONscFORINSERT UPDATEASRAISERROR 50009 16 10 GO请问 1 RAISERROR函数用途 在遇到问题时 根据严重级别 将把sysmessages系统表中的消息 发送到客户端 2 如何自定义系统错误消息 Sp addmessage50009 16 请注意 有人试图对数据表进行数据的插入 修改操作 自定义的消息ID号 消息的严重级别 如严重级别11到16 这些消息表明错误可由用户修正 有关错误调用状态的信息 从1到127的任意整数 负值默认为1 是否成功 删除自定义消息 sp dropmessage50009 补充实例 在student表中 添加一列为sumgrade 记录每位学生的总分 写一个触发器 使得SC表中 某学生的课程成绩发生变化时 student表中该学生的sumgrade字段值能够自动更新为新值 思考 课程成绩发生变化包括了哪些情况 1 插入新课程成绩 2 删除了某课程成绩 3 修改了某课程成绩 1 在student表中添加新列usestudentaltertablestudentaddsumgradesmallint给sumgrade赋值 updatestudentsetsumgrade selectsum grade fromscwheresc sno student sno 2 创建触发器如下 createtriggersc changeonscforupdate insert deleteasbegintransactionifupdate grade beginupdatestudentsetsumgrade selectsum grade fromscwheresc sno student sno wheresnoin selectsnofrominserted end 删除已创建的触发器的另一种写法 ifobject id sc change isnotnulldroptriggersc change elsebeginupdatestudentsetsumgrade selectsum grade fromscwheresc sno student sno wheresnoin selectsnofromdeleted endcommittransaction 另一种方法 createtriggersc changeonscforupdate insert deleteasbegintransactionifexists select fromdeleted beginupdatestudentsetsumgrade selectsum grade fromscwheresc sno student sno wheresnoin selectsnofromdeleted end elsebeginupdatestudentsetsumgrade selectsum grade fromscwheresc sno student sno wheresnoin selectsnofrominserted endcommittransaction 查看触发器的内容 Hereisthestatementtoactuallyseethetextofthetrigger SELECTo id c textFROMsysobjectsoINNERJOINsyscommentscONo id c idWHEREo type TR ando name trig1 或Sp helptexttrig1 6 Insteadof触发器的实例分析 INSTEADOFINSERT触发器可以在视图或表上定义INSTEADOFINSERT触发器来代替INSERT语句的标准操作 通常 在视图上定义INSTEADOFINSERT触发器以在一个或多个基表中插入数据 INSTEADOFINSERT触发器执行过程 1 用户或系统运行insert语句 2 如果记录不违反任何限制 则只插入inserted表中 3 触发器触发和运行必要的操作 注 先检查后插入 实例1 对SC创建一个insteadof触发器 确保插入的学生的学号在student表中存在 注 进行插入数据时 先插入到inserted表中 再由所建的insteadof触发器执行实际的插入 usestudentcreatetriggersc sno check2onscinsteadofinsertasdeclare newsnochar 8 select newsno snofrominsertedifexists select fromstudentwheresno newsno insertintoscselect frominsertedelseprint sorry 学号不在学生表中 问 插入时为结果集时 该触发器是否有效 当其中有学号不满足条件时 将无效 思考1 如何改进 createtriggersc sno check2onscinsteadofinsertasifexists select frominsertedwheresnonotin selectsnofromstudent print sorry 有学号不在学生表中 elseinsertintoscselect frominserted 实例3 在视图上创建insteadof触发器 usetempdbCREATETABLEBaseTable IDintIDENTITY 1 1 自增长Colornvarchar 10 NOTNULL Materialnvarchar 10 NOTNULL ComputedColAS Color Material CREATEVIEWInsteadViewASSELECTID Color Material ComputedColFROMBaseTableGO CREATETRIGGERInsteadTriggeronInsteadViewINSTEADOFINSERTASBEGININSERTINTOBaseTableSELECTColor materialFROMinsertedEND测试 INSERTINTOInsteadView ID Color Material VALUES 3 N Red N Cloth 注 直接引用BaseTable的INSERT语句不能为ID和ComputedCol列提供值 下列语句正确INSERTINTOBaseTable Color Material VALUES N Red N Cloth 下列语句错误 INSERTINTOBaseTableVALUES 2 N Green N Wood N GreenWood 查看数据表所拥有的触发器的系统存储过程 sp helptriggerinsteadview 然而 通过InsteadView的INSERT语句必须为ID和ComputedCol列提供值 INSERTINTOInsteadView ID Color Material ComputedCol VALUES 999 N Blue N Plastic N XX