存储过程、触发器和游标

1、数据库原理与应用教程数据库原理与应用教程 SQL Server 存储过程、触发器和游标存储过程、触发器和游标 存储过程、触发器和游标存储过程、触发器和游标 存储过程存储过程 触发器触发器 游标游标 存储过程、触发器和游标存储过程、触发器和游标 1存储过程存储过程 SQL Server 2005的存储过程（的存储过程（Stored Procedure）包含一些）包含一些T-SQL语句并以特定语句并以特定 的名称存储在数据库中。的名称存储在数据库中。 可以在存储过程中声明变量、有条件地执行可以在存储过程中声明变量、有条件地执行 以及其他各项强大的程序设计功能。以及其他各项强大的程序设计功能。 存储

2、过程、触发器和游标存储过程、触发器和游标 1.1 存储过程概述存储过程概述 一种数据库对象一种数据库对象 存储过程可以接受输入参数、输出参数，返存储过程可以接受输入参数、输出参数，返 回单个或多个结果集以及返回值，由回单个或多个结果集以及返回值，由应用程应用程 序通过调用执行序通过调用执行。 SQL Server支持存储过程和系统过程。存储支持存储过程和系统过程。存储 过程是独立存在于表之外的数据对象。可以过程是独立存在于表之外的数据对象。可以 由客户调用，也可以从另一个过程或触发器由客户调用，也可以从另一个过程或触发器 调用，参数可以被传递和返回，出错代码也调用，参数可以被传递和返回，出错代

3、码也 可以被检验。可以被检验。 存储过程、触发器和游标存储过程、触发器和游标 了解存储过程的优点 掌握如何创建存储过程(参数、返回值) 掌握如何调用存储过程 了解为什么需要触发器 理解触发器的工作原理 掌握如何使用inserted表和deleted表 掌握如何创建： INSERT触发器 UPDATE触发器 DELETE触发器 游标的基本更新 理解游标的使用步骤 存储过程、触发器和游标存储过程、触发器和游标 1.1 存储过程概述存储过程概述 在在性能性能方面方面,存储过程有如下优点存储过程有如下优点: (1)预编译预编译:存储过程预先编译好放在数据库内，存储过程预先编译好放在数据库内，减少编减少

4、编 译译语句所花的时间。语句所花的时间。 (2)缓存缓存:编译好的存储过程会进入缓存，所以对于经常编译好的存储过程会进入缓存，所以对于经常 执行的存储过程，除了第一次执行外，其他次执行的速执行的存储过程，除了第一次执行外，其他次执行的速 度会有明显提高。度会有明显提高。 (3)减少网络传输减少网络传输:特别对于处理一些数据的存储过程，特别对于处理一些数据的存储过程， 不必像直接用不必像直接用T-SQL语句实现那样多次传送数据到客户语句实现那样多次传送数据到客户 端。端。 (4)更好的利用服务器内存更好的利用服务器内存:特别对于处理中间数据量不特别对于处理中间数据量不 大的情况，存储过程中可以利

5、用存放在内存的大的情况，存储过程中可以利用存放在内存的表变量表变量。 存储过程、触发器和游标存储过程、触发器和游标 1.1 存储过程概述存储过程概述 一般来讲，应使用一般来讲，应使用SQL Server中的中的存储过程存储过程而而 不使用存储在客户计算机本地的不使用存储在客户计算机本地的 T-SQL 程序，程序， 其其优势优势主要表现在：主要表现在： (1)允许模块化程序设计。允许模块化程序设计。 (2)允许更快速地执行。允许更快速地执行。 (3)减少网络流量。减少网络流量。 (4)可作为安全机制使用。可作为安全机制使用。 存储过程、触发器和游标存储过程、触发器和游标 1.2存储过程的类型存储

6、过程的类型 1.系统存储过程系统存储过程 主要存储在主要存储在master数据库中并以数据库中并以sp_为前缀，为前缀， 并且系统存储过程主要是从系统表中获取信息，并且系统存储过程主要是从系统表中获取信息， 从而为数据库系统管理员管理从而为数据库系统管理员管理SQL Server提供提供 支持。支持。 通过系统存储过程，通过系统存储过程，SQL Server中的许多管理中的许多管理 性或信息性的活动（如获取数据库和数据库对性或信息性的活动（如获取数据库和数据库对 象的信息）都可以被顺利有效地完成。象的信息）都可以被顺利有效地完成。 存储过程、触发器和游标存储过程、触发器和游标 1.2存储过程的

