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

1、第第4 4章章SQL Server 存储过程与存储过程与触发器触发器2021/5/414.1 SQL Server 存储过程4.1.1存储过程概述存储过程概述 存储过程存储过程(Stored Procedure)是一组为是一组为了完成特定功能了完成特定功能T-SQL语句集合语句集合,经编译后经编译后存储在存储在SQL Server服务器端数据库中。存服务器端数据库中。存储过程可以分为两类储过程可以分为两类:系统存储过程系统存储过程和和自定自定义存储过程义存储过程。2021/5/42系统存储过程系统存储过程2021/5/43系统存储过程系统存储过程系统存储过程在系统存储过程在SQL Server

2、安装成功后安装成功后,就就已经存储在系统数据库已经存储在系统数据库Master中中,这些存储过这些存储过程都是以程都是以sp_为前缀命名的为前缀命名的 它们主要是从系统表中获取信息它们主要是从系统表中获取信息,系统管理员可以系统管理员可以通过简单调用系统存储过程而完成复杂的通过简单调用系统存储过程而完成复杂的SQL Server管理工作。可以通过系统存储过程完成许管理工作。可以通过系统存储过程完成许多管理性或信息的操作。多管理性或信息的操作。系统存储过程在系统存储过程在Master数据库中数据库中,在其他数据库中在其他数据库中可以直接调用可以直接调用,调用时不必在存储过程名前加上数调用时不必在

3、存储过程名前加上数据库名。据库名。2021/5/44自定义存储过程自定义存储过程自定义存储过程是由用户创建并能完成自定义存储过程是由用户创建并能完成某一特定功能的存储过程。某一特定功能的存储过程。2021/5/45存储过程的优点存储过程的优点1提高应用程序的通用性和可移植性提高应用程序的通用性和可移植性2可以更有效地管理用户操作数据库的权限可以更有效地管理用户操作数据库的权限 3可以提高可以提高T-SQL的速度的速度4减轻服务器的负担减轻服务器的负担5块化程序设计。块化程序设计。6减少操作错误。减少操作错误。7能自动处理复杂的或敏感的事务。能自动处理复杂的或敏感的事务。8可以实现管理任务自动化

4、。可以实现管理任务自动化。2021/5/464.1.24.1.2存储过程的创建与执行存储过程的创建与执行创建前确定创建前确定 所有的输入参数以及传给调用者的输出参数。所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句被执行的针对数据库的操作语句,包括调用其它存包括调用其它存储过程的语句。储过程的语句。返回给调用者的状态值返回给调用者的状态值,以指明调用是成功还是失以指明调用是成功还是失败。败。一个存储过程的最大尺寸为一个存储过程的最大尺寸为128M2021/5/471.1.直接创建存储过程直接创建存储过程(1)打开打开Microsoft SQL Server Manager管

5、理器管理器(2)单击数据库前面的单击数据库前面的“+”号号,然后单击然后单击“Material_Data1”数据库前面的数据库前面的“+”号号,再单击再单击“可编程性可编程性”前面的前面的“+”号号,选择选择“存储过程存储过程”,单击鼠标右键单击鼠标右键,在弹出的快在弹出的快捷菜单中单击捷菜单中单击“新建存储过程新建存储过程”命令。命令。(3)打开了一个创建存储过程的数据库引打开了一个创建存储过程的数据库引擎查询模板擎查询模板,修改相应参数即可。修改相应参数即可。2021/5/48直接创建存储过程直接创建存储过程2021/5/492.2.代码创建存储过程代码创建存储过程语法语法CREATE P

6、ROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS sql_statement .n begin 命令行或命令块命令行或命令块 end2021/5/410说明说明procedure_name:用于指定要创建的存储过程的名称。用于指定要创建的存储过程的名称。number:该参数是可选的整数该参数是可选的整数,它用来对同名的存储过程分组它用来对同名的存储过程分组,以便用一条以便用一条 DROP PRO

