SQLServer存储过程与触发器课件.ppt

第4章,SQL Server 存储过程与触发器,2021/5/4,1,第4章SQL Server 存储过程与触发器2021/5/,4.1 SQL Server 存储过程,4.1.1存储过程概述 存储过程(Stored Procedure)是一组为了完成特定功能T-SQL语句集合,经编译后存储在SQL Server服务器端数据库中。存储过程可以分为两类:系统存储过程和自定义存储过程。,2021/5/4,2,4.1 SQL Server 存储过程4.1.1存储过程概,系统存储过程,2021/5/4,3,系统存储过程2021/5/43,系统存储过程,系统存储过程在SQL Server安装成功后,就已经存储在系统数据库Master中,这些存储过程都是以sp_为前缀命名的它们主要是从系统表中获取信息,系统管理员可以通过简单调用系统存储过程而完成复杂的SQL Server管理工作。可以通过系统存储过程完成许多管理性或信息的操作。系统存储过程在Master数据库中,在其他数据库中可以直接调用,调用时不必在存储过程名前加上数据库名。,2021/5/4,4,系统存储过程系统存储过程在SQL Server安装成功后,就,自定义存储过程,自定义存储过程是由用户创建并能完成某一特定功能的存储过程。,2021/5/4,5,自定义存储过程自定义存储过程是由用户创建并能完成某一特定功能,存储过程的优点,1提高应用程序的通用性和可移植性2可以更有效地管理用户操作数据库的权限 3可以提高T-SQL的速度4减轻服务器的负担5块化程序设计。6减少操作错误。7能自动处理复杂的或敏感的事务。8可以实现管理任务自动化。,2021/5/4,6,存储过程的优点1提高应用程序的通用性和可移植性2021/5,4.1.2存储过程的创建与执行,创建前确定所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句,包括调用其它存储过程的语句。返回给调用者的状态值,以指明调用是成功还是失败。一个存储过程的最大尺寸为128M,2021/5/4,7,4.1.2存储过程的创建与执行创建前确定2021/5/47,1.直接创建存储过程,(1)打开Microsoft SQL Server Manager管理器(2)单击数据库前面的“+”号,然后单击“Material_Data1”数据库前面的“+”号,再单击“可编程性”前面的“+”号,选择“存储过程”,单击鼠标右键,在弹出的快捷菜单中单击“新建存储过程”命令。(3)打开了一个创建存储过程的数据库引擎查询模板,修改相应参数即可。,2021/5/4,8,1.直接创建存储过程(1)打开Microsoft SQL S,直接创建存储过程,2021/5/4,9,直接创建存储过程2021/5/49,2.代码创建存储过程,语法CREATE PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statement .n begin 命令行或命令块 end,2021/5/4,10,2.代码创建存储过程语法2021/5/410,说明,rocedure_name:用于指定要创建的存储过程的名称。 number:该参数是可选的整数,它用来对同名的存储过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type:用于指定参数的数据类型。 Default:用于指定参数的默认值。 OUTPUT:表明该参数是一个返回参数。recompile:表示每次执行此存储过程时都重新编译一次 encryption:所创建的存储过程的内容会被加密,2021/5/4,11,说明procedure_name:用于指定要创建的存储过程的,3.代码执行存储过程,利用T-SQL执行存储过程的语法格式如下: execute 过程名参数值,output(1)没有参数的存储过程创建 create proc hyprocl as select * from manager where wage 1800 执行该存储过程 execute hyprocl,2021/5/4,12,3.代码执行存储过程利用T-SQL执行存储过程的语法格式如下,(2) 有参数存储过程创建,create proc hyproc2 mingz int, maxgz intas select * from manager where wage between mingz and maxgz,2021/5/4,13,(2) 有参数存储过程创建create proc hypr,执行实例,假设要显示工资在1000到2000之间的manager信息,具体代码: execute hyproc2 1005,1800,2021/5/4,14,执行实例假设要显示工资在1000到2000之间的manage,4.1.3 修改存储过程,修改存储过程具体格式如下: alter proc过程名 parameter参数类型 parameter参数类型output as sql_statement .n begin 命令行或命令块 end,2021/5/4,15,4.1.3 修改存储过程修改存储过程具体格式如下:2021/,实例,修改存贮过程hyproc2, 输出manager性别分类人员数与总工资。 alter proc hyproc2 sex1 char(2), managercount int output, wagetotal real output asbegin Select * from manager select managercount=count(wage) from manager where sex=sex1 select wagetotal=sum(wage) from manager where sex=sex1 end,2021/5/4,16,实例修改存贮过程hyproc2, 输出manager性别分类,实例,假设要显示manager信息及输出工资的最大值与平均值,具体代码如下:Declare x1 char(2), x2 realexecute hyproc2 男,x1 output,x2 output,2021/5/4,17,实例假设要显示manager信息及输出工资的最大值与平均值,4.2 SQL Server 触发器,触发器是一种特殊类型的存储过程,是用户自定义的复杂的完整性控制过程。特点:功能强、开销高维护行级数据的完整性与CHECK约束相比,能实现更加复杂的数据完整性,2021/5/4,18,4.2 SQL Server 触发器 触发器是一种特殊类型的,数据完整性,完整性是指数据的正确性相容性（一致性）三类基本完整性规则域完整性规则使基本表的列输入有效。控制域完整性有效的方法有:限制数据类型、格式、可能的取值范围、修改列值时必须满足的条件等。实体完整性规则 实体完整性规则用来约束现实世界中的实体是可区分的,即它们具有唯一性标识。这一规则在关系模型中的体现是基本表所有主属性都不能取空值（NULL）。参照完整性规则参照完整性规则用来约束具有参照关系的两个表中,主码和外码的数据要保持一致。,2021/5/4,19,数据完整性完整性是指数据的2021/5/419,触发器的作用,完成比约束更复杂的数据约束检查所做的SQL是否允许触发器可以检查SQL所做的操作是否被允许。例如:在产品库存表里,如果要删除一条产品记录,在删除记录时,触发器可以检查该产品库存数量是否为零,如果不为零则取消该删除操作。修改其它数据表里的数据当一个SQL语句对数据表进行操作的时候,触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如:一个订单取消的时候,那么触发器可以自动修改产品库存表,在订购量的字段上减去被取消订单的订购数量。调用更多的存储过程触发器本身就是一种存储过程,而存储过程是可以嵌套使用的,所以触发器也可以调用一个或多过存储过程。,2021/5/4,20,触发器的作用完成比约束更复杂的数据约束2021/5/420,触发器的作用,发送SQL Mail在SQL语句执行完之后,触发器可以判断更改过的记录是否达到一定条件,如果达到这个条件的话,触发器可以自动调用SQL Mail来发送邮件。例如:当一个订单交费之后,可以物流人员发送Email,通知他尽快发货。返回自定义的错误信息约束是不能返回信息的,而触发器可以。例如插入一条重复记录时,可以返回一个具体的友好的错误信息给前台应用程序。更改原本要操作的SQL语句触发器可以修改原本要操作的SQL语句例如原本的SQL语句是要删除数据表里的记录,但该数据表里的记录是最要记录,不允许删除的,那么触发器可以不执行该语句。防止数据表构结更改或数据表被删除为了保护已经建好的数据表,触发器可以在接收到Drop和Alter开头的SQL语句里,不进行对数据表的操作。,2021/5/4,21,触发器的作用发送SQL Mail2021/5/421,触发器的种类,在SQL Server 2005中,触发器可以分为两大类:DML触发器和DDL触发器DML触发器:DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML触发器又分为两类:After触发器和Instead Of触发器DDL触发器:DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。SQL SERVER 2005新增添功能,2021/5/4,22,触发器的种类在SQL Server 2005中,触发器可以分,After触发器和Instead Of触发器,After触发器是在记录更变完之后才被激活执行的。以删除记录为例:SQL Server先将要删除的记录存放在删除表里,然后把数据表里的记录删除。再激活After触发器,执行After触发器里的SQL语句。执行完毕之后, 删除内存中的删除表,退出整个操作。Instead Of触发器是在这些操作进行之前就激活了,并且不再去执行原来的SQL操作,而去运行触发器本身的SQL语句。,2021/5/4,23,After触发器和Instead Of触发器After触发器,触发器的工作原理,在SQL Server 2005里,为每个DML触发器都定义了两个特殊的表,一个是插入表,一个是删除表。这两个表是建在数据库服务器的内存中的,是由系统管理的逻辑表,而不是真正存储在数据库中的物理表。对于这两个表,用户只有读取的权限,没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的,当触发器的工作完成之后,这两个表也将会从内存中删除。插入表里存放的是更新前的记录对于插入记录操作来说,插入表里存放的是要插入的数据对于更新记录操作来说,插入表里存放的是要更新的记录。删除表里存放的是更新后的记录对于更新记录操作来说,删除表里存放的是更新前的记录（更新完后即被删除）;对于删除记录操作来说,删除表里存入的是被删除的旧记录。,2021/5/4,24,触发器的工作原理在SQL Server 2005里,为每个D,触发器的工作原理,2021/5/4,25,触发器的工作原理激活触发器的动作Inserted表Delet,其他注意事项,After触发器只能用于数据表中,Instead Of触发器可以用于数据表和视图上,但两种触发器都不可以建立在临时表上。一个数据表可以有多个触发器,但是一个触发器只能对应一个表。在同一个数据表中,对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器,但Instead Of触发器针对每个操作只有建立一个。如果针对某个操作即设置了After触发器又设置了Instead Of触发器,那么Instead of触发器一定会激活,而After触发器就不一定会激活了。,2021/5/4,26,其他注意事项After触发器只能用于数据表中,Instead,4.2.1触发器定义语法,after触发器: create trigger 触发器名 on 表名 with encryption for insert,update,delete as begin 命令行或程序块 endInstead of触发器: create trigger 触发器名 on 表名或视图名 instead of insert,update,delete as begin 命令行或程序块 end,2021/5/4,27,4.2.1触发器定义语法after触发器:2021/5/42,实例,创建一个触发器,向manager中插入一条记录,同时创建一个数据库表并向表中插入两条记录。(1)打开Microsoft SQL Server Manager管理器。(2)新建一个数据库引擎查询文档。(3)在数据库引擎查询文档中输入如下代码:Use Material_Data1(4)按键盘上的F5”键,显示如下提示信息:命令已成功完成。(5)这样就打开要使用的数据库。,2021/5/4,28,实例创建一个触发器,向manager中插入一条记录,同时创建,实例,create trigger hytriggerl on manager for update as begin create table triuser( userid int identity(1,1)primary key, usermame varchar(50), userpwd varchar(50) ) insert into triuser(username,userpwd)values(李明,111) insert into triuser(usermame,userpwd)values(王明,222) insert into triuser(usermame,userpwd)values(刘芳,333)end,2021/5/4,29,实例create trigger hytriggerl on,实例,(6)选择创建触发器的代码,按键盘上的F5”键,显示如下提示信息: 命令已成功完成。 (7)这样就成功创建了触发器。在这里要注意,只是创建了触发器,并没有执行触发器中的代码,即表triuser还不存在, 当然该表中也不会有记录。 (8)下面来通过对manager表的更新操作调用触发器hytriggerl,具体代码如下: Update manager set wage=wage+100 where managerNo=001 (9)选择SQL语句,按下键盘上的F5”键执行该SQL语句,显示如图4.5提示信息:,2021/5/4,30,实例 (6)选择创建触发器的代码,按键盘上的F5”键,显示,实例,图4.5 执行触发器,2021/5/4,31,实例 图4.5 执行触发器 2021/5/431,实例,(10)提示信息表示影响了四行,即更新了仓库表中的一条记录,创建triuser表,并向该表中插入三条记录,下面通过select * from triuser来显示触发器产生新表中的数据信息,如图4.6所示。,2021/5/4,32,实例 (10)提示信息表示影响了四行,即更新了仓库表中的,实例,图4.6 显示触发器执行后的结果,2021/5/4,33,实例图4.6 显示触发器执行后的结果 2021/5/433,实例,在订单明细表里,折扣字段不能大于0.6,如果插入记录时,折扣大于0.6的话,回滚操作。CREATE TRIGGER 订单明细_Insert ON 订单明细 AFTER INSERTAS BEGIN if (Select 折扣 from inserted)0.6 begin print 折扣不能大于0.6 Rollback Transaction endENDGO,2021/5/4,34,实例在订单明细表里,折扣字段不能大于0.6,如果插入记录时,实例,在订单明细表里,折扣字段不能大于0.6,如果插入记录时,折扣大于0.6的话,回滚操作。CREATE TRIGGER 订单明细_Insert ON 订单明细 Instead Of INSERTAS BEGIN declare 订单ID int,产品ID int,单价 money,数量 smallint, 折扣 real set 订单ID = (select 订单ID from inserted) set 产品ID = (select 产品ID from inserted) set 单价 = (select 单价 from inserted) set 数量 = (select 数量 from inserted) set 折扣 = (select 折扣 from inserted) if (折扣)0.6 print 折扣不能大于0.6 else INSERT into订单明细(订单ID,产品ID,单价,数量, 折扣),2021/5/4,35,实例在订单明细表里,折扣字段不能大于0.6,如果插入记录时,实例,如果更改了学生的学号, 希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号)Create Trigger truStudent On Student -在Student表中创建触发器 for Update -为什么事件触发 As -事件触发后所要做的事情 if Update(StudentID) begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRecord br , Deleted d ,Inserted i Where br.StudentID=d.StudentID end,2021/5/4,36,实例如果更改了学生的学号, 希望他的借书记录仍然与这个学生相,实例,如果该学生已经毕业,希望删除他的学号的同时,也删除它的借书记录。 Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID,2021/5/4,37,实例如果该学生已经毕业,希望删除他的学号的同时,也删除它的借,4.2.2 查看触发器基本信息,通过sp_help能够查看触发器的基本信息触发器名、所有者、创建者和创建时间。其语法格式如下:exec sp_help 触发器名 如查看触发器hytriggerl信息exec sp_help hytriggerl,2021/5/4,38,4.2.2 查看触发器基本信息通过sp_help能够查看触发,实例,图4.7 查看触发器基本信息,2021/5/4,39,实例 图4.7 查看触发器基本信息 2021/5/439,4.2.3 查看触发器代码,通过sp_helptext能够查看触发器SQL的代码信息,但要注意如果在创建触发器时使with encrypdon选项,则执行该命令也看不到SQL代码。其语法格式如下:exec sp_helptext 触发器名。若要查看触发器hytriggerl代码exec sp_helptext hytriggerl,2021/5/4,40,4.2.3 查看触发器代码通过sp_helptext能够查看,实例,图4.8 查看触发器hytriggerl代码,2021/5/4,41,实例图4.8 查看触发器hytriggerl代码2021/5,4.2.4 修改触发器,修改触发器的方法很简单,利用T-SQL修改触发器的语法格式如下:after触发器: alter trigger 触发器名 On 表名with encryption for insert,update,delete as begin 命令行或程序块 End,2021/5/4,42,4.2.4 修改触发器 修改触发器的方法很简单,利用T-S,实例,修改hytriggerl插入行数据刘芳为张清alter trigger hytriggerl on manager for update as begin create table triuser( userid int identity(1,1)primary key, usermame varchar(50), userpwd varchar(50) ) insert into triuser(username,userpwd)values(李明,111) insert into triuser(usermame,userpwd)values(王明,222) insert into triuser(usermame,userpwd)values(张清,333)end,2021/5/4,43,实例修改hytriggerl插入行数据刘芳为张清20,修改instead of触发器,alter trigger 触发器名 on 表名或视图名 instead of insert ,update, delete as begin 命令行或程序块 end 修改触发器与创建触发器几乎相同,只是把create改为alter即可。,2021/5/4,44,修改instead of触发器 alter trigge,4.2.5删除触发器,删除触发器的方法很简单,利用T-SQL删除触发器的语法格式如下: drop trigger 触发器名,2021/5/4,45,4.2.5删除触发器删除触发器的方法很简单,利用T-SQL删,4.2.6 DDL触发器,DDL触发器是SQL Server 2005新增的一个触发器类型,是一种特殊的触发器,它在响应数据定义语言（DDL）语句时触发。一般用于数据库中执行管理任务。与DML触发器一样,DDL触发器也是通过事件来激活,并执行其中 的SQL语句的。但与DML触发器不同,DML触发器是响应Insert、Update或Delete语句而激活的,DDL触发器是响应Create、 Alter或Drop开头的语句而激活的。,2021/5/4,46,4.2.6 DDL触发器DDL触发器是SQL Server,以下几种情况下可以使用DDL触发器:数据库里的库架构或数据表架构很重要,不允许被修改。防止数据库或数据表被误操作删除。在修改某个数据表结构的同时修改另一个数据表的相应的结构。要记录对数据库结构操作的事件,2021/5/4,47,以下几种情况下可以使用DDL触发器:2021/5/447,语法,CREATE TRIGGER 触发器名 ON ALL SERVER或DATABASEFOR 或 AFTER 激活DDL触发器的事件AS 要执行的SQL语句,2021/5/4,48,语法CREATE TRIGGER 触发器名 2021/5/4,实例,启动Management Studio,登录到指定的服务器上。在【对象资源管理器】下选择【数据库】,定位到【Northwind】数据库上。单击【新建查询】按钮,在弹出的【查询编辑器】的编辑区里输入以下代码:CREATE TRIGGER 禁止对数据表操作ON DATABASE FOR DROP_TABLE, ALTER_TABLEAS PRINT 对不起,您不能对数据表进行操作 ROLLBACK ;,2021/5/4,49,实例启动Management Studio,登录到指定的服务,实例,建立一个DDL触发器,用于保护当前SQL Server服务器里所有数据库不能被删除。具体代码如下:CREATE TRIGGER 不允许删除数据库ON all server FOR DROP_DATABASEAS PRINT 对不起,您不能删除数据库 ROLLBACK ;GO,2021/5/4,50,实例建立一个DDL触发器,用于保护当前SQL Server服,在Management Studio如果要修改DDL触发器内容,就只能先删除该触发器,再重新建立一个DDL触发器。,2021/5/4,51,在Management Studio如果要修改DDL触发器内,触发器的创建,例:对S表定义一个删除触发器,使得当删除学生记录时,将S_C表中相应的选课记录删除。,S（SNO,SNAME）S_C（SNO,CNO,SCORE）,2021/5/4,52,触发器的创建例:对S表定义一个删除触发器,使得当删除学生记录,触发器的创建,例:对S_C表定义一个插入触发器,使得当插入选课记录时,检查所参照的学生学号和课程号是否存在,如果不存在,则撤消所做的插入操作。,S（SNO,SNAME）C（CNO,CNAME）S_C（SNO,CNO,SCORE）,2021/5/4,53,触发器的创建例:对S_C表定义一个插入触发器,使得当插入选课,触发器的创建,例:当插入或更新学生成绩时,触发器检查该课程是否为考查课,若是,则通过的成绩只能以60分计,未通过的只能以40分计。,涉及的表结构:C1(Cno,Cname,Ctype) S_C(Sno,Cno,Score),2021/5/4,54,触发器的创建例:当插入或更新学生成绩时,触发器检查该课程是否,触发器的创建,例:对职工表定义一个插入触发器,使得当插入职工记录时,检查所参照的仓库元组是否存在,如果不存在,则撤消所做的插入操作。,仓库（仓库号,城市,面积）职工（仓库号,职工号,工资）,2021/5/4,55,触发器的创建例:对职工表定义一个插入触发器,使得当插入职工记,触发器的创建,例:对职工表定义一个更新触发器,使得当职工变换所属仓库时,检查所参照的仓库元组是否存在,如果不存在,则撤消所做的更新操作,如果新的仓库号是WH2则将工资提高10%。,仓库（仓库号,城市,面积）职工（仓库号,职工号,工资）,2021/5/4,56,触发器的创建例:对职工表定义一个更新触发器,使得当职工变换所,触发器的创建,CREATE TRIGGER TR_UPDATE ON 职工 FOR UPDATE AS DECLARE WHNO CHAR(4) IF UPDATE(仓库号) BEGIN IF NOT EXISTS (SELECT * FROM 仓库 WHERE 仓库号=(SELECT 仓库号 FROM INSERTED) BEGIN RAISERROR(非法仓库号!,16,1) ROLLBACK TRANSACTION END ELSE BEGIN SELECT WHNO=仓库号 FROM INSERTED IF WHNO=WH2 UPDATE 职工 SET 工资=工资*1.1 WHERE 职工号=(SELECT 职工号 FROM INSERTED) END END,2021/5/4,57,触发器的创建CREATE TRIGGER TR_UPDATE,触发器的创建,CREATE TRIGGER WH_INSON 职工 FOR INSERTAS IF NOT EXISTS (SELECT * FROM 仓库 WHERE 仓库号 = (SELECT 仓库号 FROM INSERTED)BEGIN RAISERROR(非法仓库号!,1,1) ROLLBACK TRANSACTIONEND,INSERT 职工 VALUES(WH5,E10,1600),IN,2021/5/4,58,触发器的创建CREATE TRIGGER WH_INSINS,触发器的创建,CREATE TRIGGER SCORE_KCON S_C FOR INSERT,UPDATEAS DECLARE SCORE INT,CTYPE CHAR(4)SELECT SCORE=SCORE,CTYPE=CTYPEFROM C1,INSERTEDWHERE INSERTED.CNO=C1.CNOIF(CTYPE=考查) AND (SCORE60 AND SCORE40)BEGIN RAISERROR(该课程为考查课,成绩以60或40计!,16,1) ROLLBACK TRANSACTIONEND,2021/5/4,59,触发器的创建CREATE TRIGGER SCORE_KC2,