存储过程触发器.ppt

《存储过程触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程触发器.ppt（98页珍藏版）》请在三一办公上搜索。

1、数据库原理与应用教程SQL Server,第10章 存储过程和触发器,在SQL Server 2008应用操作中，存储过程和触发器都扮演着相当重要的角色。存储过程可以使用户对数据库的管理工作变得更容易。存储过程是SQL语句和可选流程控制语句的预编译集合，它以一个名称存储并作为一个单元处理，能够提高系统的应用效率和执行速度。SQL Server提供了许多系统存储过程以管理SQL Server和显示有关数据库和用户的信息。,触发器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就会自动触发执行。触发器是与表紧密联系在一起的，它在特定的表上定义，并与指定的数据修改事件相对应，它是一

2、种功能强大的工具，它可以扩展SQL Server完整性约束默认值对象和规则的完整性检查逻辑，实施更为复杂的数据完整性约束。本章主要介绍存储过程的基本概念，存储过程的创建、修改、调用和删除操作；触发器的基本概念，触发器的分类，触发器的创建、修改和删除，以及触发器的应用。,10.1 存储过程当开发一个应用程序时，为了易于修改和扩充，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句，即是“过程”(Procedure)。SQL Server 2008的存储过程（Stored Procedure）包含一些T-SQL语句并以特定的名称存储在数

3、据库中。可以在存储过程中声明变量、有条件地执行以及其他各项强大的程序设计功能。,10.1.1 存储过程概述存储过程是一种数据库对象，独立存储在数据库内。存储过程可以接受输入参数、输出参数，返回单个或多个结果集以及返回值，由应用程序通过调用执行。存储过程是SQL Server中一个非常有用的工具。SQL Server支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用，也可以从另一个过程或触发器调用，参数可以被传递和返回，出错代码也可以被检验。,存储过程最主要的特色是当写完一个存储过程后即被翻译成可执行码存储在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并

4、且提供存储过程所需的参数就可以得到所要的结果而不必再去编辑T-SQL命令。一般来讲，应使用SQL Server中的存储过程而不使用存储在客户计算机本地的 T-SQL 程序，其优势主要表现在：,(1)允许模块化程序设计。只需创建一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。如果业务规则发生变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端的应用程序。这样所有调用该存储过程的应用程序就会遵循新的业务规则。,(2)允许更快速地执行。如果某操作需要大量 T-SQL 语句或需重复执行，存储过程将比 T

5、-SQL 批处理代码的执行要快。创建存储过程时对其进行分析和优化并预先编译好放在数据库内，减少编译语句所花的时间；编译好的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其他次执行的速度会有明显提高。而客户计算机本地的T-SQL 语句每次运行时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。,(3)减少网络流量。一个需要数百行 T-SQL 语句的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。(4)可作为安全机制使用。数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限。这

6、些对象将由存储过程来执行操作，另外，存储过程可以加密，这样用户就无法阅读存储过程中的T-SQL语句。这些安全特性将数据库结构和数据库用户隔离开来，这也进一步保证数据的完整性和可靠性。With ENCRYPTION,10.1.2 存储过程的类型1.系统存储过程存储过程在运行时生成执行方式，其后在运行时执行速度很快。SQL Server 2008中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQL Server提供支持。通过系统存储过程，S

7、QL Server中的许多管理性或信息性的活动（如获取数据库和数据库对象的信息）都可以被顺利有效地完成。,尽管这些系统存储过程被存储在master数据库中，但是仍可以在其他数据库中对其进行调用，在调用时，不必在存储过程名前加上数据库名。而且当创建一个数据库时，一些系统存储过程会在新的数据库中被自动创建。SQL Server 2008系统存储过程是为用户提供方便的，它们使用户可以很容易地从系统表中提取信息、管理数据库，并执行涉及更新系统表的其他任务。,如果过程以SP_开始，又在当前数据库中找不到，SQL Server 2008就在master数据库中寻找。当系统存储过程的参数是保留字或对象名，且

