上篇博客中所说的对于表操作的几种限制少分析了触发器。这次从对表设计的角度来着重分析约束和触发器的关系，并进一步扩展比较触发器和存储过程。但在看该篇博客前强烈建议大家好好读下我的上一篇博客《约束与数据库对象规则、默认值的探究》
首先，从图上来比较三者的关系：
触发器不仅能够保证数据的完整性，而且还可以封装复杂的T-SQL逻辑处理语句，在功能上类似于存储过程，所以触发器又是一种特殊的存储过程。但是存储过程的执行是我们使用Exec主观调用的，而触发器是经过一种事件操作后自动被调用的。
在拆开分析约束和触发器、触发器和存储过程之前我们穿插点外话。在数据库程序设计中包含有多种数据模型：
20世纪60年代后期，在文件系统基础上发展起来的层次模型、网状模型和关系模型等传统数据模型；20世纪70年代后期产生的E－R数据模型；20世纪80年代以来又相继推出面向对象数据模型、基于逻辑的数据模型等新的模型。下图关系数据库中的关键术语和语义对象模型及ER图中使用的术语之间的映射关系：
上面的内容只存在了解而已，不用深究。
数据完整性和业务规则
在上篇博客我已经简单介绍了数据完整性，接下来我们详细说下数据完整性和业务规则。
一、数据完整性
数据完整性=可靠性+准确性，这里我们要清楚一下两点：
● 数据存放在表中
● 创建表的时候，就应当保证以后数据输入是正确的（错误的数据、不符合要求的数据不允许输入）
为了保证数据的完整性我们经常使用完整性约束来确保数据的完整性。数据完整性，主要包括下面四部分：
| --#Update型触发器 If exits(select name from sysobjects where name=’tgr_update’) Drop trigger tgr_update Go Create trigger tgr_update on student for update As If (Update(student_ID)) Print ‘更改成功！’ Else Begin Raiserror(‘系统提示：更新发生错误’,16,1) Rollback tran End Go --测试 Update student set student_ID=10002 where student_ID=10001 |
注意：在创建触发器时，创建触发器必须是批处理的第一行，存储过程也是如此。
| --# 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; |
| --禁用触发器 disable trigger tgr_message on student; --启用触发器 enable trigger tgr_message on student; <P style="BACKGROUND: white"><SPAN style="COLOR: #4b4b4b"> # </SPAN><SPAN style="COLOR: #4b4b4b">显示自定义消息</SPAN><SPAN style="COLOR: #4b4b4b">raiserror</SPAN></P> |
| 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; <SPAN style="BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial"></SPAN> |
一、约束，上篇博客我已经着重讲解了约束的概念，这里不再深究。
SQL Server中存在五种约束：
● 约束的目的：确保表中数据的完整型
● 常用的约束类型：
– 主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空
– 唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。
– 检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束
– 默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”
– 外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列
二、触发器，首先在下表中来看触发器的基本结构。
触发器是一种对表进行插入、删除、更改的时候自动运行的特殊的存储过程。它一般用在比核查约束更为复杂的约束中。但能用约束实现的功能，一般不用触发器。
触发器的应用种类繁多上面的几个示例都是比较常用的，当然最好的熟练方法就是多用，多练。
触发器和存储过程
触发器是一种特殊的存储过程，不是由用户直接调用。而存储过程是一组T-SQL语句，经过编译后可以被多次调用。类似于其它编程语言中的过程。它可以接收输入参数、输出参数、返回单个或多个结果集以及返回值。
存储过程分为三类：
1、系统存储过程：以sp_开头，用来进行系统的各项设定、取得信息。相关管理工作，如 sp_help就是取得指定对象的相关信息
2、扩展存储过程 以XP_开头，用来调用操作系统提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
3、用户自定义的存储过程，这是我们所指的存储过程
常用格式
| Create PRocedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement --解释: --output：表示此参数是可传回的 --with {recompile|encryption} --recompile:表示每次执行此存储过程时都重新编译一次 --encryption:所创建的存储过程的内容会被加密 |
举例：
有如下表量表
result_Info：
Student_Info
| If exists(select name from sysobjects where name=’proc_return’ and type=’P’) Drop proc proc_return Go Create proc proc_return @param1 int, @param2 char(10), @param3 char(10) @param4 int output With encryption --加密 As Insert into student_Info(student_ID,name,result) values(@param1,@param2,@param3) Select @param4=sum(result) from student_Info Print ‘总分为：’ & convert(char,@param) Go --调用测试 Declare @sumresult int Exec proc_return 12,’王刚’,80,@sumresult Go |
output和return都可在批次程式中用变量接收，而recordset则传回到执行批次的客户端中
#创建一个存储过程，实现将表一和表二合并，该表只含Student_ID、Name、sex、result，将临时表存放在存储过程中。
| If exists(select name from sysobjects where name=’proc_save’ and type=’P’) Drop proc proc_return Go Create proc proc_save As Select r.student_ID,r.Name,r.result,s.sex into #temptable from result r inner join student s on r.student_ID=s.student_ID If @@error=0 Print ‘Successed’ Else Print ‘Failed’ Go |
存储过程的应用类型还有很多，这里我只介绍了在编程时常用的两种。
有些不懂得地方在SQL Server中按F1，在SQL Server联机丛书的索引中查找可以解决我们的一切矛盾。
====================================分割线================================
最新内容请见作者的GitHub页：http://qaseven.github.io/