数据库数据库完整性触发器.ppt
《数据库数据库完整性触发器.ppt》由会员分享,可在线阅读,更多相关《数据库数据库完整性触发器.ppt(92页珍藏版)》请在课桌文档上搜索。
1、补充:1).T-SQL语言简介,T-SQL的存储过程:由SQL语句组成,分系统存储过程和用户自定义存储过程。,T-SQL组成:SQL语句、函数和存储过程。,T-SQL的子语言:数据定义语言DDL(Data Definition Language)、数据操纵语言DML(Data Manipulation Language)和数据控制语言DCL(Data Control Language)。,T-SQL的函数:系统函数、聚集函数、数学函数、字符串函数、数据类型转换函数和日期函数等。,2).T-SQL编程,表示及概念:变量名,由用户定义并使用。,A.注释(Comment)方式,(1)多行时,用/*.
2、*/,(2)单行时,用-,B.变量,局部变量定义:DECLARE 变量名 类型,n DECLARE myqty int,msg varchar(40),(1)局部变量,说明:(1)变量一旦定义,系统自动赋NULL值。,(2)对局部变量的赋值用SELECT语句。SELECT myqty=60,表示及概念:变量名,由系统定义,用户可使用之。,作用:用于指明系统运行过程中的运行状态,用户只能引用,不能修改和定义。,(2)全局变量,常用的全局变量:,(1)error:返回最后一个语句产生的错误码。,(2)rowcount:返回语句执行后受影响的行数。,(3)version:SQL SERVER版本号。
3、,(4)trancount:事务计数。,(5)transtate:事务状态(成功、失败)。,C.SELECT用法,(1)查看表或视图的内容;,(2)执行函数,返回值到客户端:SELECT db_name(),直接赋值:Select xxxx=2。一次只能一个。,从表中提取值赋给变量(应保证是唯一行且类型一致)。,(3)给变量赋值,(4)查看变量值:Select 变量,示例:SELECT tp=price FROM titles T WHERE T.id=Bu1032,D.流程控制语句,(1)IF(条件关系式)ELSE IF:如果关系式含有SELECT语句,则该SELECT语句须用圆括号括起,其
4、嵌套级别最多为150;,IF(SELECT AVG(price)FROM titles)=20 UPDATE titles SET price=price*1.3ELSE PRINT 平均价格未知!,(2)BEGIN.END:用于界定由多条SQL语句组成的语句块;示例:DECLARE avg_price money SELECT avg_price=AVG(price)FROM titles IF avg_price 20 BEGIN UPDATE titles SET price=price*1.5 PRINT 价格提高50%END,(3)WHILE.BREAK/CONTINUE:循环控制语
5、句 示例:WHILE(SELECT AVG(price)FROM titles)30 BREAK END,(4)GOTO.:跳到用户定义的标号处;,(5)RETURN:无条件退出;,(6)IF EXISTS/IF NOT EXISTS:是否有数据存在 IF NOT EXISTS(SELECT*FROM titles WHERE title_id=010101)BEGIN PRINT 没有010101的任何记录 RETURN END,E.信息显示,(1)Print.,(2)Print msg,(3)用参数嵌套形式加入不同的值:Print.%1!.%2!.,参数1,参数2,,(7)WAITFOR:
6、延迟某段时间,示例:WAITFOR DELAY 00:30:00/*延迟30分钟*/,语法:WAITFOR DELAY 日期时间格式的时间值|TIME 时间|,F.RAISERROR,用途:系统有很多系统信息及其代码(代码值在两万以下),用户也可用RAISERROR,自己定义错误信息及其代码(其代码值应在两万以上)。,语法:RAISERROR 错误代码 错误信息|局部变量,参数表 例:DECLARE table_name varchar(30)SELECT table_name=TITLERAISERROR 99999 表%1!不存在。,table_name,3).T-SQL提供的函数,A.字
7、符串操作,(1)连接操作:+,(2)convert函数,作用:将表达式按指定风格转换成指定的类型,其中的style只用于日期类型到字符串的转换。,语法:convert(类型符,表达式,style),convert(char(10),pubdate,105)其中,pubdate为日期型。,示例:,(3)日期=字符串,B.有关日期的几个函数,(1)getdate(),(2)datename()与datepart(),datename(日期元素,日期表达式):以字符串形式返回日期元素指定的日期的名字,用途:将服务器OS的时间送客户机。,示例:SELECT getdate(),datepart(日期元
8、素,日期表达式):以数字形式返回日期元素指定的日期的名字,日期元素及其指定返回的日期部分为:,yy:返回日期表达式中的年year或年数。,qq:返回日期表达式表示的季quarter或季数。,mm:返回日期表达式中的月month或月数。,dw:返回日期表达式表示的星期几day of week。,dy:返回日期表达式表示的一年中的第几天day of year,dd:返回日期表达式中的天或天数day。,wk:返回日期表达式表示的一年中的第几个星期week或星期数。,hh:返回日期表达式中的小时hour或小时数。,(3)dateadd()与datediff(),dateadd(日期元素,数值,日期表达
9、式):将数值转换成日期元素指定的部分加到日期表达式上返回。,datediff(日期元素,较早日期表达式,较晚日期表达式):两个日期相减后,按日期元素指定部分转化后返回。,示例:,Select datename(mm,pubdate)from titiles where title_id=Bu1032返回June,如用datepart则返回 6。,C.数学函数,4)存储过程,创建存储过程执行存储过程查看和修改存储过程重命名和删除存储过程,存储过程的概念,SQL Server提供了一种方法,它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成,以实现某个任务,这种方法就是存储过程
10、。在SQL Server中存储过程分为两类:即系统提供的存储过程和用户自定义的存储过程。,创建存储过程,在SQL Server中,可以使用三种方法创建存储过程:使用创建存储过程向导创建存储过程。利用SQL Server 企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,创建存储过程时,需要确定存储过程的三个组成部分:,所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句,包括调用其它存储过程的语句。返回给调用者的状态值,以指明调用是成功还是失败。,1.使用创建存储过程向导创建存储过程,在企业管理器中,选择工具菜单中的
11、向导选项,选择“创建存储过程向导”,则出现欢迎使用创建存储过程向导对话框。根据图提示可完成创建存储过程。,新建SQL Server组,欢迎使用创建存储过程向导对话框,选择数据库对话框,选择数据库对象对话框,完成创建存储过程向导对话框,编辑存储过程属性对话框,编辑存储过程SQL对话框,2.使用SQL Server 企业管理器创建存储过程,()在SQL Server企业管理器中,选择指定的服务器和数据库,用右键单击要创建存储过程的数据库,在弹出的快捷菜单中选择“新建”选项,再选择下一级菜单中的“存储过程”选项,如图5-77所示;或者用右键单击存储过程图标,从弹出的快捷菜单中选择“新建存储过程”选项
12、,如图5-78所示。均会出现创建存储过程对话框。()在文本框中可以输入创建存储过程的T_SQL语句,单击“检查语法”,则可以检查语法是否正确;单击“确定”按钮,即可保存该存储过程。如果要设置权限,单击“权限”按钮。,选择新建存储过程对话框(1),选择新建存储过程对话框(2),新建存储过程对话框,设置权限对话框,3.使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程,创建存储过程前,应该考虑下列几个事项:不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者,该所有者可将此权限授予其他用户。存
13、储过程是数据库对象,其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。,使用CREATE PROCEDURE创建存储过程的语法形式如下:,CREATEPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement.n,用CREATE PROCEDURE创建存储过程的语法参数的意义如下:,procedure_name
14、:用于指定要创建的存储过程的名称。number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。data_type:用于指定参数的数据类型。VARYING:用于指定作为输出OUTPUT参数支持的结果集。Default:用于指定参数的默认值。OUTPUT:表明该参数是一个返回参数。,RECOMPILE:表明 SQL Server 不会保存该存储过程的执行计划。ENCRYPTION:表示 SQL Server 加密了 sysc
15、omments 表,该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。FOR REPLICATION:用于指定不能在订阅服务器上执行为复制创建的存储过程。AS:用于指定该存储过程要执行的操作。sql_statement:是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。,例:创建存储过程,1.建立不带参数的存储过程 use studentcopy go create procedure p_sc1 As Select sno,grade From sc go 执行不带参数的execute 语句Use studentcopyGoExecute p
16、_sc1,2.建立带参数的存储过程Use studentcopy goIf exists(select name from sysobjects where name=p_sc and type=p)Drop procedure p_scGoCreate procedure p_sc sno char(7),cno char(10)As Select sname,cno,gradeFrom student join sc on student.sno=sc.snoWhere sc.sno=sno and cno=cnogo带参数的execute语句execute p_sc 参数,3.带输出参数
17、的存储过程 use studentcopy go create procedure mathadd m1 int,m2 int,result int output as set result=m1+m2 Go调用带参数的存储过程Use studentcopyGoDeclare answer intExecute mathadd 8,10,answer outputSelect answer,查看和修改存储过程,查看存储过程 存储过程被创建之后,它的名字就存储在系统表sysobjects中,它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过
18、程。,(1)使用企业管理器查看用户创建的存储过程,在企业管理器中,打开指定的服务器和数据库项,选择要创建存储过程的数据库,单击存储过程文件夹,此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程,从弹出的快捷菜单中选择属性选项,此时便可以看到存储过程的源代码。,(2)使用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下:sp_help:用于显示存储过程的参数及其数据类型sp_help objname=name参数name为要查看的存储过程的名称。sp_helptext:用于显示存储过程的源代码 sp_helptext objname=name参数
19、name为要查看的存储过程的名称。sp_depends:用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures:用于返回当前数据库中的存储过程列表,2.修改存储过程,存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTER PROCEDURE语句可以更改先前通过执行 CREATE PROCEDURE 语句创建的过程,但不会更改权限,也不影响相关的存储过程或触发器。其语法形式如下:ALTERPROCEDUREprocedure_name;number paramete
20、rdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION ASsql_statement.n,重命名和删除存储过程,1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename,其语法形式如下:sp_rename 原存储过程名称,新存储过程名称另外,通过企业管理器也可以修改存储过程的名称。,2.删除存储过程,删除存储过程可以使用DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除,其语法形式如下:drop proce
21、dure procedure,n当然,利用企业管理器也可以很方便地删除存储过程。,5)触发器概述 触发器是一种特殊类型的存储过程,触发器主要是通过事件进行触发而被执行,而存储过程可以通过存储过程名字而被直接调用。当对某一表进行如UPDATE,INSERT,DELETE这些操作时,SQL Server就会自动执行触发器所定义的SQL语句。从而确保对数据的处理必须符合这些SQL语句定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的参照完整性和数据的一致性。除此之外,触发器还有其他许多不同的功能:,(1)强化约束 触发器能够实现比CHECK语句更为复杂的约束。(2)跟踪变化 触发器可
22、以侦测数据库内的操作,从而不允许数据库未 经许可的指定更新和变化。(3)级联运行 触发器可以侦测数据库内的操作,并自动地级联影响整 个数据库的各项内容。例如,某个表上的触发器中包含 有另外一个表的数据操作(如删除、更新、插入)而该 操作也导致该表上的触发器被触发。,(4)存储过程的调用 为了响应数据库更新,触发器可以调用一个或多个存储过程,甚至可以通过外部过程的调用而在DBMS(数据库管理系统)本身之外进行操作。由此可见,触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。总体而言,触发器性能通常比较低,当运行触发器时,系统处理的大部分时间花费在参照其他表的这一处理
23、上,因为这些表既不在内存中也不在数据库设备上,而删除表和插入表总是位于内存中。可见触发器所参照的其他表的位置决定了操作要花费的时间长短。,SQL Server 2000支持两种类型的触发器:AFTER触发器和INSTEAD OF触发器。,6)触发器原理 SQL Server将数据写入数据库之前先校验规则和默认值,类似于一种信息“预过滤器”,避免某些数据项会影响数据库完整性造成数据库中的数据冗余。触发器是“后过滤器”,它在数据修改通过所有规则、默认值之后才执行。触发器是存储过程的特殊类型,它在对表进行插入、修改或删除操作时执行。如果触发器请求失败,将拒绝修改信息,并返回错误信息。,触发器常用来加
24、强业务规则和数据完整性。SQL Server通过表的创建语句(ALTER TABLE 和CREATE TABLE)提供了声明引用完整性(DRI)。然而DRI不能提供跨数据库的引用完整性。引用完整性可以通过定义主键和外键来实现。如果约束是存在于触发器表中的,则它们可以在INSTEAD OF触发器执行之后与AFTER触发器执行之前被检查。如果与约束冲突,则INSTEAD OF触发器动作将回滚,并且AFTER触发器不会执行。,每个触发器有两个特殊的表插入表和删除表。这两个表是逻辑表,并且这两个表是由系统管理的,存储在内存中,不是存储在数据库中,因此不允许用户直接对其修改。这两个表的结构总是与被该触发
25、器作用的表有相同的表结构。这两个表是动态驻留在内存中,当触发器工作完成这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。另外,这两个表是只读的,即用户不能向这两个表写入内容但可以引用表中的数据。,(1)inserted表 根据触发器中的语句所执行的操作类型的不同,执行一系列触发器可以创建一个或者两个临时表(inserted表和deleted表),也可两个都创建。表1说明了在进行何种查询时,触发器创建哪些表。表1 触发器创建的表触发器类型 创建inserted 表 创建deleted 表INSERT 是 否UPDATE 是 是DELETE 否 是,应当了解的是:inse
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 完整性 触发器
链接地址:https://www.desk33.com/p-250686.html