8、对象名由数据库或拥有者名字限定时，整个名字必须包含在单引号中。一个用户可以在所有数据库中执行一个系统存储过程的许可权，否则在任何数据库中都不能执行系统存储过程。,2本地存储过程 本地存储过程也就是用户自行创建并存储在用户数据库中的存储过程，一般所说的存储过程指的就是本地存储过程。用户创建的存储过程是由用户创建并能完成某一特定功能(如查询用户所需的数据信息)的存储过程。,3临时存储过程 临时存储过程可分为以下两种：(1)本地临时存储过程不论哪一个数据库是当前数据库，如果在创建存储过程时，其名称以“#”号开头，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程。本地临时存储过程只有

9、创建它的连接的用户才能够执行它，而且一旦这位用户断开与SQL Server的连接，本地临时存储过程就会自动删除，当然，这位用户也可以在连接期间用DROP PROCEDURE命令删除他所创建的本地临时存储过程。,(2)全局临时存储过程不论哪一个数据库是当前数据库，只要所创建的存储过程名称是以两个“#”号开头，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。全局临时存储过程一旦创建，以后连接到SQL Server 2008的任意用户都能执行它，而且不需要特定的权限。,当创建全局临时存储过程的用户断开与SQL Server 2008的连接时，SQL Server 2008将检查是

10、否有其他用户正在执行该全局临时存储过程，如果没有，便立即将全局临时存储过程删除；如果有，SQL Server 2008会让这些正在执行中的操作继续进行，但是不允许任何用户再执行全局临时存储过程，等到所有未完成的操作执行完毕后，全局临时存储过程就会自动删除。不论创建的是本地临时存储过程还是全局临时存储过程，只要SQL Server 2008停止运行，它们将不复存在。,4远程存储过程 在SQL Server 2008中，远程存储过程是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。5扩展存储过程扩展存储过程是用户可以使用外部程序语言（例如C语言）编写的存

11、储过程。显而易见，扩展存储过程可以弥补SQL Server 2008的不足，并按需要自行扩展其功能。扩展存储过程在使用和执行上与一般的存储过程完全相同，为了区别，扩展存储过程的名称通常以XP_开头。扩展存储过程是以动态链接库(DLL)的形式存在，能让SQL Server 2008动态地装载和执行。扩展存储过程一定要存储在系统数据库master中。,10.1.3 创建存储过程在SQL Server 2008中创建存储过程主要有两种方式：一种方式是在SQL Server Management Studio中创建存储过程；另一种方式是通过在查询窗口中执行T-SQL语句创建存储过程。1在SQL Ser

12、ver Management Studio中创建存储过程 在SQL Server Management Studio中创建存储过程的步骤如下：(1)打开SQL Server Management Studio，展开要创建存储过程的数据库，展开“可编程性”选项，可以看到存储过程列表中系统自动为数据库创建的系统存储过程。右键单击“存储过程”选项，选择“新建存储过程”命令,(2)出现创建存储过程的T-SQL命令，编辑相关的命令即可。(3)命令编辑成功后，进行语法检查，然后单击“！”按钮，至此一个新的存储过程建立成功。注意：用户只能在当前数据库中创建存储过程，数据库的拥有者有默认的创建权限，权限也可以

13、转让给其他用户。,2利用T-SQL语句创建存储过程SQL Server 2008提供了CREATE PROCEDURE创建存储过程。语法格式如下：CREATE PROC|PROCEDURE procedure_name;number parameter data_type VARYING=default OUT PUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,.n FOR REPLICATIONAS sql_statement.n,在创建存储过程时，应当注意以下几点。(1)存储过程最大不能超过128MB。(2)用户定义的存储过程只能在