7、类型存储过程的类型 2本地存储过程本地存储过程 本地存储过程也就是用户自行创建并存储在用本地存储过程也就是用户自行创建并存储在用 户数据库中的存储过程，一般所说的存储过程户数据库中的存储过程，一般所说的存储过程 指的就是本地存储过程。指的就是本地存储过程。 用户创建的存储过程是由用户创建并能完成用户创建的存储过程是由用户创建并能完成某一特某一特 定功能定功能(如查询用户所需的数据信息如查询用户所需的数据信息)的存储过程。的存储过程。 存储过程、触发器和游标存储过程、触发器和游标 1.2存储过程的类型存储过程的类型 3临时临时存储过程存储过程 临时存储过程可分为以下两种：临时存储过程可分为以下两

8、种： (1)本地临时存储过程本地临时存储过程 不论哪一个数据库是当前数据库，如果在创建存储过程不论哪一个数据库是当前数据库，如果在创建存储过程 时，其名称以时，其名称以“#”号开头，则该存储过程将成为一个存号开头，则该存储过程将成为一个存 放在放在tempdb数据库中的本地临时存储过程。数据库中的本地临时存储过程。 (2)全局临时存储过程全局临时存储过程 不论哪一个数据库是当前数据库，只要所创建的存储过不论哪一个数据库是当前数据库，只要所创建的存储过 程名称是以两个程名称是以两个“#”号开头，则该存储过程将成为一个号开头，则该存储过程将成为一个 存储在存储在tempdb数据库中的全局临时存储过

9、程。数据库中的全局临时存储过程。 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 1在在SQL Server Management Studio中创建存储过程中创建存储过程 步骤如下：步骤如下： (1)打开打开SQL Server Management Studio，展开要创建，展开要创建 存储过程的数据库，展开存储过程的数据库，展开“可编程性可编程性”选项，可以看到存选项，可以看到存 储过程列表中系统自动为数据库创建的系统存储过程。右储过程列表中系统自动为数据库创建的系统存储过程。右 键单击键单击“存储过程存储过程”选项，选选项，选“新建存储过程新建存储过程”

10、命令。命令。 (2)出现创建存储过程的出现创建存储过程的T-SQL命令，编辑相关的命令即可。命令，编辑相关的命令即可。 (3)命令编辑成功后，进行语法检查，然后单击命令编辑成功后，进行语法检查，然后单击“确定确定”按按 钮，至此一个新的存储过程建立成功。钮，至此一个新的存储过程建立成功。 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 2利用利用T-SQL语句创建存储过程语句创建存储过程 CREATE PROCEDURE创建存储过程，语法格式如下：创建存储过程，语法格式如下： CREATE PROC | PROCEDURE procedure_name ; nu

11、mber parameter data_type VARYING = default OUT PUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ,.n FOR REPLICATION AS sql_statement .n 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 在创建存储过程时，应当注意以下几点。在创建存储过程时，应当注意以下几点。 (1)存储过程最大不能超过存储过程最大不能超过128MB。 (2)用户定义的存储过程只能在当前数据库中创建，用户定义的存储过程只能在当前数据库中创建，

12、 但是临时存储过程通常是在但是临时存储过程通常是在tempdb数据库中创建数据库中创建 的。的。 (3)在一条在一条T-SQL语句中语句中CREATE PROCEDURE 不能与其他不能与其他T-SQL 句一起使用。句一起使用。 (4)SQL Server允许在存储过程创建时引用一个不允许在存储过程创建时引用一个不 存在的对象，在创建的时候，系统只检查创建存存在的对象，在创建的时候，系统只检查创建存 储过程的语法。储过程的语法。 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 例例11-1在教学库创建无参存储过程，在教学库创建无参存储过程， 查询每个同学各门功课

13、的平均成绩。查询每个同学各门功课的平均成绩。 CREATE PROCEDURE student_avg AS SELECT 学生号学生号, avg(成绩成绩) as 平均分平均分 FROM 选课选课 GROUP BY 学生号学生号 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 例例11-2在教学库创建带参数的存储过程，在教学库创建带参数的存储过程， 查询某个同学的基本信息。查询某个同学的基本信息。 CREATE PROCEDURE GetStudent number char(7) AS SELECT * FROM 学生学生 WHERE 学生号学生号= num

14、ber 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 例例11-3在教学库创建带有参数和默认值（通配符）在教学库创建带有参数和默认值（通配符） 的存储过程，从学生表中返回指定的学生（提供的存储过程，从学生表中返回指定的学生（提供 姓名）的信息。该存储过程对传递的参数进行模姓名）的信息。该存储过程对传递的参数进行模 式匹配，如果没有提供参数，则返回所有学生的式匹配，如果没有提供参数，则返回所有学生的 信息。信息。 CREATE PROCEDURE Student_Name name varchar(40) = % AS SELECT * FROM 学生学生 WH