7、CEDURE 语句即可将同组的过程一起语句即可将同组的过程一起除去。除去。parameter:过程中的参数。在过程中的参数。在 CREATE PROCEDURE 语句语句中可以声明一个或多个参数。中可以声明一个或多个参数。data_type:用于指定参数的数据类型。用于指定参数的数据类型。Default:用于指定参数的默认值。用于指定参数的默认值。OUTPUT:表明该参数是一个返回参数。表明该参数是一个返回参数。recompile:表示每次执行此存储过程时都重新编译一次表示每次执行此存储过程时都重新编译一次 encryption:所创建的存储过程的内容会被加密所创建的存储过程的内容会被加密 2

8、021/5/4113.3.代码执行存储过程代码执行存储过程利用利用T-SQL执行存储过程的语法格式如下执行存储过程的语法格式如下:execute 过程名过程名参数值参数值,output(1)没有参数的存储过程创建没有参数的存储过程创建 create proc hyprocl as select*from manager where wage 1800 执行该存储过程执行该存储过程 execute hyprocl2021/5/412(2)(2)有参数存储过程创建有参数存储过程创建create proc hyproc2 mingz int,maxgz intas select*from manag

9、er where wage between mingz and maxgz2021/5/413执行实例执行实例假设要显示工资在假设要显示工资在1000到到2000之间的之间的manager信息信息,具体代码具体代码:execute hyproc2 1005,18002021/5/4144.1.3 4.1.3 修改存储过程修改存储过程修改存储过程具体格式如下修改存储过程具体格式如下:alter proc过程名过程名 parameter参数类型参数类型 parameter参数类型参数类型output as sql_statement .n begin 命令行或命令块命令行或命令块 end2021/

10、5/415实例实例修改存贮过程修改存贮过程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/416实例实

11、例假设要显示假设要显示manager信息及输出工资的信息及输出工资的最大值与平均值最大值与平均值,具体代码如下具体代码如下:Declare x1 char(2),x2 realexecute hyproc2 男男,x1 output,x2 output2021/5/4174.2 SQL Server 4.2 SQL Server 触发器触发器 触发器是一种特殊类型的触发器是一种特殊类型的存储过程存储过程,是用户自是用户自定义的复杂的定义的复杂的完整性控制完整性控制过程。过程。特点特点:功能强、开销高功能强、开销高维护行级数据的完整性维护行级数据的完整性与与CHECK约束相比约束相比,能实现更加

12、复杂的数据完能实现更加复杂的数据完整性整性2021/5/418数据完整性数据完整性完整性是指数据的完整性是指数据的 正确性正确性 相容性（一致性）相容性（一致性）三类基本完整性规则三类基本完整性规则 域完整性规则域完整性规则使基本表的使基本表的列列输入有效。输入有效。控制域完整性有效的方法有控制域完整性有效的方法有:限制限制数据类型、格式、可能数据类型、格式、可能的取值范围、修改列值时必须满足的条件的取值范围、修改列值时必须满足的条件等。等。实体完整性规则实体完整性规则 实体完整性规则用来约束现实世界中的实体是可区分的实体完整性规则用来约束现实世界中的实体是可区分的,即它们具有即它们具有唯一性

13、标识唯一性标识。这一规则在关系模型中的体现。这一规则在关系模型中的体现是基本表所有主属性都不能取空值（是基本表所有主属性都不能取空值（NULL）。）。参照完整性规则参照完整性规则参照完整性规则用来约束具有参照关系的两个表中参照完整性规则用来约束具有参照关系的两个表中,主码主码和外码和外码的数据要保持一致。的数据要保持一致。2021/5/419触发器的作用触发器的作用完成比约束更复杂的数据约束完成比约束更复杂的数据约束检查所做的检查所做的SQL是否允许是否允许 触发器可以检查触发器可以检查SQL所做的操作是否被允许。所做的操作是否被允许。例如例如:在产品库存表里在产品库存表里,如果要删除一条产品