14、当前数据库中创建，但是临时存储过程通常是在tempdb数据库中创建的。(3)在一条T-SQL语句中CREATE PROCEDURE不能与其他T-SQL 语句一起使用。,(4)SQL Server允许在存储过程创建时引用一个不存在的对象，在创建的时候，系统只检查创建存储过程的语法。存储过程在执行的时候，如果缓存中没有一个有效的计划，则会编译生成一个可执行计划。只有在编译的时候，才会检查存储过程所引用的对象是否都存在。这样，如果一个创建存储过程语句值要在语法上没有错误，即使引用了不存在的对象也是可以成功执行的。但是，如果在执行的时候，存储过程引用了一个不存在的对象，这次执行操作将会失败。,【例10

15、-1】在“教学库”创建无参存储过程，查询每个同学各门功课的平均成绩。即使没有输出参数也可返回内容，内嵌表值或多语句表值。USE 教学库GOCREATE PROCEDURE student_avgAS SELECT sno,avg(grade)as 平均分 FROM sc GROUP BY snoGO命令执行，创建存储过程成功,【例10-2】在教学库创建带参数的存储过程，查询某个同学的基本信息。USE 教学库GOCREATE PROCEDURE GetStudent number char(7)AS SELECT*FROM student WHERE sno=numberGO,【例10-3】在“

16、教学库”创建带参数的存储过程，修改某个同学某门课的成绩。USE 教学库GOCREATE PROCEDURE Update_score number char(7),cn char(4),score int AS UPDATE sc SET grade=scoreWHERE sno=number and cno=cn,【例10-4】使用流程控制语句，在“仓库库存”数据库创建存储过程，修改某商品的单价，如果库存总量大于某个值，就打九折。USE 仓库库存GOCREATE PROCEDURE Update_price number char(3),s floatAS IF(SELECT sum(数量)

17、FROM 库存情况 WHERE 商品编号=number)s UPDATE 商品 SET 单价=单价*0.9WHERE 商品编号=number,【例10-5】在“教学库”创建带有参数和默认值（通配符）的存储过程，从“学生表”中返回指定的学生（提供姓名）的信息。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有学生的信息。USE 教学库GOCREATE PROCEDURE Student_Name name varchar(40)=%AS SELECT*FROM student WHERE sname LIKE nameGO,【例10-6】在教学库中创建带OUTPUT参数的存储过程，

18、用于计算指定学生的平均成绩，存储过程中使用一个输入参数（学号）和一个输出参数（平均成绩）。USE 教学库GOCREATE PROCEDURE Pname p_n varchar(20),aveage int OUTPUTASSELECT aveage=avg(grade)FROM sc WHERE sno=p_nGO,10.1.4 执行存储过程执行存储过程可以使用SQL Server Management Studio界面，也可以使用T-SQL 语句中的EXECUTE命令。1使用SQL Server Management Studio执行存储过程 在SQL Server Management

19、Studio中执行存储过程的步骤如下：(1)打开SQL Server Management Studio，展开存储过程所在的数据库，展开“可编程性”选项，右键单击存储过程名，如“教学库”中的“GetStudent”，在弹出的快捷菜单中选择“执行存储过程”命令(2)进入“执行过程”对话框，输入要查询的学生的学号，如“0100111”,2.使用T-SQL 语句执行存储过程如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。对于存储过程的所有者或任何一名对此过程拥有EXECUTE权限的用户，都可以执行此存储过程。如果需要在启动SQL Server时，系统自动执行存

20、储过程，可以使用sp_procoption进行设置。如果被调用的存储过程需要参数输入时，在存储过程名后逐一给定，每一个参数用逗号隔开，不必使用括号。如果没有使用参数名=default这种方式传入值，则参数的排列必须和建立存储过程所定义的次序对应，用来接受输出值的参数则必须加上OUTPUT。,EXECUTE可以简写为EXEC，如果存储过程是批处理中的第一条语句，那么可以省略EXECUTE关键字。对于以sp_开头的系统存储过程，系统将在master数据库中查找。如果执行用户自定义的sp_开头的存储过程，就必须用数据库名和所有者名限定。EXECUTE语句的语法格式为：EXECUTE return_s