15、ERE 姓名姓名 LIKE name 存储过程、触发器和游标存储过程、触发器和游标 1.3 创建存储过程创建存储过程 例例11-4在仓库库存数据库创建带在仓库库存数据库创建带OUTPUT参数的参数的 存储过程，用于计算指定的商品的平均价格，存存储过程，用于计算指定的商品的平均价格，存 储过程中使用一个输入参数（商品名）和一个输储过程中使用一个输入参数（商品名）和一个输 出参数（平均价格）。出参数（平均价格）。 CREATE PROCEDURE Pname p_n varchar(20), aveage int OUTPUT AS SELECT aveage= avg(单价单价) FROM 商品

16、商品 WHERE 商品名称商品名称=p_n 存储过程、触发器和游标存储过程、触发器和游标 1.4执行存储过程执行存储过程 EXECUTE命令。命令。 如果被调用的存储过程需要参数输入时，在存储如果被调用的存储过程需要参数输入时，在存储 过程名后逐一给定，每一个参数用逗号隔开，不过程名后逐一给定，每一个参数用逗号隔开，不 必使用括号。必使用括号。 如果没有使用如果没有使用参数名参数名=default这种方式传入值，则参这种方式传入值，则参 数的排列必须和建立存储过程所定义的次序对应。数的排列必须和建立存储过程所定义的次序对应。 用来接受输出值的参数则必须加上用来接受输出值的参数则必须加上OUTP

17、UT。 存储过程、触发器和游标存储过程、触发器和游标 1.4执行存储过程执行存储过程 EXECUTE语句的语法格式为：语句的语法格式为： EXECUTEUTE return_status= procedure_name;number parameter=value | parameter= variable OUTPUT WITH RECOMPILE 例例11-5执行存储过程执行存储过程student_avg。 EXECUTE student_avg 存储过程、触发器和游标存储过程、触发器和游标 1.4执行存储过程执行存储过程 例例11-6执行带参数的存储过程执行带参数的存储过程GetStud

18、ent，查询学号为，查询学号为01010001 的学生的基本信息。的学生的基本信息。 EXECUTE GetStudent 01010001 例例11-7执行带有参数和默认值（通配符）的存储过程执行带有参数和默认值（通配符）的存储过程 Student_Name。 (1)显示所有学生的信息：显示所有学生的信息： EXECUTE Student_Name (2)显示显示“王小明王小明”所有学生的信息：所有学生的信息： EXECUTE Student_Name 王小明王小明 例例11-8执行带有输入和输出参数的存储过程执行带有输入和输出参数的存储过程Pname。 Declare avgage int

19、 EXECUTE Pname 冰箱冰箱,avgage OUTPUT Print 冰箱的平均价格：冰箱的平均价格：+str(avgage) .NET中调用存储过程 功能：返回销售额和插入的记录SN字段值 SqlConnection conn = new SqlConnection(); conn.ConnectionString = Data Source=.;Initial Catalog=CPXS;Integrated Security=True; if (conn.State = ConnectionState.Closed) conn.Open(); SqlCommand cmd = n