14、记录如果要删除一条产品记录,在删除记录时在删除记录时,触发器可以检查该产品库存数量是否为零触发器可以检查该产品库存数量是否为零,如果不为零则取消如果不为零则取消该删除操作。该删除操作。修改其它数据表里的数据修改其它数据表里的数据 当一个当一个SQL语句对数据表进行操作的时候语句对数据表进行操作的时候,触发器可以根据触发器可以根据该该SQL语句的操作情况来对另一个数据表进行操作。语句的操作情况来对另一个数据表进行操作。例如例如:一个订单取消的时候一个订单取消的时候,那么触发器可以自动修改产品库存那么触发器可以自动修改产品库存表表,在订购量的字段上减去被取消订单的订购数量。在订购量的字段上减去被取

15、消订单的订购数量。调用更多的存储过程调用更多的存储过程 触发器本身就是一种存储过程触发器本身就是一种存储过程,而存储过程是可以嵌套使用而存储过程是可以嵌套使用的的,所以触发器也可以调用一个或多过存储过程。所以触发器也可以调用一个或多过存储过程。2021/5/420触发器的作用触发器的作用发送发送SQL Mail 在在SQL语句执行完之后语句执行完之后,触发器可以判断更改过的记录是否达到一触发器可以判断更改过的记录是否达到一定条件定条件,如果达到这个条件的话如果达到这个条件的话,触发器可以自动调用触发器可以自动调用SQL Mail来来发送邮件。发送邮件。例如例如:当一个订单交费之后当一个订单交费

16、之后,可以物流人员发送可以物流人员发送Email,通知他尽快发货。通知他尽快发货。返回自定义的错误信息返回自定义的错误信息 约束是不能返回信息的约束是不能返回信息的,而触发器可以。而触发器可以。例如插入一条重复记录时例如插入一条重复记录时,可以返回一个具体的友好的错误信息给前台可以返回一个具体的友好的错误信息给前台应用程序。应用程序。更改原本要操作的更改原本要操作的SQL语句语句 触发器可以修改原本要操作的触发器可以修改原本要操作的SQL语句语句例如原本的例如原本的SQL语句是要删除数据表里的记录语句是要删除数据表里的记录,但该数据表里的记录是但该数据表里的记录是最要记录最要记录,不允许删除的

17、不允许删除的,那么触发器可以不执行该语句。那么触发器可以不执行该语句。防止数据表构结更改或数据表被删除防止数据表构结更改或数据表被删除 为了保护已经建好的数据表为了保护已经建好的数据表,触发器可以在接收到触发器可以在接收到Drop和和Alter开开头的头的SQL语句里语句里,不进行对数据表的操作。不进行对数据表的操作。2021/5/421触发器的种类触发器的种类在在SQL Server 2005中中,触发器可以分为两大类触发器可以分为两大类:DML触发器和触发器和DDL触发器触发器DML触发器触发器:DML触发器是当数据库服务器中发生数据操作语言（触发器是当数据库服务器中发生数据操作语言（Da

18、ta Manipulation Language）事件时执行的存储过程。）事件时执行的存储过程。DML触发器又分为两类触发器又分为两类:After触发器和触发器和Instead Of触发器触发器DDL触发器触发器:DDL触发器是在响应数据定义语言（触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。）事件时执行的存储过程。DDL触发器一般用于触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。数据库表结构被修改等。SQL SERVER 2005新增添功能新增添功能

19、2021/5/422AfterAfter触发器和触发器和Instead OfInstead Of触发器触发器After触发器是在记录更变完之后才被激活执触发器是在记录更变完之后才被激活执行的。行的。以删除记录为例以删除记录为例:SQL Server先将要删除的记录先将要删除的记录存放在删除表里存放在删除表里,然后把数据表里的记录删除。然后把数据表里的记录删除。再激活再激活After触发器触发器,执行执行After触发器里的触发器里的SQL语语句。句。执行完毕之后执行完毕之后,删除内存中的删除表删除内存中的删除表,退出整个操退出整个操作。作。Instead Of触发器是在这些操作进行之前就触发器