21、tatus=procedure_name;numberparameter=value|parameter=variable OUTPUTWITH RECOMPILE,【例10-7】执行存储过程student_avg。EXECUTE student_avg【例10-8】执行带参数的存储过程GetStudent，查询学号为0100111的学生的基本信息。EXECUTE GetStudent 0100111【例10-9】执行修改成绩的存储过程Update_score。EXECUTE Update_score 0100111,C007,100【例10-10】执行修改单价的存储过程Update_pric

22、e。EXECUTE Update_price ds-018,20,【例10-11】执行带有参数和默认值（通配符）的存储过程Student_Name。(1)显示所有学生的信息：EXECUTE Student_Name(2)显示“王小明”所有学生的信息：EXECUTE Student_Name 王小明【例10-12】执行带有输入和输出参数的存储过程Pname。Declare avgage intEXECUTE Pname 冰箱,avgage OUTPUTPrint 冰箱的平均价格：+str(avgage),10.1.5 查看存储过程查看存储过程可以使用SQL Server Management S

23、tudio界面，也可以使用T-SQL 语句。1使用SQL Server Management Studio查看(1)打开SQL Server Management Studio，展开存储过程所在的数据库，展开“可编程性”选项，右键单击存储过程名，如“教学库”中的“GetStudent”，在弹出的快捷菜单中选择“编写存储过程脚本为”命令，再选择“CREATE到”，再选择“新查询编辑器窗口”(2)进入“查询编辑器”窗口，可以看到“CREATE PROCEDURE”代码,2使用T-SQL 语句查看存储过程可以执行系统存储过程sp_helptext，用于查看创建存储过程的命令语句；也可以执行系统存储过

24、程sp_help，用于查看存储过程的名称、拥有者、类型、创建时间，以及存储过程中所使用的参数信息。其语法格式分别为：sp_helptext 存储过程名称sp_help 存储过程名称【例10-13】查看存储过程Pname的相关信息。(1)sp_helptext Pname(2)sp_help Pname,10.1.6 修改和删除存储过程1修改存储过程可以在SQL Server Management Studio中；也可以通过T-SQL中的ALTER语句来完成。ALTER语句的语法格式如下：ALTER PROC|PROCEDURE procedure_name;number parameter d

25、ata_type VARYING=default OUT PUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION,.n FOR REPLICATIONAS sql_statement.n,【例10-14】修改存储过程Pname，除了用于计算指定的商品的平均价格外，还用于计算此类商品的库存总数量，存储过程中使用一个输入参数（商品名）和两个输出参数（平均价格和总数量）。USE 仓库库存GOALTER PROCEDURE Pname p_n varchar(20),aveage int OUTPUT,sum int OUTPUTAS SELECT

26、aveage=avg(单价),sum=sum(数量)FROM 商品 as s,库存情况 as kWHERE 商品名称=p_n and s.商品编号=k.商品编号GO,【例10-15】执行修改后的存储过程Pname。Declare avgage int,sum intEXECUTE Pname 冰箱,avgage OUTPUT,sum OUTPUTPrint 冰箱的平均价格：+str(avgage)+库存总量：+str(sum),2删除存储过程对于不需要的存储过程可以在SQL Server Management Studio中鼠标右击要删除的存储过程，选择“删除”命令将其删除，也可以使用T-SQ

27、L语句中的DROP PROCEDURE命令将其删除。如果另一个存储过程调用某个己删除的存储过程，则SQL Server 2008会在执行该调用过程时显示一条错误信息。如果定义了同名和参数相同的新存储过程来替换己删除存储过程，那么引用该过程的其他过程仍能顺利执行。,删除存储过程的T-SQL语句的语法格式为：DROP PROCEDURE procedure_name,nprocedure_name指要删除的存储过程或存储过程组的名称。【例10-16】删除存储过程PnameDROP PROCEDURE Pname,存储过程与函数的区别：1.存储过程可以返回0个或多个参数值(也可执行多个操作而无返回)

