数据库程序设计中的约束、触发器和存储过程

       上篇博客中所说的对于表操作的几种限制少分析了触发器。这次从对表设计的角度来着重分析约束和触发器的关系，并进一步扩展比较触发器和存储过程。但在看该篇博客前强烈建议大家好好读下我的上一篇博客《约束与数据库对象规则、默认值的探究》

       首先，从图上来比较三者的关系：

       触发器不仅能够保证数据的完整性，而且还可以封装复杂的T-SQL逻辑处理语句，在功能上类似于存储过程，所

以触发器又是一种特殊的存储过程。但是存储过程的执行是我们使用Exec主观调用的，而触发器是经过一种事件操作

后自动被调用的。

      在拆开分析约束和触发器、触发器和存储过程之前我们穿插点外话。在数据库程序设计中包含有多种数据模型：

20世纪60年代后期，在文件系统基础上发展起来的层次模型、网状模型和关系模型等传统数据模型；20世纪70年代

后期产生的E－R数据模型；20世纪80年代以来又相继推出面向对象数据模型、基于逻辑的数据模型等新的模型。下

图关系数据库中的关键术语和语义对象模型及ER图中使用的术语之间的映射关系：

 

数据库中正式术语

文件

SOM

E-R

表

关系

文件

类

实体集合

行

元组

记录

对象

实体

列

属性

域

属性

属性

 

         上面的内容只存在了解而已，不用深究。

●数据完整性和业务规则

          在上篇博客我已经简单介绍了数据完整性，接下来我们详细说下数据完整性和业务规则。

      一、数据完整性

              数据完整性=可靠性+准确性，这里我们要清楚一下两点：

                  •    数据存放在表中

                  •   创建表的时候，就应当保证以后数据输入是正确的（错误的数据、不符合要求的数据不允许输入）

              为了保证数据的完整性我们经常使用完整性约束来确保数据的完整性。数据完整性，主要包括下面四部分：

      二、业务规则

            业务规则听起来很难理解，当然它也是值得我们深究东西，通俗的讲它其实是符合实际条件。如：某商店规定

一个售货员在一个月内售出10个以上的热浴盆，那么奖励2000元；某公司的订单上必须含有客户的姓名和联系方式等

等，这些都是简单的业务规则。从数据库的角度看，业务规则就是约束。

●约束和触发器

         MS SQL Server提供了两种主要的机制进行强制业务规则和数据的完整性：约束和触发器。在作用上约束支持的触

发器都可以实现，它们两者是相容的关系，如下图。虽然两者在作用关系上有重合的地方，但是相较两者的执行效率

和维护难易来说，触发器是远远不如约束的。所以约束能实现的情况下编程人员是不会选择触发器的。

                                                                                       

         一、约束，上篇博客我已经着重讲解了约束的概念，这里不再深究。

                     SQL Server中存在五种约束：

                             •  约束的目的：确保表中数据的完整型

                             •  常用的约束类型：

                                   –        主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空

                                   –        唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。

                                   –        检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束

                                   –        默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男”

                                   –        外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列

            二、触发器，首先在下表中来看触发器的基本结构。

              触发器是一种对表进行插入、删除、更改的时候自动运行的特殊的存储过程。它一般用在比核查约束更为复杂

的约束中。但能用约束实现的功能，一般不用触发器。

           接下来我们从代码中认识下几种触发器。

       --#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       

                 注意：在创建触发器时，创建触发器必须是批处理的第一行，存储过程也是如此。

     --# 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;
  # 显示自定义消息raiserror

   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.用户自定义的存储过程,这是我们所指的存储过程     

                     常用格式

Create PRocedure procedue_name   [@parameter data_type][output]   [with]{recompile|encryption}   as        sql_statement--解释:  --output：表示此参数是可传回的--with {recompile|encryption}--recompile:表示每次执行此存储过程时都重新编译一次--encryption:所创建的存储过程的内容会被加密

 

举例：

       有如下表量表

result_Info：

 

Student_ID

Name

result

1

张红

70

2

马力

80

       Student_Info

Student_ID

Sex

Grade

1

女

一年级

2

男

二年级

 

      #创建返回参数的存储过程

If 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，将临时表存放在存储过

程中。

If exists(select name from sysobjects where name=’proc_save’ and type=’P’)Drop proc proc_returnGoCreate proc proc_saveAsSelect r.student_ID,r.Name,r.result,s.sex into #temptable from result r inner join student s on                 r.student_ID=s.student_IDIf @@error=0Print ‘Successed’ElsePrint ‘Failed’Go

          存储过程的应用类型还有很多，这里我只介绍了在编程时常用的两种。

●总结

       在进行数据库程序设计时，数据的完整性是编程人员必须要考虑的，但是有时候这些知识的细节却让我们纠结的

很，搞不清改用哪个。总之吧：能用存储过程实现的不用触发器；能用约束实现的不用触发器，约束和存储过程用哪

个都可以。

        有些不懂得地方在SQL Server中按F1，在SQL Server联机丛书的索引中查找可以解决我们的一切矛盾。