20、是在这些操作进行之前就激活了激活了,并且不再去执行原来的并且不再去执行原来的SQL操作操作,而去而去运行触发器本身的运行触发器本身的SQL语句。语句。2021/5/423触发器的工作原理触发器的工作原理在在SQL Server 2005SQL Server 2005里里,为每个为每个DMLDML触发器都定义了两触发器都定义了两个特殊的表个特殊的表,一个是插入表一个是插入表,一个是删除表。一个是删除表。这两个表是建在数据库服务器的内存中的这两个表是建在数据库服务器的内存中的,是由系统管理的是由系统管理的逻辑表逻辑表,而不是真正存储在数据库中的物理表。而不是真正存储在数据库中的物理表。对于这两个表

21、对于这两个表,用户只有读取的权限用户只有读取的权限,没有修改的权限。没有修改的权限。这两个表的结构与触发器所在数据表的结构是完全一致的这两个表的结构与触发器所在数据表的结构是完全一致的,当触发器的工作完成之后当触发器的工作完成之后,这两个表也将会从内存中删除。这两个表也将会从内存中删除。插入表里存放的是更新前的记录插入表里存放的是更新前的记录对于插入记录操作来说对于插入记录操作来说,插入表里存放的是要插入的数据插入表里存放的是要插入的数据对于更新记录操作来说对于更新记录操作来说,插入表里存放的是要更新的记录。插入表里存放的是要更新的记录。删除表里存放的是更新后的记录删除表里存放的是更新后的记录

22、对于更新记录操作来说对于更新记录操作来说,删除表里存放的是更新前的记录删除表里存放的是更新前的记录（更新完后即被删除）（更新完后即被删除）;对于删除记录操作来说对于删除记录操作来说,删除表里存入的是被删除的旧记录。删除表里存入的是被删除的旧记录。2021/5/424触发器的工作原理触发器的工作原理激活触发器激活触发器的动作的动作Inserted表表Deleted表表Insert存放要插入存放要插入的记录的记录Update存放要更新存放要更新的记录的记录存放更新前的旧存放更新前的旧记录记录Delete存放要删除的旧存放要删除的旧记录记录2021/5/425其他注意事项其他注意事项After触发器

23、只能用于触发器只能用于数据表数据表中中,Instead Of触发器可触发器可以用于以用于数据表和视图数据表和视图上上,但两种触发器都不可以建立但两种触发器都不可以建立在在临时表临时表上。上。一个数据表可以有多个触发器一个数据表可以有多个触发器,但是一个触发器只能但是一个触发器只能对应一个表。对应一个表。在同一个数据表中在同一个数据表中,对每个操作（如对每个操作（如Insert、Update、Delete）而言可以建立）而言可以建立许多个许多个After触发器触发器,但但Instead Of触发器针对每个操作只有建立触发器针对每个操作只有建立一个一个。如果针对某个操作即设置了如果针对某个操作即设

24、置了After触发器又设置了触发器又设置了Instead Of触发器触发器,那么那么Instead of触发器一定会激活触发器一定会激活,而而After触发器就不一定会激活了。触发器就不一定会激活了。2021/5/4264.2.14.2.1触发器定义语法触发器定义语法after触发器触发器:create trigger 触发器名触发器名 on 表名表名 with encryption for insert,update,delete as begin 命令行或程序块命令行或程序块 endInstead of触发器触发器:create trigger 触发器名触发器名 on 表名或视图名表名或视

25、图名 instead of insert,update,delete as begin 命令行或程序块命令行或程序块 end2021/5/427实例实例创建一个触发器创建一个触发器,向向manager中插入一条记录中插入一条记录,同时创建一个数据库表并向表中插入两条记同时创建一个数据库表并向表中插入两条记录。录。(1)打开打开Microsoft SQL Server Manager管理器。管理器。(2)新建一个数据库引擎查询文档。新建一个数据库引擎查询文档。(3)在数据库引擎查询文档中输入如下代码在数据库引擎查询文档中输入如下代码:Use Material_Data1(4)按键盘上的按键盘上的