28、，而函数只能返回单个值或表对象，函数中无法使用select只查询而不处理其值或是返回值；2.函数可以嵌入到sql语句中执行，如在from子句中，而存储过程只能使用exec单独调用。3.函数实现一些简单功能，存储过程相对功能强大些。,根据输入的学号，计算学生的平均成绩，并显示绩点（平均成绩在90-100为4个绩点；平均成绩在80-90为3个绩点；平均成绩在70-80为2个绩点；平均成绩在60-70为1个绩点；平均成绩在60以下绩点为0），用函数、游标和存储过程分别实现！显示如下：学号 平均成绩 绩点,create function f1(snotemp smallint)-用函数实现return

29、s newtab table(sno1 smallint,pjcj numeric(5,2),jd int)asbegin declare tempcj numeric(5,2),tempjd char(2)select tempcj=avg(grade)from sc where sno=snotemp set tempjd=case when tempcj=90 then 4 when tempcj=80 then 3 when tempcj=70 then 2 when tempcj=60 then 1 else 0 end insert into newtab values(snote

30、mp,tempcj,cast(tempjd as int)returnendselect*from dbo.f1(2),create proc p1-用存储过程实现1snotemp smallintasbegin declare tempcj numeric(5,2),tempjd char(2)select tempcj=avg(grade)from sc where sno=snotemp set tempjd=case when tempcj=90 then 4 when tempcj=80 then 3 when tempcj=70 then 2 when tempcj=60 then

31、 1 else 0 end print 课程号+cast(snotemp as varchar(20)+平均成绩+cast(tempcj as varchar(20)+绩点+tempjdendexec p1 1,create proc p2-用存储过程实现2，带有输出参数snotemp smallint,shuchu char(100)outputasbegin declare tempcj numeric(5,2),tempjd char(2)select tempcj=avg(grade)from sc where sno=snotemp set tempjd=case when temp

32、cj=90 then 4 when tempcj=80 then 3 when tempcj=70 then 2 when tempcj=60 then 1 else 0 end set shuchu=课程号+cast(cnotemp as varchar(20)+平均成绩+cast(tempcj as varchar(20)+绩点+tempjdEnddeclare tm char(100)exec p2 1,shuchu=tm outputprint tm,-用case语句实现每个学生的平均成绩、绩点select sno,avg(grade),case when avg(grade)=90

33、then 4 when avg(grade)=80 then 3 when avg(grade)=70 then 2 when avg(grade)=60 then 1 else 0 end from sc group by sno,-游标实现declare a1 char(20),a2 floatdeclare c1 cursor for select sno,avg(grade)from sc group by snoopen c1fetch next from c1 into a1,a2while FETCH_STATUS=0beginselect a1,case when a290 t

34、hen 4 when a2=80 then 3 when a2=70 then 2 when a2=60 then 1 else 0 end fetch next from c1 into a1,a2 end close c1,10.2 触发器就本质而言，触发器也是一种存储过程，它在特定语言事件发生时自动执行。10.2.1 触发器概述在SQL Server 2008数据库系统中，存储过程和触发器都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程，它是一种在基本表被修改时自动执行的内嵌过程，主要通过事件进行触发而被执行，而存储过程可以通过存储过程名字而被直接调用。,当对某一张

35、表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL Server 2008就会自动执行触发器所定义的SQL语句。从而确保对数据的处理符合由这些SQL语句所定义的规则，触发器的主要作用是其能实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。,触发器的优点：由于在触发器中可以包含复杂的处理逻辑，因此，应该将触发器用来保持低级的数据的完整性，而不是返回大量的查询结果。使用触发器主要可以实现以下操作：(1)强制比CHECK约束更复杂的数据的完整性在数据库中要实现数据的完整性的约束，可以使用CHEC

36、K约束或触发器来实现。但是在CHECK约束中不允许引用其他表中的列来完成检查工作，而触发器可以引用其他表中的列来完成数据的完整性的约束。,(2)使用自定义的错误提示信息 用户有时需要在数据的完整性遭到破坏或其他情况下，使用预先自定义好的错误提示信息或动态自定义的错误提示信息。通过使用触发器，用户可以捕获破坏数据的完整性的操作，并返回自定义的错误提示信息。(3)实现数据库中多张表的级联修改 用户可以通过触发器对数据库中的相关表进行级联修改。,(4)比较数据库修改前后数据的状态触发器提供了访问有INSERT、UPDATE或DELETE语句引起的数据前后状态变化的能力。因此用户就可以在触发器中引用由

37、于修改所影响的记录行。(5)调用更多的存储过程约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多个存储过程。,(6)维护非规范化数据 用户可以使用触发器来保证非规范数据库中的低级数据的完整性。维护非规范化数据与表的级联是不同的。表的级联指的是不同表之间的主外键关系，维护表的级联可以通过设置表的主键与外键的关系来实现。而非规范数据通常是指在表中派生的、冗余的数据值，维护非规范化数据应该通过使用触发器来实现。,10.2.2 触发器的分类1DML触发器DML触发器是当数据库服务器中发生数据操作语言(DML)事件时会自动执行的存储过

38、程。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。,DML 触发器经常用于强制执行业务规则和数据完整性。SQL Server通过ALTERTABLE和CREATE TABLE语句来提供声明性引用完整性。引用完整性是指有关表的主键和外键之间的关系的规则。若要强制实现引用完整性，请在ALTER TABLE和CREATE TABLE中使用PRIMARY KEY和FOREIG

39、N KEY约束。如果触发器表存在约束，则在INSTEAD OF触发器执行之后和AFTER触发器执行之前检查这些约束。如果违反了约束，则将回滚INSTEAD OF触发器操作，并且不激活AFTER触发器。,SQL Server 2008的DML触发器分为两类：(1)AFTER触发器：这类触发器是在记录已经改变完之后，才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用ROLLBACK TRANSACTION语句来回滚本次的操作。以删除记录为例：当SQL Server接收到一个要执行删除操作的SQL语句时，SQL Server先将要删除的记录存放在一个临时表（删除表）里，然后把

40、数据表里的记录删除，再激活AFTER触发器，执行AFTER触发器里的SQL语句。执行完毕之后，删除内存中的删除表，退出整个操作。,(2)INSTEAD OF触发器：与AFTER触发器不同，这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（UPDATE、INSERT、DELETE），而去执行触发器本身所定义的操作。,2DDL 触发器DDL触发器是SQL Server 2005以后的版本新增的一个触发器类型，是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。添加、删除或修改数据库的对象，一旦误操作，可能会导致大麻

41、烦，需要一个数据库管理员或开发人员对相关可能受影响的实体进行代码的重写。为了在数据库结构发生变动而出现问题时，能够跟踪问题和定位问题的根源，我们可以利用DDL触发器来记录类似“用户建立表”这种变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化的繁琐程度。,与DML触发器一样，DDL触发器也是通过事件激活并执行其中的SQL语句的。但与DML触发器不同，DML触发器是响应UPDATE、INSERT或DELETE语句而激活的，DDL触发器是响应CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和UPDATE STATISTICS等语句而激活的。,一般来说，在以下几种情况下可

42、以使用DDL触发器：(1)数据库里的库架构或数据表架构很重要，不允许被修改。(2)防止数据库或数据表被误操作删除。(3)在修改某个数据表结构的同时修改另一个数据表的相应的结构。(4)要记录对数据库结构操作的事件。,10.2.3 创建触发器在创建触发器前，需要注意以下问题。(1)CREATE TRIGGER语句必须是批处理中第一条语句，只能用于一个表或视图。(2)创建触发器的权限默认为表的所有者，不能将该权限转给其他用户。(3)虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建。(4)虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视

43、图。,(5)在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF触发器。(6)虽然TRUNCATE TABLE语句类似于没有WHERE子句的DELETE语句，但不会激发DELETE触发器，因为TRUNCATE TABLE语句没有记录日志。,创建触发器时需指定以下几项内容。(1)触发器的名称。(2)在其上定义触发器的表。(3)触发器将何时激发。(4)激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE多个数据修改语句可激活同一个触发器。,在SQL Server 2008中创建DML触发器主要有两种方式：在SQL Server Managem

44、ent Studio界面或通过在查询窗口中执行T-SQL语句创建DML触发器。1在SQL Server Management Studio中创建DML触发器 在SQL Server Management Studio中创建存储过程的步骤如下：(1)打开SQL Server Management Studio，展开要创建DML触发器的数据库和其中的表或视图（如学生表），右键单击“触发器”选项，选择“新建触发器”命令,(2)出现创建触发器的T-SQL语句，编辑相关的命令即可(3)命令编辑成功后，进行语法检查，然后单击“！执行”按钮，至此一个DML触发器建立成功。,2利用T-SQL语句创建触发器SQ

45、L Server 2008提供了CREATE TRIGGER创建触发器。语法格式如下：CREATE TRIGGER trigger_nameON table_name|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT DELETE UPDATE NOT FOR REPLICATIONAS sql_statement.n,【例10-17】使用DDL触发器limited来防止数据库中的任一表被修改或删除。USE 教学库GOCREATE TRIGGER limited ON databaseFOR DROP_TABLE,ALTER_TABLE AS P

46、RINT 名为limited的触发器不允许您执行对表的修改或删除操作!ROLLBACK,【例10-18】假定某用户要修改学生表，添加一列：出生日期 datetime。ALTER TABLE 学生表ADD 出生日期 datetime所以，当任一用户在“教学库”中试图修改表的结构或删除表时，都会触发limited触发器。该触发器显示提示信息，并回滚用户试图执行的操作。,【例10-19】为学生表创建一个简单DML触发器，在插入和修改数据时，都会自动显示提示信息。USE 教学库GOCREATE TRIGGER reminder ON 学生表FOR INSERT,UPDATE AS print 你在插入

47、或修改学生表的数据,【例10-20】将姓名为“刘尚”的名字改为“刘尚宏”。UPDATE 学生表 SET 姓名=刘尚宏 WHERE 姓名=刘尚【例10-21】为学生表创建一个DML触发器，在插入和修改数据时，都会自动显示所有学生的信息。CREATE TRIGGER print_table ON 学生FOR INSERT,UPDATE AS SELECT*FROM 学生表,【例10-22】将姓名为“李红”的名字改为“李小红”。UPDATE 学生 SET 姓名=李小红 WHERE 姓名=李红【例10-23】在学生表上创建一个DELETE类型的触发器，删除数据时，显示删除学生的个数。CREATE TR

48、IGGER del_count ON 学生表FOR DELETEAS DECLARE count varchar(50)SELECT count=STR(ROWCOUNT)+个学生被删除 SELECT countRETURN,【例10-24】删除所有“计算机”专业的学生。DELETE FROM 学生表 WHERE 专业=计算机在SQL Server 2008里，为每个DML触发器都定义了两个特殊的表，一个是插入表（Inserted），一个是删除表（Deleted）。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没

49、有修改的权限。,在触发器的执行过程中，SQL Server建立和管理这两个临时表。这两个表的结构与触发器所在数据表的结构是完全一致的，其中包含了在激发触发器的操作中插入或删除的所有记录。当触发器的工作完成之后，这两个表也将会从内存中删除。插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。,也就是说，在用户执行INSERT语句时，所有被添加的记录都会存储在In

50、serted表中；在用户执行DELETE语句时，从触发程序表中被删除的行会发送到Deleted表；对于UPDATE语句，SQL Server先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到Inserted表以及触发程序表。,下面利用触发器和这两个特殊的表实现参照完整性约束。先创建一个dept数据库（属性默认），和两个表dept,gongcheng，其结构如下。CREATE TABLE dept(dno char(5)primary key,dname varchar(20),leader varchar(10)CREATE TABLE gongcheng(gno cha