开发者社区> 玄学酱> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

  首先,从图上来比较三者的关系:

  触发器不仅能够保证数据的完整性,而且还可以封装复杂的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>

  二、业务规则

  业务规则听起来很难理解,当然它也是值得我们深究东西,通俗的讲它其实是符合实际条件。如:某商店规定一个售货员在一个月内售出10个以上的热浴盆,那么奖励2000元;某公司的订单上必须含有客户的姓名和联系方式等等,这些都是简单的业务规则。从数据库的角度看,业务规则就是约束。

  约束和触发器

  MS SQL Server提供了两种主要的机制进行强制业务规则和数据的完整性:约束和触发器。在作用上约束支持的触发器都可以实现,它们两者是相容的关系,如下图。虽然两者在作用关系上有重合的地方,但是相较两者的执行效率和维护难易来说,触发器是远远不如约束的。所以约束能实现的情况下编程人员是不会选择触发器的。

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

  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

  存储过程的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_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/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
编写Java程序,在维护英雄数据的业务过程中复用数据库操作封装类
编写Java程序,在维护英雄数据的业务过程中复用数据库操作封装类
24 0
数据库中的表如何设置约束?(精简版)
数据库中的表如何设置约束?(精简版)
164 0
程序设计中的设计
通常我们所谓的程序设计,其实包含编码与设计两个部分,编码行为一般为人们所熟知,被称为coding,然而设计过程却容易为大家所忽视。
762 0
《C语言程序设计》一2.2 常量、数据的存储与数据类型
本节书摘来自华章出版社《C语言程序设计》一 书中的第2章,第2.2节,作者:赵宏 陈旭东 马迪芳,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1038 0
面向过程的程序设计
      一,设计的任务             在软件的需求分析阶段,已经把软件是“做什么”的问题搞清了,并且这些需求都最后归结为文档了,从而得出软件系统设计的逻辑模型。在设计阶段的任务,就是要把这个系统的逻辑模型转变成“怎么做”的物理模型。
830 0
+关注
玄学酱
这个时候,玄酱是不是应该说点什么...
20683
文章
438
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载