26、F5”键键,显示如下提示信息显示如下提示信息:命令已成功完成。命令已成功完成。(5)这样就打开要使用的数据库。这样就打开要使用的数据库。2021/5/428实例实例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

27、 triuser(usermame,userpwd)values(王明王明,222)insert into triuser(usermame,userpwd)values(刘芳刘芳,333)end2021/5/429实例实例(6)选择创建触发器的代码选择创建触发器的代码,按键盘上的按键盘上的F5”键键,显示显示如下提示信息如下提示信息:命令已成功完成。命令已成功完成。(7)这样就成功创建了触发器。在这里要注意这样就成功创建了触发器。在这里要注意,只是只是创建了触发器创建了触发器,并没有执行触发器并没有执行触发器中的代码中的代码,即表即表triuser还不存在还不存在,当然该表中也不会有记录。当

28、然该表中也不会有记录。(8)下面来通过对下面来通过对manager表的更新操作调用触发表的更新操作调用触发器器hytriggerl,具体代码如下具体代码如下:Update manager set wage=wage+100 where managerNo=001(9)选择选择SQL语句语句,按下键盘上的按下键盘上的F5”键执行该键执行该SQL语句语句,显示如图显示如图4.5提示信息提示信息:2021/5/430实例实例 图图4.5 执行触发器执行触发器 2021/5/431实例实例 (10)提示信息表示影响了四行提示信息表示影响了四行,即更新了仓即更新了仓库表中的一条记录库表中的一条记录,创建

29、创建triuser表表,并向该并向该表中插入三条记录表中插入三条记录,下面通过下面通过select*from triuser来显示触发器来显示触发器产生新表中的数据信息产生新表中的数据信息,如图如图4.6所示。所示。2021/5/432实例实例图图4.6 显示触发器执行后的结果显示触发器执行后的结果 2021/5/433实例实例在订单明细表里在订单明细表里,折扣字段不能大于折扣字段不能大于0.6,如果插入记如果插入记录时录时,折扣大于折扣大于0.6的话的话,回滚操作。回滚操作。CREATE TRIGGER 订单明细订单明细_Insert ON 订单明细订单明细 AFTER INSERT AS

30、BEGIN if(Select 折扣折扣 from inserted)0.6 begin print 折扣不能大于折扣不能大于0.6 Rollback Transaction end END GO2021/5/434实例实例在订单明细表里在订单明细表里,折扣字段不能大于折扣字段不能大于0.6,如果插入记录时如果插入记录时,折扣大于折扣大于0.6的话的话,回滚操作。回滚操作。CREATE TRIGGER 订单明细订单明细_Insert ON 订单明细订单明细 Instead Of INSERTAS BEGIN declare 订单订单ID int,产品产品ID int,单价单价 money,数量

31、数量 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,单价单价,数量数量,折折扣扣)20

32、21/5/435实例实例如果更改了学生的学号如果更改了学生的学号,希望他的借书记录仍然与这希望他的借书记录仍然与这个学生相关个学生相关(也就是同时更改借书记录表的学号也就是同时更改借书记录表的学号)Create Trigger truStudent On Student -在在Student表中创建触发表中创建触发器器 for Update -为什么事件触发为什么事件触发 As -事件触发后所要做的事情事件触发后所要做的事情 if Update(StudentID)begin Update BorrowRecord Set StudentID=i.StudentID From BorrowRe

33、cord br,Deleted d,Inserted i Where br.StudentID=d.StudentID end 2021/5/436实例实例如果该学生已经毕业如果该学生已经毕业,希望删除他的学希望删除他的学号的同时号的同时,也删除它的借书记录。也删除它的借书记录。Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br,Delted d Where br.StudentID=d.StudentID 2021/5/4374.2.2 4.2.2 查看触发器基

