触发器全解析.doc

据库程序设计中的约束、触发器和存储过程 分类： 数据库技术 2012-08-16 21:41 3020人阅读 评论(35) 收藏 举报 上篇博客中所说的对于表操作的几种限制少分析了触发器。这次从对表设计的角度来着重分析约束和触发器的关系，并进一步扩展比较触发器和存储过程。但在看该篇博客前强烈建议大家好好读下我的上一篇博客约束与数据库对象规则、默认值的探究 首先，从图上来比较三者的关系： 触发器不仅能够保证数据的完整性，而且还可以封装复杂的T-SQL逻辑处理语句，在功能上类似于存储过程，所以触发器又是一种特殊的存储过程。但是存储过程的执行是我们使用Exec主观调用的，而触发器是经过一种事件操作后自动被调用的。 在拆开分析约束和触发器、触发器和存储过程之前我们穿插点外话。在数据库程序设计中包含有多种数据模型：20世纪60年代后期，在文件系统基础上发展起来的层次模型、网状模型和关系模型等传统数据模型；20世纪70年代后期产生的ER数据模型；20世纪80年代以来又相继推出面向对象数据模型、基于逻辑的数据模型等新的模型。下图关系数据库中的关键术语和语义对象模型及ER图中使用的术语之间的映射关系：数据库中正式术语文件SOME-R表关系文件类实体集合行元组记录对象实体列属性域属性属性 上面的内容只存在了解而已，不用深究。数据完整性和业务规则 在上篇博客我已经简单介绍了数据完整性，接下来我们详细说下数据完整性和业务规则。一、数据完整性 数据完整性=可靠性+准确性，这里我们要清楚一下两点： 数据存放在表中 创建表的时候，就应当保证以后数据输入是正确的（错误的数据、不符合要求的数据不允许输入） 为了保证数据的完整性我们经常使用完整性约束来确保数据的完整性。数据完整性，主要包括下面四部分： 二、业务规则 业务规则听起来很难理解，当然它也是值得我们深究东西，通俗的讲它其实是符合实际条件。如：某商店规定一个售货员在一个月内售出10个以上的热浴盆，那么奖励2000元；某公司的订单上必须含有客户的姓名和联系方式等等，这些都是简单的业务规则。从数据库的角度看，业务规则就是约束。约束和触发器MS SQL Server提供了两种主要的机制进行强制业务规则和数据的完整性：约束和触发器。在作用上约束支持的触发器都可以实现，它们两者是相容的关系，如下图。虽然两者在作用关系上有重合的地方，但是相较两者的执行效率和维护难易来说，触发器是远远不如约束的。所以约束能实现的情况下编程人员是不会选择触发器的。 一、约束，上篇博客我已经着重讲解了约束的概念，这里不再深究。 SQL Server中存在五种约束： 约束的目的：确保表中数据的完整型 常用的约束类型：主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列 二、触发器，首先在下表中来看触发器的基本结构。 触发器是一种对表进行插入、删除、更改的时候自动运行的特殊的存储过程。它一般用在比核查约束更为复杂的约束中。但能用约束实现的功能，一般不用触发器。 接下来我们从代码中认识下几种触发器。sql view plaincopyprint?1. -#Update型触发器 2. Ifexits(selectnamefromsysobjectswherename=tgr_update)3. Droptriggertgr_update4. Go5. Createtriggertgr_updateonstudent6. forupdate7. As8. If(Update(student_ID)9. Print更改成功！10. Else11. Begin12. Raiserror(系统提示：更新发生错误,16,1)13. Rollbacktran14. End15. Go16. -测试 17. Updatestudentsetstudent_ID=10002wherestudent_ID=10001 -#Update型触发器If exits(select name from sysobjects where name=tgr_update)Drop trigger tgr_updateGoCreate trigger tgr_update on studentfor updateAsIf (Update(student_ID)Print 更改成功！ElseBegin Raiserror(系统提示：更新发生错误,16,1)Rollback tranEndGo-测试Update student set student_ID=10002 where student_ID=10001 注意：在创建触发器时，创建触发器必须是批处理的第一行，存储过程也是如此。html view plaincopyprint?1. -#insteadof触发器2. if(object_id(tgr_classes_inteadOf,TR)isnotnull)3. droptriggertgr_classes_inteadOf4. go5. createtriggertgr_classes_inteadOf6. onclasses7. insteadofdelete/*,update,insert*/8. as9. declareidint,namevarchar(20);10. -查询被删除的信息，病赋值11. selectid=id,name=namefromdeleted;12. printid:+convert(varchar,id)+,name:+name;13. -先删除student的信息14. deletestudentwherecid=id;15. -再删除classes的信息16. deleteclasseswhereid=id;17. print删除id:+convert(varchar,id)+,name:+name+的信息成功！;18. go19. -test20. select*fromstudentorderbyid;21. select*fromclasses;22. deleteclasseswhereid=7;23. -# instead of 触发器 if (object_id(tgr_classes_inteadOf, TR) is not null) drop trigger tgr_classes_inteadOf go create trigger tgr_classes_intead Of on classes instead of delete/*, update, insert*/ as declare id int, name varchar(20); -查询被删除的信息，病赋值 select id = id, name = name from deleted; print id: + convert(varchar, id) + , name: + name; -先删除student的信息 delete student where cid = id; -再删除classes的信息 delete classes where id = id; print 删除 id: + convert(varchar, id) + , name: + name + 的信息成功！; go -test select * from student order by id; select * from classes; delete classes where id = 7; # 启用、禁用触发器sql view plaincopyprint?1. -禁用触发器 2. disabletriggertgr_messageonstudent;3. -启用触发器 4. enabletriggertgr_messageonstudent;5. #显示自定义消息raiserror -禁用触发器 disable trigger tgr_message on student; -启用触发器 enable trigger tgr_message on student; # 显示自定义消息raiserrorsql view plaincopyprint?1. if(object_id(tgr_message,TR)isnotnull)2. droptriggertgr_message3. go4. createtriggertgr_message5. onstudent6. afterinsert,update7. asraisError(tgr_message触发器被触发,16,10);8. go9. -test 10. insertintostudentvalues(lily,22,1,7);11. updatestudentsetsex=0wherename=lucy;12. select*fromstudentorderbyid;13. if (object_id(tgr_message, TR) is not null) drop trigger tgr_message go create trigger tgr_message on student after insert, update as raisError(tgr_message触发器被触发, 16, 10); go -test insert into student values(lily, 22, 1, 7); update student set sex = 0 where name = lucy; select * from student order by id; 触发器的应用种类繁多上面的几个示例都是比较常用的，当然最好的熟练方法就是多用，多练。触发器和存储过程 触发器是一种特殊的存储过程，不是由用户直接调用。而存储过程是一组T-SQL语句，经过编译后可以被多次调用。类似于其它编程语言中的过程。它可以接收输入参数、输出参数、 返回单个或多个结果集以及返回值。 存储过程分为三类： 1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作, 如 sp_help就是取得指定对象的相关信息 2.扩展存储过程以XP_开头,用来调用操作系统提供的功能 exec master.xp_cmdshell ping 10.8.16.1 3.用户自定义的存储过程,这是我们所指的存储过程 常用格式delphi view plaincopyprint?1. CreatePRocedureprocedue_name2. parameterdata_typeoutput3. withrecompile|encryption4. as5. sql_statement6. -解释:7. -output：表示此参数是可传回的8. -withrecompile|encryption9. -recompile:表示每次执行此存储过程时都重新编译一次10. -encryption:所创建的存储过程的内容会被加密Create PRocedure procedue_name parameter data_typeoutput withrecompile|encryption as sql_statement-解释: -output：表示此参数是可传回的-with recompile|encryption-recompile:表示每次执行此存储过程时都重新编译一次-encryption:所创建的存储过程的内容会被加密举例： 有如下表量表result_Info： Student_IDNameresult1张红70 2马力80 Student_InfoStudent_IDSexGrade1女一年级2男二年级 #创建返回参数的存储过程sql view plaincopyprint?1. Ifexists(selectnamefromsysobjectswherename=proc_returnandtype=P)2. Dropprocproc_return3. Go4. Createprocproc_return5. param1int,6. param2char(10),7. param3char(10)8. param4intoutput9. Withencryption-加密 10. As11. Insertintostudent_Info(student_ID,name,result)values(param1,param2,param3)12. Selectparam4=sum(result)fromstudent_Info13. Print总分为：&convert(char,param)14. Go15. -调用测试 16. Declaresumresultint17. Execproc_return12,王刚,80,sumresult18. GoIf exists(select name from sysobjects where name=proc_return and type=P)Drop proc proc_returnGoCreate proc proc_return param1 int,param2 char(10),param3 char(10)param4 int outputWith encryption -加密AsInsert into student_Info(student_ID,name,result) values(param1,param2,param3)Select param4=sum(result) from student_InfoPrint 总分为： & convert(char,param)Go-调用测试Declare sumresult intExec proc_return 12,王刚,80,sumresultGo 存储过程的3种传回值: 1.以Return传回整数 2.以output格式传回参数 3.Recordset 传回值的区别: output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中 #创建一个存储过程，实现将表一和表二合并，该表只含Student_ID、Name、sex、result，将临时表存放在存储过程中。sql view plaincopyprint?1. Ifexists(sel