20、ew SqlCommand(); cmd.Connection = conn; cmd.CommandText = 销售InsertOne; cmd.CommandType = CommandType.StoredProcedure; / cmd.CommandText = INSERT INTO CPXS.dbo.销售(产品编号,客户编号,销售日期,数量,销售额) + /VALUES(产品编号,客户编号,销售日期,数量,销售额); / cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue(产品编号,DropDownLi

21、st1.SelectedValue); cmd.Parameters.AddWithValue(客户编号,DropDownList2.SelectedValue); cmd.Parameters.AddWithValue(销售日期,Convert.ToDateTime(TextBox1.Text.Trim(); cmd.Parameters.AddWithValue(数量,Convert.ToInt32(TextBox2.Text.Trim(); cmd.Parameters.AddWithValue(销售额, 0); cmd.Parameters销售额.Direction=Parameter

22、Direction.InputOutput; /SqlParameter ret = cmd.Parameters.Add(ret, SqlDbType.Int); /ret.Direction = ParameterDirection.ReturnValue; cmd.Parameters.AddWithValue(ret, 0);/返回值类型参数的名称可以是存储过程参数中定义的参数 cmd.Parametersret.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); int retvalue = Conve

23、rt.ToInt32(cmd.Parametersret.Value); decimal xse = Convert.ToDecimal(cmd.Parameters销售额.Value); GridView1.DataBind(); CREATE PROCEDURE 销售InsertOne 产品编号CHAR(6) ,客户编号CHAR(6) ,销售日期DATETIME = NULL ,数量 INT = NULL ,销售额DECIMAL(18,0) = NULL OUTPUT AS SET NOCOUNT ON SELECT 销售额=数量*价格 FROM dbo.产品 WHERE 产品编号=产品编

24、号 INSERT 销售(产品编号, 客户编号, 销售日期, 数量 , 销售额) VALUES(产品编号, 客户编号, 销售日期, 数量 , 销售额) RETURN IDENTITY SET NOCOUNT OFFGO 存储过程、触发器和游标存储过程、触发器和游标 1.5查看、修改和删除存储过程查看、修改和删除存储过程 1查看存储过程查看存储过程 可以执行系统存储过程可以执行系统存储过程sp_helptext，用于查看创，用于查看创 建存储过程的命令语句；也可以执行系统存储过建存储过程的命令语句；也可以执行系统存储过 程程sp_help，用于查看存储过程的名称、拥有者、，用于查看存储过程的名称、

25、拥有者、 类型、创建时间，以及存储过程中所使用的参数类型、创建时间，以及存储过程中所使用的参数 信息。其语法格式分别为：信息。其语法格式分别为： sp_helptext 存储过程名称存储过程名称 sp_help 存储过程名称存储过程名称 存储过程、触发器和游标存储过程、触发器和游标 1.5查看、修改和删除存储过程查看、修改和删除存储过程 1查看存储过程查看存储过程 例例11-9查看存储过程查看存储过程Pname的相关信息。的相关信息。 (1) sp_helptext Pname 执行结果如图所示。执行结果如图所示。 (2) sp_help Pname 执行结果如图所示。执行结果如图所示。 存储

26、过程、触发器和游标存储过程、触发器和游标 1.5查看、修改和删除存储过程查看、修改和删除存储过程 2修改存储过程修改存储过程 修改存储过程可以在修改存储过程可以在SQL Server Management Studio中中 鼠标右击要修改的存储过程，选择鼠标右击要修改的存储过程，选择“修改修改”命令进行，与命令进行，与 创建时的步骤基本相同；也可以通过创建时的步骤基本相同；也可以通过T-SQL中的中的ALTER语语 句来完成。语法格式如下：句来完成。语法格式如下： ALTER PROC | PROCEDURE procedure_name ; number parameter data_typ

27、e VARYING = default OUT PUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ,.n FOR REPLICATION AS sql_statement .n 存储过程、触发器和游标存储过程、触发器和游标 1.5查看、修改和删除存储过程查看、修改和删除存储过程 2修改存储过程修改存储过程 例例11-10修改存储过程修改存储过程Pname，除了用于计算指定的商，除了用于计算指定的商 品的平均价格外，还用于计算此类商品的库存总数量，存品的平均价格外，还用于计算此类商品的库存总数量，存 储过程中使用一个输入参数

28、（商品名）和两个输出参数储过程中使用一个输入参数（商品名）和两个输出参数 （平均价格和总数量）。（平均价格和总数量）。 ALTER PROCEDURE Pname p_n varchar(20), aveage int OUTPUT,sum int OUTPUT AS SELECT aveage= avg(单价单价),sum=sum(数量数量) FROM 商品商品as s,库库 存情况存情况as k WHERE 商品名称商品名称=p_n and s.商品编号商品编号=k.商品编号商品编号 存储过程、触发器和游标存储过程、触发器和游标 1.5查看、修改和删除存储过程查看、修改和删除存储过程 3删

29、除存储过程删除存储过程 对于不需要的存储过程可以对于不需要的存储过程可以SQL Server Management Studio中鼠标右击要删除的存储过程，选择中鼠标右击要删除的存储过程，选择“删除删除”命令命令 将其删除，也可以使用将其删除，也可以使用T-SQL语句中的语句中的DROP PROCEDURE命令将其删除。命令将其删除。 删除存储过程的删除存储过程的T-SQL语句的语法格式为：语句的语法格式为： DROP PROCEDURE procedure_name ,n procedure_name指要删除的存储过程或存储过程组的名指要删除的存储过程或存储过程组的名 称。称。 例例11-1

30、2删除存储过程删除存储过程Pname DROP PROCEDURE Pname 存储过程存储过程VS函数函数 相同点相同点 数据库对象数据库对象 完成特定功能，可以有输入参数和返回值完成特定功能，可以有输入参数和返回值 返回的结果集可以插入表中返回的结果集可以插入表中 insert #t1 select * from 函数名称函数名称 (实参实参) insert #t1 EXEC 存储过程名称存储过程名称 实参实参 区别区别 存储过程可以直接独立执行，函数必须嵌在存储过程可以直接独立执行，函数必须嵌在SQL语句中语句中 标量函数可以用于表达式中，内嵌表值函数与多语句表标量函数可以用于表达式中，

31、内嵌表值函数与多语句表 值函数可以数据表形式用于值函数可以数据表形式用于FROM后面。后面。 存储过程不能。存储过程不能。 存储过程、触发器和游标存储过程、触发器和游标 2触发器触发器 2.1触发器概述触发器概述 存储过程和触发器都是存储过程和触发器都是SQL语句和流程控语句和流程控 制语句的集合。制语句的集合。 特殊存储过程，它是一种在基本表被修改特殊存储过程，它是一种在基本表被修改 时自动执行的内嵌过程，主要通过事件进时自动执行的内嵌过程，主要通过事件进 行触发而被执行。行触发而被执行。 UPDATE INSERT DELETE 存储过程、触发器和游标存储过程、触发器和游标 2.1触发器概

32、述触发器概述 触发器的优点：触发器的优点： 由于在触发器中可以包含复杂的处理逻辑，应该将触发由于在触发器中可以包含复杂的处理逻辑，应该将触发 器用来器用来保持低级的数据的完整性保持低级的数据的完整性，而不是返回大量的查，而不是返回大量的查 询结果。询结果。 使用触发器主要可以实现以下操作：使用触发器主要可以实现以下操作： (1)强制比强制比CHECK约束更复杂的数据的完整性。约束更复杂的数据的完整性。 (2)使用自定义的错误提示信息使用自定义的错误提示信息 (3)实现数据库中多张表的级联修改实现数据库中多张表的级联修改 (4)比较数据库修改前后数据的状态比较数据库修改前后数据的状态 (5)调用

33、更多的存储过程调用更多的存储过程 (6)维护规范化数据维护规范化数据 存储过程、触发器和游标存储过程、触发器和游标 2.2触发器的分类触发器的分类 1DML触发器触发器 当数据库服务器中发生数据操作语言当数据库服务器中发生数据操作语言(DML)事件时会自事件时会自 动执行的存储过程。动执行的存储过程。 DML事件事件 INSERT语句语句 UPDATE语句语句 DELETE语句语句 系统将触发器和触发它的语句作为可在触发器内系统将触发器和触发它的语句作为可在触发器内 回滚的回滚的单个事务单个事务对待，如果检测到错误（例如，对待，如果检测到错误（例如， 磁盘空间不足），则整个事务即自动回滚。磁盘

34、空间不足），则整个事务即自动回滚。 存储过程、触发器和游标存储过程、触发器和游标 触发器触发时触发器触发时： 系统自动在内存中创建系统自动在内存中创建deleted表或表或inserted表表 只读，不允许修改只读，不允许修改；触发器执行完成后，自动删除触发器执行完成后，自动删除 inserted 表表 临时保存了插入或更新后的记录行临时保存了插入或更新后的记录行 可以从可以从inserted表中检查插入的数据是否满足业务需求表中检查插入的数据是否满足业务需求 如如 果不满足，则向用户报告错误消息，并回滚插入操作果不满足，则向用户报告错误消息，并回滚插入操作 deleted 表表 临时保存了删

35、除或更新前的记录行临时保存了删除或更新前的记录行 可以从可以从deleted表中检查被删除的数据是否满足业务需求表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作如果不满足，则向用户报告错误消息，并回滚插入操作 存储过程、触发器和游标存储过程、触发器和游标 修改操作修改操作inserted表表deleted表表 增加增加(INSERT)记录记录存放新增的记录存放新增的记录- 删除删除(DELETE)记录记录-存放被删除的记录存放被删除的记录 修改修改(UPDATE)记录记录存放更新后的记录存放更新后的记录存放更新前的记录存放更新前的记录 inserted表和

36、表和deleted表存放的信息表存放的信息 存储过程、触发器和游标存储过程、触发器和游标 2.2触发器的分类触发器的分类 1DML触发器触发器 SQL Server 2005的的DML触发器分为两类：触发器分为两类： (1) AFTER触发器：这类触发器是在记录已经改变完之后，触发器：这类触发器是在记录已经改变完之后， 才会被激活执行，它主要是用于记录变更后的处理或检查，才会被激活执行，它主要是用于记录变更后的处理或检查， 一旦发现错误，也可以用一旦发现错误，也可以用ROLLBACK TRANSACTION语语 句来回滚本次的操作。句来回滚本次的操作。 (2) INSTEAD OF触发器：与触

37、发器：与AFTER触发器不同，这类触触发器不同，这类触 发器一般是用来取代原本的操作，在记录变更之前发生的，发器一般是用来取代原本的操作，在记录变更之前发生的， 它并不去执行原来它并不去执行原来SQL语句里的操作（语句里的操作（UPDATE、 INSERT、DELETE），而去执行触发器本身所定义的操），而去执行触发器本身所定义的操 作。作。 存储过程、触发器和游标存储过程、触发器和游标 2.2触发器的分类触发器的分类 2DDL 触发器触发器 SQL Server 2005新增的一个触发器类型，是一种特殊的新增的一个触发器类型，是一种特殊的 触发器，它在响应数据定义语言（触发器，它在响应数据定

38、义语言（DDL）语句时触发，一）语句时触发，一 般用于数据库中执行管理任务。般用于数据库中执行管理任务。 添加，删除或修改数据库的对象，一旦误操作，可能会导添加，删除或修改数据库的对象，一旦误操作，可能会导 致大麻烦，需要一个数据库管理员或开发人员对相关可能致大麻烦，需要一个数据库管理员或开发人员对相关可能 受影响的实体进行代码的重写。为了在数据库结构发生变受影响的实体进行代码的重写。为了在数据库结构发生变 动而出现问题时，能够跟踪问题和定位问题的根源，我们动而出现问题时，能够跟踪问题和定位问题的根源，我们 可以利用可以利用DDL触发器来记录类似触发器来记录类似“用户建立表用户建立表”这种变化

39、这种变化 的操作，这样可以大大减轻跟踪和定位数据库模式的变化的操作，这样可以大大减轻跟踪和定位数据库模式的变化 的繁琐程度。（的繁琐程度。（系统操作日志系统操作日志） 存储过程、触发器和游标存储过程、触发器和游标 2.2触发器的分类触发器的分类 2DDL 触发器触发器 一般来说，在以下几种情况下可以使用一般来说，在以下几种情况下可以使用DDL触发器：触发器： (1)数据库里的库架构或数据表架构很重要（如系统配置信数据库里的库架构或数据表架构很重要（如系统配置信 息）息） ，不允许被修改。，不允许被修改。 (2)防止数据库或数据表被误操作删除。防止数据库或数据表被误操作删除。 (3)在修改某个数

40、据表结构的同时修改另一个数据表的相应在修改某个数据表结构的同时修改另一个数据表的相应 的结构。的结构。 (4)要记录对数据库结构操作的事件。要记录对数据库结构操作的事件。 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 在创建触发器前，需要注意以下一些问题。在创建触发器前，需要注意以下一些问题。 (1)CREATE TRIGGER语句必须是批处理中的第一个语句，而且只能用语句必须是批处理中的第一个语句，而且只能用 于一个表或视图。于一个表或视图。 (2)创建触发器的权限默认为创建触发器的权限默认为表的所有者表的所有者，不能将该权限转给其他用户。，不能将该权限转给其他用

41、户。 (3)虽然触发器可以引用当前数据库以外的对象，但虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中只能在当前数据库中 创建触发器创建触发器。 (4)虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时 表。不应引用系统表，而应使用信息架构视图。表。不应引用系统表，而应使用信息架构视图。 (5)在含有用在含有用DELETE或或UPDATE操作定义的外键的表中，不能定义操作定义的外键的表中，不能定义 INSTEAD OF和和INSTEAD OF UPDATE触发器。触发器。 (6)虽然虽然TRUNCATE TABL

42、E语句语句类似于没有类似于没有WHERE子句子句(用于删除行用于删除行)的的 DELETE语句，但语句，但不会激发不会激发DELETE触发器触发器，因为，因为TRUNCATE TABLE语句语句 没有记录日志。没有记录日志。 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 1在在SQL Server Management Studio中创建中创建DML触发触发 器器 步骤如下：步骤如下： (1)打开打开SQL Server Management Studio，展开要创建，展开要创建 DML触发器的数据库和其中的表或视图（如学生表），触发器的数据库和其中的表或视图（如学

43、生表）， 右键单击右键单击“触发器触发器”选项，选择选项，选择“新建触发器新建触发器”命令。命令。 (2)出现创建触发器的出现创建触发器的T-SQL命令，编辑相关的命令即可。命令，编辑相关的命令即可。 (3)命令编辑成功后，进行语法检查，然后单击命令编辑成功后，进行语法检查，然后单击“确定确定” 按钮，至此一个按钮，至此一个DML触发器建立成功。触发器建立成功。 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 2利用利用T-SQL语句创建触发器语句创建触发器 SQL Server 2005提供了提供了CREATE TRIGGER 创建触发器。其语法格式如下：创建触发器

44、。其语法格式如下： CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT DELETE UPDATE NOT FOR REPLICATION AS sql_statement .n 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-13使用使用DDL触发器触发器limited来防止数据库来防止数据库 中的任一表被修改或删除。中的任一表被修改或删除。 CREATE TRIGGER limited ON database FO

45、R DROP_TABLE, ALTER_TABLE AS PRINT 名为名为limited的触发器不允许您执行对的触发器不允许您执行对 表的修改或删除操作表的修改或删除操作! ROLLBACK 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-15为学生表创建一个简单为学生表创建一个简单DML触触 发器，在插入和修改数据时，都会自动显发器，在插入和修改数据时，都会自动显 示提示信息。示提示信息。 CREATE TRIGGER reminder ON 学生学生 FOR INSERT , UPDATE AS print 你在插入或修改学生表的数据你在插入或修改学

46、生表的数据 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-17为学生表创建一个为学生表创建一个DML触发器，在插触发器，在插 入和修改数据时，都会自动显示所有学生的信息。入和修改数据时，都会自动显示所有学生的信息。 CREATE TRIGGER print_table ON 学生学生 FOR INSERT, UPDATE AS select * from 学生学生 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-19在学生表上创建一个在学生表上创建一个DELETE类型的触类型的触 发器，删除数据时，显示删除学生的个数。发

47、器，删除数据时，显示删除学生的个数。 CREATE TRIGGER del_count ON 学生学生 FOR DELETE AS DECLARE count varchar(50) SELECT count=STR(ROWCOUNT)+个学个学 生被删除生被删除 SELECT count RETURN 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 在在SQL Server 2005里，为每个里，为每个DML触发触发 器都定义了两个特殊的表：器都定义了两个特殊的表：Inserted， Deleted。 用户只有读取的权限，没有修改的权限。用户只有读取的权限，没有修改

48、的权限。 SQL Server建立和管理这两个临时表。建立和管理这两个临时表。 这两个表的结构与触发器所在这两个表的结构与触发器所在数据表的结构数据表的结构是是 完全一致的，其中包含了在激发触发器的操作完全一致的，其中包含了在激发触发器的操作 中插入或删除的所有记录。中插入或删除的所有记录。 当触发器的工作完成之后，这两个表也将会从当触发器的工作完成之后，这两个表也将会从 内存中删除。内存中删除。 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 在用户执行在用户执行INSERT语句时，所有被添加的记语句时，所有被添加的记 录都会存储在录都会存储在Inserted表中；

49、表中； 在用户执行在用户执行DELETE语句时，从触发程序表中语句时，从触发程序表中 被删除的行会发送到被删除的行会发送到Deleted表；表； 对于对于UPDATE语句，语句，SQL Server先将要进行先将要进行 修改的记录存储到修改的记录存储到Deleted表中，然后再将修表中，然后再将修 改后的数据复制到改后的数据复制到Inserted表以及触发程序表。表以及触发程序表。 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 先创建一个先创建一个dept数据库（属性默认），和两个表数据库（属性默认），和两个表 dept,gongcheng，其结构如下。，其结构如下

50、。 Create table dept ( dno char(5) primary key, dname varchar(20), leader varchar(10) ) CREATE table gongcheng ( gno char(5) primary key, gname varchar(20), Leader varchar(10) ) 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-21为为dept表创建一个名为表创建一个名为d_tr的触发器，的触发器， 当执行添加、更新或删除时，激活该触发器。通当执行添加、更新或删除时，激活该触发器。通 过

51、此例，了解过此例，了解Inserted表和表和Deleted表的功能。表的功能。 CREATE TRIGGER d_tr ON dept FOR INSERT, UPDATE, DELETE AS SELECT * FROM inserted SELECT * FROM deleted 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-23为为gongcheng表创建一个名为表创建一个名为g_tr的触发器，的触发器， 实现参照完整性。实现参照完整性。 CREATE TRIGGER g_tr ON gongcheng FOR insert AS if not e

52、xists (select * from dept where leader=(SELECT leader FROM inserted) begin declare lead varchar(10) set lead=(SELECT leader FROM inserted) print 你在你在gongcheng表中要插入的记录，表中要插入的记录， 在在dept表中不存在这样的表中不存在这样的leader:+lead rollback end 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-25为为dept表创建一个名为表创建一个名为d_tr1的实现级联更

53、新的实现级联更新 的的update触发器，当执行更新触发器，当执行更新(leader列列)时，激活该触发时，激活该触发 器同时更新器同时更新gongcheng表中相应记录。表中相应记录。 CREATE TRIGGER d_tr1 ON dept FOR UPDATE AS update gongcheng set leader=(SELECT leader FROM inserted) where leader=(SELECT leader FROM deleted) 存储过程、触发器和游标存储过程、触发器和游标 2.3创建触发器创建触发器 例例11-27 触发器中调用存储过程。触发器中调用存

54、储过程。 首先创建一个存储过程首先创建一个存储过程p1如下：如下： CREATE PROC p1 AS Select * from 学生学生 然后，为学生表创建一个触发器然后，为学生表创建一个触发器tr1，在插入、修改或删，在插入、修改或删 除数据时，都会调用存储过程除数据时，都会调用存储过程p1。 CREATE TRIGGER tr1 on 学生学生 for insert, update, delete AS EXEC p1 存储过程、触发器和游标存储过程、触发器和游标 2.4查看触发器信息及修改触查看触发器信息及修改触 发器发器 1在在SQL Server Management Studi

55、o 中查看触发器中查看触发器 存储过程、触发器和游标存储过程、触发器和游标 2.4查看触发器信息及修改触发器查看触发器信息及修改触发器 2使用系统存储过程查看触发器使用系统存储过程查看触发器 系统存储过程系统存储过程sp_help和和sp_helptext分别提供有关分别提供有关 触发器的不同信息。触发器的不同信息。 (1)通过通过sp_help系统存储过程，可以了解触发器的系统存储过程，可以了解触发器的 一般信息，包括名字、拥有者名称、类型、创建时一般信息，包括名字、拥有者名称、类型、创建时 间。间。 (2)通过通过sp_helptext能够查看触发器的定义信息。能够查看触发器的定义信息。

56、还可以通过使用系统存储过程还可以通过使用系统存储过程sp_helptrigger来查来查 看某张特定表上存在的触发器的某些相关信息。看某张特定表上存在的触发器的某些相关信息。 存储过程、触发器和游标存储过程、触发器和游标 2.4查看触发器信息及修改触发器查看触发器信息及修改触发器 例例11-31修改教学库中的学生表上的触发器修改教学库中的学生表上的触发器 reminder，使得在用户执行添加或修改操作时，使得在用户执行添加或修改操作时， 自动给出错误提示信息，撤销此次操作。自动给出错误提示信息，撤销此次操作。 ALTER TRIGGER reminder ON 学生学生 INSTEAD OF

57、INSERT , UPDATE AS print 你执行的添加或修改操作无效！你执行的添加或修改操作无效！ 存储过程、触发器和游标存储过程、触发器和游标 2.5禁止、启用和删除触发器禁止、启用和删除触发器 禁用触发器与删除触发器不同禁用触发器与删除触发器不同 1禁止和启用触发器禁止和启用触发器 在使用触发器时，用户可能会遇到需要禁止某在使用触发器时，用户可能会遇到需要禁止某 个触发器起作用的场合，例如，在某些表上不个触发器起作用的场合，例如，在某些表上不 允许批量更新操作，使用的是用触发器，里面允许批量更新操作，使用的是用触发器，里面 根据根据ROWCOUNT来进行判断，如果来进行判断，如果

58、ROWCOUNT大于预设的值就不允许更新，大于预设的值就不允许更新， 但是作为数据库管理员难免有批量更新的要求，但是作为数据库管理员难免有批量更新的要求， 此时就需要让触发器不起作用，即禁止。此时就需要让触发器不起作用，即禁止。 在系统刚上线时，需要在系统刚上线时，需要大量加载历史数据大量加载历史数据，应该禁用，应该禁用 触发器。触发器。 存储过程、触发器和游标存储过程、触发器和游标 2.5禁止、启用和删除触发器禁止、启用和删除触发器 当一个触发器被禁止，该触发器仍然存在于表上，只当一个触发器被禁止，该触发器仍然存在于表上，只 是触发器的动作将不再执行，直到该触发器被重新启是触发器的动作将不再

59、执行，直到该触发器被重新启 用。用。ALTER TABLE可以禁止和启用一个表上的一个可以禁止和启用一个表上的一个 或者全部的触发器，禁止和启用触发器的语法格式如或者全部的触发器，禁止和启用触发器的语法格式如 下：下： ALTER TABLE table_name ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n 例例11-32禁止学生表上创建的所有触发器。禁止学生表上创建的所有触发器。 ALTER TABLE 学生学生 DISABLE TRIGGER ALL 存储过程、触发器和游标存储过程、触发器和游标 2.5禁止、启用和删除触发器禁止、启用和删

60、除触发器 2删除触发器删除触发器 DROP TRIGGER trigger_ name 例例11-33使用使用DROP TRIGGER命令删命令删 除学生表上的除学生表上的del_count触发器。触发器。 DROP TRIGGER del_count 存储过程、触发器和游标存储过程、触发器和游标 3游标（游标（Cursor） 适用情况：从某一结果集中逐一地读取一适用情况：从某一结果集中逐一地读取一 条记录，逐一进行处理。条记录，逐一进行处理。 比如：自动对所有数据库进行备份。比如：自动对所有数据库进行备份。 学习目标：学习目标： 了解游标的概念及基本用法、适用场合。了解游标的概念及基本用法、