34、本信息查看触发器基本信息通过通过sp_help能够查看触发器的基本信能够查看触发器的基本信息息 触发器名、所有者、创建者和创建时间。触发器名、所有者、创建者和创建时间。其语法格式如下其语法格式如下:exec sp_help 触发器名触发器名 如查看触发器如查看触发器hytriggerl信息信息exec sp_help hytriggerl2021/5/438实例实例 图图4.7 查看触发器基本信息查看触发器基本信息 2021/5/4394.2.3 4.2.3 查看触发器代码查看触发器代码通过通过sp_helptext能够查看触发器能够查看触发器SQL的代码信息的代码信息,但要注意如果在创建触发

35、但要注意如果在创建触发器时使器时使with encrypdon选项选项,则执行该则执行该命令也看不到命令也看不到SQL代码。其语法格式如代码。其语法格式如下下:exec sp_helptext 触发器名。触发器名。若要查看触发器若要查看触发器hytriggerl代码代码 exec sp_helptext hytriggerl2021/5/440实例实例图图4.8 查看触发器查看触发器hytriggerl代码代码2021/5/4414.2.4 4.2.4 修改触发器修改触发器 修改触发器的方法很简单修改触发器的方法很简单,利用利用T-SQL修改触修改触发器的语法格式如下发器的语法格式如下:aft

