`
sjk2013
  • 浏览: 2245750 次
文章分类
社区版块
存档分类
最新评论

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

 
阅读更多

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

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

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

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

传回值的区别:

outputreturn都可在批次程式中用变量接收,recordset则传回到执行批次的客户端中

#创建一个存储过程,实现将表一和表二合并,该表只含Student_IDNamesexresult,将临时表存放在存储过

程中。

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联机丛书的索引中查找可以解决我们的一切矛盾。


分享到:
评论

相关推荐

    数据库原理与技术触发器和存储过程

    在数据库系统中,存储过程和触发器是两个非常重要的概念,它们极大地增强了数据库的功能和灵活性。 **存储过程(Stored Procedures)** 存储过程是预编译的SQL语句集合,存储在数据库服务器上并可重复调用。它们...

    数据库设计包括表、视图、触发器、存储过程、序列、索引等.doc

    在数据库设计中,主要对象包括表、视图、触发器、存储过程、序列、索引等。这些对象都是数据库设计的核心组件,各自扮演着不同的角色,以下是对每个对象的详细解释: 1. 表(Table) 表是数据库中最基本的存储结构...

    数据库存储过程+触发器实例+权限构架

    数据库存储过程和触发器是数据库管理系统中的重要组成部分,它们在数据操作和业务逻辑控制中扮演着关键角色。权限构架则是确保系统安全性和数据完整性的重要机制。以下将详细阐述这三个核心概念及其应用。 首先,...

    数据库触发器是一种存储过程

    数据库触发器是数据库管理系统中的一种重要机制,它们本质上是预定义的存储过程,可以在特定的数据库操作(如INSERT、UPDATE、DELETE)发生时自动执行。SQL Server提供了对触发器的支持,允许用户根据需要创建和定制...

    MYSQL数据库的索引、视图、触发器、游标和存储过程.pdf

    在MySQL中,索引、视图、触发器、游标和存储过程是数据库设计和开发中经常使用的关键特性,它们极大地增强了数据库的功能和灵活性。 索引是数据库中用来快速寻找特定数据行的数据库对象。在MySQL中,索引可以极大地...

    SQL Server 2005编程入门经典-触发器和存储过程教程

    《SQL Server 2005编程...总结,SQL Server 2005的触发器和存储过程是数据库开发中的重要组成部分,熟练掌握它们有助于提升数据库管理和应用程序的性能。通过不断学习和实践,你可以成为一个精通SQL Server的开发者。

    SQL网络数据库中存储过程、触发器及其应用方法.pdf

    在数据库系统设计中,考虑到存储过程和触发器在数据处理方面的重要作用,开发者应当精心设计并合理使用这两种数据库对象。正确地应用存储过程和触发器可以使数据库应用程序在性能、安全性和易维护性方面得到显著的...

    mysql数据库程序设计练习题.docx

    本资源摘要信息主要对MySQL数据库程序设计进行了详细的介绍和练习题的设计。该资源涵盖了数据库系统的核心、SQL语言的功能、关系数据库设计、事务控制、数据一致性、安全性等多方面的知识点。 数据库系统的核心 ...

    数据库存储过程与触发器PPT教案.pptx

    存储过程是数据库管理系统中一种重要的程序设计元素,它是由一组预先编译好的SQL语句和控制流语句组成的。在SQL Server中,存储过程可以分为系统存储过程(通常以"sp_"开头,存储在Master数据库中)和用户自定义存储...

    存储过程和触发器的编程

    总的来说,存储过程和触发器是数据库管理和应用程序开发中的强大工具,它们可以帮助我们实现更高效、更安全的数据操作。掌握这两者,对于提升数据库性能和实现复杂业务逻辑至关重要。在学习过程中,可以参考相关文档...

    二级openGauss数据库程序设计考试大纲(2022年版)1

    【全国计算机等级考试二级openGauss数据库程序设计考试大纲(2022年版)】主要涵盖以下几个核心知识点: 1. **数据库基本概念与方法**: - **数据库系统**:理解其基本概念,包括数据库、数据库管理系统、数据库...

    SQL数据库程序设计

    表是由行和列构成的二维表,视图是由表或其他视图导出的虚拟表,索引用于快速查询,约束用于为表中的列定义完整性的规则, 默认值是为列提供的默认值,存储过程是存放于服务器的 T-SQL 语句,触发器是特殊的存储过程...

    数据库课程设计-仓库管理系统

    4. **T-SQL语言**:SQL Server使用T-SQL(Transact-SQL)进行数据操作,包括数据的增删改查(INSERT、DELETE、UPDATE、SELECT)、事务处理、存储过程和触发器等。 5. **VS2010**:Visual Studio 2010是微软的集成...

    第八单元 存储过程和触发器

    在数据库管理中,存储过程和触发器是两个关键概念,它们极大地增强了SQL Server数据库的效率和安全性。让我们深入探讨这两个主题。 首先,我们来看“存储过程”。存储过程是预编译的SQL语句集合,存储在数据库...

    SQL Server数据库中使用触发器经验谈

    - **过度使用**:不应过度依赖触发器来处理业务逻辑,更推荐使用应用程序代码或约束来实现,以保持数据库设计的清晰和高效。 6. **示例**: - INSERT触发器示例:创建一个触发器,每当有新订单(向`Order Details...

    数据库的存储过程和触发器

    总的来说,存储过程和触发器是数据库设计和开发中的重要工具,它们允许数据库管理员和开发者更精细地控制数据处理和业务逻辑,同时提高系统的性能和数据的准确性。理解并熟练使用这两者对于优化数据库应用至关重要。

    Orcal 数据库触发器文件

    - 触发器应该作为最后的手段,当其他数据库约束、存储过程或应用程序逻辑无法满足需求时使用。 - 为触发器编写详尽的文档,以便其他开发人员理解其功能和可能的影响。 6. 替代方案: - 使用应用程序层的逻辑,将...

    数据库系统概论——存储过程和触发器资料.ppt

    以上是关于"数据库系统概论——存储过程和触发器"的详细内容,涵盖存储过程和触发器的定义、用途、Transact-SQL编程基础以及相关概念。这些知识点在数据库设计和管理中扮演着核心角色,是数据库开发和维护人员必备的...

Global site tag (gtag.js) - Google Analytics