36、er触发器触发器:alter trigger 触发器名触发器名 On 表名表名with encryption for insert,update,delete as begin 命令行或程序块命令行或程序块 End2021/5/442实例实例修改修改hytriggerl插入行数据插入行数据刘芳刘芳为为张清张清alter trigger hytriggerl on manager for update as begin create table triuser(userid int identity(1,1)primary key,usermame varchar(50),userpwd var

37、char(50)insert into triuser(username,userpwd)values(李明李明,111)insert into triuser(usermame,userpwd)values(王明王明,222)insert into triuser(usermame,userpwd)values(张清张清,333)end2021/5/443修改修改instead ofinstead of触发器触发器 alter trigger 触发器名触发器名 on 表名或视图表名或视图名名 instead of insert,update,delete as begin 命令行或程序块命令

38、行或程序块 end 修改触发器与创建触发器几乎相同修改触发器与创建触发器几乎相同,只只是把是把create改为改为alter即可。即可。2021/5/4444.2.5删除触发器删除触发器的方法很简单删除触发器的方法很简单,利用利用T-SQL删除触发器的语法格式如下删除触发器的语法格式如下:drop trigger 触发器名触发器名2021/5/4454.2.6 DDL4.2.6 DDL触发器触发器DDL触发器是触发器是SQL Server 2005新增的一个新增的一个触发器类型触发器类型,是一种特殊的触发器是一种特殊的触发器,它在响应数它在响应数据定义语言（据定义语言（DDL）语句时触发。一般

39、用于）语句时触发。一般用于数据库中执行管理任务。数据库中执行管理任务。与与DML触发器一样触发器一样,DDL触发器也是通过事件触发器也是通过事件来激活来激活,并执行其中并执行其中 的的SQL语句的。但与语句的。但与DML触发器不同触发器不同,DML触发器是响应触发器是响应Insert、Update或或Delete语句而激活的语句而激活的,DDL触发器触发器是响应是响应Create、Alter或或Drop开头的语句而开头的语句而激活的。激活的。2021/5/446以下几种情况下可以使用以下几种情况下可以使用DDL触发器触发器:数据库里的库架构或数据表架构很重要数据库里的库架构或数据表架构很重要,

40、不不允许被修改。允许被修改。防止数据库或数据表被误操作删除。防止数据库或数据表被误操作删除。在修改某个数据表结构的同时修改另一个在修改某个数据表结构的同时修改另一个数据表的相应的结构。数据表的相应的结构。要记录对数据库结构操作的事件要记录对数据库结构操作的事件2021/5/447语法语法CREATE TRIGGER 触发器名触发器名 ON ALL SERVER或或DATABASEFOR 或或 AFTER 激活激活DDL触发器的事件触发器的事件AS 要执行的要执行的SQL语句语句2021/5/448实例实例启动启动Management Studio,登录到指定的服务器上。登录到指定的服务器上。在

41、在【对象资源管理器】【对象资源管理器】下选择下选择【数据库】【数据库】,定位到定位到【Northwind】数据库上。】数据库上。单击单击【新建查询】【新建查询】按钮按钮,在弹出的在弹出的【查询编辑器】【查询编辑器】的的编辑区里输入以下代码编辑区里输入以下代码:CREATE TRIGGER 禁止对数据表操作禁止对数据表操作ON DATABASE FOR DROP_TABLE,ALTER_TABLEAS PRINT 对不起对不起,您不能对数据表进行操作您不能对数据表进行操作 ROLLBACK;2021/5/449实例实例建立一个建立一个DDL触发器触发器,用于保护当前用于保护当前SQL Serve

42、r服务器里所有数据库不能被删除。具服务器里所有数据库不能被删除。具体代码如下体代码如下:CREATE TRIGGER 不允许删除数据库不允许删除数据库 ON all server FOR DROP_DATABASE AS PRINT 对不起对不起,您不能删除数据库您不能删除数据库 ROLLBACK;GO2021/5/450在在Management Studio如果要修改如果要修改DDL触发器内容触发器内容,就只能先删除该触发就只能先删除该触发器器,再重新建立一个再重新建立一个DDL触发器。触发器。2021/5/451触发器的创建例例:对对S表定义一个删除触发器表定义一个删除触发器,使得当删除学

43、生记录时使得当删除学生记录时,将将S_C表中相应的选课记录删除。表中相应的选课记录删除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）2021/5/452触发器的创建例例:对对S_C表定义一个插入触发器表定义一个插入触发器,使得当插入选课记录时使得当插入选课记录时,检检查所参照的学生学号和课程号是否存在查所参照的学生学号和课程号是否存在,如果不存在如果不存在,则撤消则撤消所做的插入操作。所做的插入操作。S（SNO,SNAME）C（CNO,CNAME）S_C（SNO,CNO,SCORE）2021/5/453触发器的创建例例:当插入或更新学生成绩时当插入或更新学生成绩时,触发器检查该

44、课程是否为考查触发器检查该课程是否为考查课课,若是若是,则通过的成绩只能以则通过的成绩只能以60分计分计,未通过的只能以未通过的只能以40分分计。计。涉及的表结构涉及的表结构:C1(Cno,Cname,Ctype)S_C(Sno,Cno,Score)2021/5/454触发器的创建例例:对职工表定义一个插入触发器对职工表定义一个插入触发器,使得当插入职工记录时使得当插入职工记录时,检检查所参照的仓库元组是否存在查所参照的仓库元组是否存在,如果不存在如果不存在,则撤消所做的插则撤消所做的插入操作。入操作。仓库（仓库号仓库（仓库号,城市城市,面积）面积）职工（仓库号职工（仓库号,职工号职工号,工资

45、）工资）2021/5/455触发器的创建例例:对职工表定义一个更新触发器对职工表定义一个更新触发器,使得当职工变换所属仓使得当职工变换所属仓库时库时,检查所参照的仓库元组是否存在检查所参照的仓库元组是否存在,如果不存在如果不存在,则撤消则撤消所做的更新操作所做的更新操作,如果新的仓库号是如果新的仓库号是WH2则将工资提高则将工资提高10%。仓库（仓库号仓库（仓库号,城市城市,面积）面积）职工（仓库号职工（仓库号,职工号职工号,工资）工资）2021/5/456触发器的创建CREATE TRIGGER TR_UPDATE ON 职工职工 FOR UPDATE AS DECLARE WHNO CHA

46、R(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 END2021/5/

47、457触发器的创建CREATE TRIGGER WH_INSON 职工职工 FOR INSERTAS IF NOT EXISTS(SELECT*FROM 仓库仓库 WHERE 仓库号仓库号=(SELECT 仓库号仓库号 FROM INSERTED)BEGIN RAISERROR(非法仓库号非法仓库号!,1,1)ROLLBACK TRANSACTIONENDINSERT 职工职工 VALUES(WH5,E10,1600)IN2021/5/458触发器的创建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 TRANSACTIONEND2021/5/459