大型数据库_第4章存储过程与触发器

1、2022年6月4日第1页第第4章章 存储过程和触发器存储过程和触发器本章概述 本章要点本章内容2022年6月4日第2页本章概述本章概述l如何提高如何提高Transact-SQL语句的执行效率呢？如何语句的执行效率呢？如何加强数据库中数据完整性的机制呢？这些问题的加强数据库中数据完整性的机制呢？这些问题的解决都依赖于数据库的编程对象。典型的数据库解决都依赖于数据库的编程对象。典型的数据库编程对象包括视图、存储过程、触发器、函数等。编程对象包括视图、存储过程、触发器、函数等。存储过程是一个可重用的代码模块，可以高效率存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的

2、存地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。储过程，可以实现自动化的操作。l本章将全面研究存储过程、触发器特点和使用方本章将全面研究存储过程、触发器特点和使用方式。式。2022年6月4日第3页本章要点本章要点l存储过程的特点、类型和作用存储过程的特点、类型和作用l使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程l存储过程的执行方式存储过程的执行方式lDML触发器的特点和创建方式触发器的特点和创建方式lDML触发器的工作原理触发器的工作原理l使用使用CREATE TRIGGER语句创建语句创建DML触发器触发器lDDL触发器的特点和创建方式触

3、发器的特点和创建方式2022年6月4日第4页本章内容本章内容4.1 存储过程存储过程4.2 触发器触发器4.3 本章小结本章小结4.1 存储过程存储过程l存储过程可以提高应用程序的设计效率和存储过程可以提高应用程序的设计效率和增强系统的安全性。本节将全面介绍存储增强系统的安全性。本节将全面介绍存储过程的特点、类型、创建、执行等内容。过程的特点、类型、创建、执行等内容。2022年6月4日第5页类型类型l在在Microsoft SQL Server 2008系统中，提系统中，提供了供了3种基本的存储过程类型，即用户定义种基本的存储过程类型，即用户定义的存储过程、扩展存储过程和系统存储过的存储过程、

4、扩展存储过程和系统存储过程。程。2022年6月4日第6页系统存储过程系统存储过程系统存储过程是方便查询系统信息或完成系统管理系统存储过程是方便查询系统信息或完成系统管理任务，一般以任务，一般以sp_开头，并存放在开头，并存放在sys架构中。架构中。常用的系统存储过程有：常用的系统存储过程有：1Sp_help报告有关数据库对象（报告有关数据库对象（sys.sysobjects 兼容视图中兼容视图中列出的所有对象）、用户定义数据类型或某种数列出的所有对象）、用户定义数据类型或某种数据类型的信息。据类型的信息。语法：语法： sp_help objname = name objname = name：

5、某个对象的名称。：某个对象的名称。2. sp_helpdb报告有关指定数据库或所有数据库的信息。报告有关指定数据库或所有数据库的信息。语法：语法：sp_helpdb dbname= name dbname = name：要报告其信息的数：要报告其信息的数据库的名称。如果未指定据库的名称。如果未指定 name，则，则sp_helpdb将报告将报告sys.databases目录视图目录视图中所有数据库的信息。中所有数据库的信息。3. sp_helpfile返回与当前数据库关联的文件的物理名称及属性。返回与当前数据库关联的文件的物理名称及属性。使用此存储过程确定附加到服务器或从服务器分使用此存储过程

6、确定附加到服务器或从服务器分离的文件名。离的文件名。语法：语法： sp_helpfile filename = name filename = name是当前数据库中任意文件的是当前数据库中任意文件的逻辑名称。如果未指定逻辑名称。如果未指定 name，则返回当前数据，则返回当前数据库中所有文件的属性。库中所有文件的属性。4. sp_helpfilegroup返回与当前数据库相关联的文件组的名称及属性。返回与当前数据库相关联的文件组的名称及属性。语法：语法： sp_helpfilegroup filegroupname = name filegroupname = name：当前数据库中任：当前

7、数据库中任意文件组的逻辑名称。如果没有指定意文件组的逻辑名称。如果没有指定 name，则，则列出当前数据库中的所有文件组并只显示在结果列出当前数据库中的所有文件组并只显示在结果集部分中显示的第一个结果集。集部分中显示的第一个结果集。5. sp_helpindex报告有关表或视图上索引的信息。报告有关表或视图上索引的信息。语法：语法：sp_helpindex objname = name objname = name：用户定义的表或视图的名：用户定义的表或视图的名称。称。6. sp_helpsort显示显示 SQL Server 2008 实例的排序顺序和字符集。实例的排序顺序和字符集。7. s

8、p_helptext显示用户定义规则的定义、默认值、未加密的显示用户定义规则的定义、默认值、未加密的 Transact-SQL 存储过程存储过程、用户定义、用户定义 Transact-SQL 函数、触发器、计算列、函数、触发器、计算列、CHECK 约束、约束、视图或系统对象（如系统存储过程）。视图或系统对象（如系统存储过程）。语法：语法： sp_helptext objname = name , columnname = computed_column_name 参数：参数：objname=name：架构范围内的用户定义对象的限定名称和非限定：架构范围内的用户定义对象的限定名称和非限定名称。仅

9、当指定限定对象时才需要引号。如果提供的是完全限定名称名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。必须在当前数据库中。name 的数据类型为的数据类型为 nvarchar(776)，无默认，无默认值。值。 columnname = computed_column_name：要显示其定义信息：要显示其定义信息的计算列的名称。的计算列的名称。例例1：查看函数：查看函数multisql_tablevalued_func信息。信息。sp_helpte

10、xt multisql_tablevalued_func例例2：查看表：查看表grade_zcj的计算列的计算列“总成绩总成绩”信息。信息。sp_helptext grade_zcj,总成绩总成绩8. sp_helptrigger返回对当前数据库的指定表定义的返回对当前数据库的指定表定义的 DML 触发器的类型触发器的类型.sp_helptrigger不能用于不能用于 DDL 触发器。触发器。语法语法: sp_helptrigger tabname = table , triggertype = type 参数参数: tabname = table:当前数据库中将为其返回触发器信当前数据库中将

11、为其返回触发器信息的表的名称。息的表的名称。 triggertype = type:将为其返回有关信息的将为其返回有关信息的DML触发触发器的类型。可以是器的类型。可以是DELETE、INSERT、UPDATE。9. sp_monitor：显示有关：显示有关 Microsoft SQL Server 的统计信息。的统计信息。10. sp_rename在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或 Microsoft .NET Framework 公共语言运行时公共语言运行时 (CLR)

12、 用户定义类型。用户定义类型。注意：注意： 更改对象名的任一部分都可能破坏脚本和存储过程。建议删除该对象，然后使用新名称重更改对象名的任一部分都可能破坏脚本和存储过程。建议删除该对象，然后使用新名称重新创建该对象。新创建该对象。语法：语法：sp_rename objname = object_name , newname = new_name , objtype = object_type 参数：参数： objname = object_name：用户对象或数据类型的名称。如果要重命名的对象是表：用户对象或数据类型的名称。如果要重命名的对象是表中的列，则中的列，则object_name的格式必

13、须是的格式必须是table.column。如果要重命名的对象是索引，。如果要重命名的对象是索引，则则object_name的格式必须是的格式必须是table.index。 newname = new_name：指定对象的新名称。：指定对象的新名称。objtype =object_type：要重命名的对象的类型。：要重命名的对象的类型。COLUMN表示表示 要重命名的列，要重命名的列，DATABASE表示要重命名用户定义数据库，表示要重命名用户定义数据库，INDEX表示重命名用户定义索引，表示重命名用户定义索引，OBJECT可用于重命名约束（可用于重命名约束（CHECK、FOREIGN KEY、

14、PRIMARY/UNIQUE KEY）、用户表和规则等对象，、用户表和规则等对象，USERDATATYPE表示重命名通过执行表示重命名通过执行CREATE TYPE 或或 sp_addtype 添加别名数据类型或添加别名数据类型或 CLR 用户定义类型。用户定义类型。11. sp_who提供有关提供有关 Microsoft SQL Server 数据库引擎实例中的当前用户、会话数据库引擎实例中的当前用户、会话和进程的信息。可以筛选信息以便只返回那些属于特定用户或特定会和进程的信息。可以筛选信息以便只返回那些属于特定用户或特定会话的非空闲进程。话的非空闲进程。语法：语法： sp_who logi

15、name = login | session ID | ACTIVE 参数参数 loginame = login | session ID | ACTIVE用于筛选结果集。用于筛选结果集。login用于标识属于特定登录名的进程。用于标识属于特定登录名的进程。session ID是属于是属于 SQL Server 实例的会话标识号。实例的会话标识号。ACTIVE 排除正在等待用户发出下一个命令的会话。排除正在等待用户发出下一个命令的会话。如果没有提供任何值，则过程报告属于实例的所有会话。如果没有提供任何值，则过程报告属于实例的所有会话。12. sp_columns返回当前环境中可查询的指定表或视

16、图的列信息。返回当前环境中可查询的指定表或视图的列信息。语法：语法：sp_columns table_name=object ,table_owner=owner , table_qualifier = qualifier , column_name = column , ODBCVer = ODBCVer 参数：参数： table_name = object：用于返回目录信息的表或视图的名称。支持通配符模式匹配。：用于返回目录信息的表或视图的名称。支持通配符模式匹配。 table_owner = owner：用于返回目录信息的表或视图的对象所有者。支持通配符模式匹配。如果：用于返回目录信息的

17、表或视图的对象所有者。支持通配符模式匹配。如果未指定未指定 owner，则是默认表或视图可见性规则。，则是默认表或视图可见性规则。如果当前用户拥有的表或视图具有指定名称，则返回该表的列。如果未指定如果当前用户拥有的表或视图具有指定名称，则返回该表的列。如果未指定 owner，并且当前用户不，并且当前用户不拥有具有指定拥有具有指定 object 的表或视图，则的表或视图，则 sp_columns 将搜索数据库所有者所拥有的具有指定将搜索数据库所有者所拥有的具有指定 object 的表或视图。如果有，则返回该表的列。的表或视图。如果有，则返回该表的列。 table_qualifier = qual

18、ifier：表或视图限定符的名称。在：表或视图限定符的名称。在 SQL Server 中，此列表示数据库名称。中，此列表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。在某些产品中，该列表示表所在数据库环境的服务器名。 column_name = column：一个单独的列，当只需要目录信息的一列时可使用该参数。如果未指：一个单独的列，当只需要目录信息的一列时可使用该参数。如果未指定定column，则返回所有列。支持通配符模式匹配。，则返回所有列。支持通配符模式匹配。 ODBCVer = ODBCVer：当前使用的：当前使用的ODBC版本。版本。例：查询例：查询dbo架构中的架

19、构中的student表的列信息。表的列信息。sp_columns student,dbo,database_demo13. sp_databases列出驻留在列出驻留在 SQL Server 2005 数据库引擎实数据库引擎实例中的数据库或可以通过数据库网关访问例中的数据库或可以通过数据库网关访问的数据库。的数据库。14. sp_fkeys返回当前环境的逻辑外键信息。该过程显示各种外键关系，包括禁用的外键。返回当前环境的逻辑外键信息。该过程显示各种外键关系，包括禁用的外键。语法语法: sp_fkeys pktable_name = pktable_name , pktable_owner =

20、pktable_owner , pktable_qualifier = pktable_qualifier , fktable_name = fktable_name , fktable_owner = fktable_owner , fktable_qualifier = fktable_qualifier 参数参数: pktable_name = pktable_name:带主键的表的名称。不支持通配符模式匹配。必须提供该参数或带主键的表的名称。不支持通配符模式匹配。必须提供该参数或fktable_name参数，或二者都提供。参数，或二者都提供。 pktable_owner = pktab

21、le_owner：表（带主键）的所有者的名称。不支持通配符模式匹配。：表（带主键）的所有者的名称。不支持通配符模式匹配。在在 SQL Server 中，如果当前用户拥有具有指定名称的表，则返回该表的列。如果未指定中，如果当前用户拥有具有指定名称的表，则返回该表的列。如果未指定 pktable_owner，并且当前用，并且当前用户没有具有指定名称户没有具有指定名称 pktable_name 的表，则此过程将查找由数据库所有者拥有并具有指定名称的表，则此过程将查找由数据库所有者拥有并具有指定名称 pktable_name 的表。如果有，则返回该表的列。的表。如果有，则返回该表的列。 pktable

22、_qualifier = pktable_qualifier：表（带主键）限定符的名称。在：表（带主键）限定符的名称。在 SQL Server 中，限定符表示数据库名称中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。在某些产品中，该列表示表所在数据库环境的服务器名。 fktable_name = fktable_name：表（带外键）的名称。不支持通配符模式匹配。必须提供该参数或：表（带外键）的名称。不支持通配符模式匹配。必须提供该参数或pktable_name参数，或二者都提供。参数，或二者都提供。 fktable_owner = fktable_owner：用于

23、表（带外键）的所有者的名称。不支持通配符模式匹配。：用于表（带外键）的所有者的名称。不支持通配符模式匹配。 fktable_qualifier = fktable_qualifier：在：在 SQL Server 中，限定符表示数据库名称。在某些产品中，该列表示表中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。所在数据库环境的服务器名。例：查看引用例：查看引用student表的外建表信息。表的外建表信息。Sp_fkeys student15. sp_pkeys返回当前环境中单个表的主键信息。返回当前环境中单个表的主键信息。语法：语法： sp_pkeys table_

24、name = name , table_owner = owner , table_qualifier = qualifier 参数：参数： table_name = name：为其返回信息的表。不支持通配符模式匹配。：为其返回信息的表。不支持通配符模式匹配。 table_owner = owner：为指定的表指定所有者。：为指定的表指定所有者。在在 SQL Server 中，如果当前用户拥有一个具有指定名称的表，则返回该表的列中，如果当前用户拥有一个具有指定名称的表，则返回该表的列。如果未指定。如果未指定 owner，并且当前用户没有名称为指定，并且当前用户没有名称为指定 name 的表，则

25、此过程的表，则此过程将查找由数据库所有者拥有，并且名称为指定将查找由数据库所有者拥有，并且名称为指定 name 的表。如果有，则返回的表。如果有，则返回该表的列。该表的列。 table_qualifier = qualifier：表限定符。在：表限定符。在 SQL Server 中，此列表示数中，此列表示数据库名称。在某些产品中，它表示表所在数据库环境的服务器名称。据库名称。在某些产品中，它表示表所在数据库环境的服务器名称。例：查看例：查看student表的主键信息。表的主键信息。Sp_pkeys student16. sp_server_info返回返回 SQL Server、数据库网关或基

26、础数据源、数据库网关或基础数据源的属性名称和匹配值的列表。的属性名称和匹配值的列表。语法语法:sp_server_info attribute_id = attribute_id attribute_id = attribute_id:属性的整属性的整数数ID。17. sp_tables返回可在当前环境中查询的对象列表。也就是说，返回任何能够在返回可在当前环境中查询的对象列表。也就是说，返回任何能够在 FROM 子句子句中出现的对象（不包括同义词对象）。中出现的对象（不包括同义词对象）。语法语法:sp_tables table_name = name , table_owner = owner

27、 , table_qualifier = qualifier , table_type = type , fUsePattern = fUsePattern;参数参数: table_name = name：用来返回目录信息的表。支持通配符模式匹配。：用来返回目录信息的表。支持通配符模式匹配。 table_owner = owner：用于返回目录信息的表的所有者。：用于返回目录信息的表的所有者。o支持通配符支持通配符模式匹配。如果未指定所有者，则遵循基础模式匹配。如果未指定所有者，则遵循基础 DBMS 的默认表可见性规则。的默认表可见性规则。 table_qualifier = qualifie

28、r：在：在 SQL Server 中，此列表示数据库名称。中，此列表示数据库名称。 , table_type = type, type ：由逗号分隔的值列表，该列表提供有关：由逗号分隔的值列表，该列表提供有关所有指定的表类型的表的信息。所有指定的表类型的表的信息。例：查看所有例：查看所有dbo架构中的表信息。架构中的表信息。sp_tables %,dbo18.sp_stored_procedures返回当前环境中的存储过程列表。返回当前环境中的存储过程列表。语法：语法： sp_stored_procedures sp_name = name , sp_owner = schema , sp_q

29、ualifier = qualifier , fUsePattern = fUsePattern 参数：参数： sp_name = name：用于返回目录信息的过程名。支持通配符模式匹配。：用于返回目录信息的过程名。支持通配符模式匹配。 sp_owner = schema：该过程所属架构的名称。支持通配符模式匹配。：该过程所属架构的名称。支持通配符模式匹配。在在 SQL Server 中，如果当前架构包含具有指定名称的过程，则返回此过程。如果指定了中，如果当前架构包含具有指定名称的过程，则返回此过程。如果指定了非限定存储过程，则数据库引擎按以下顺序搜索此过程：当前数据库的非限定存储过程，则数据

30、库引擎按以下顺序搜索此过程：当前数据库的sys架构，调用架构，调用方的默认架构（在使用批或动态方的默认架构（在使用批或动态 SQL执行时）；或者，如果非限定的过程名称出现在执行时）；或者，如果非限定的过程名称出现在另一个过程定义的主体中，则接着搜索包含这一过程的架构，当前数据库中的另一个过程定义的主体中，则接着搜索包含这一过程的架构，当前数据库中的dbo架架构。构。 qualifier = qualifier：在：在SQL Server中，中，qualifier表示数据库名称。表示数据库名称。 fUsePattern = fUsePattern：确定是否将下划线：确定是否将下划线(_)、百分号

31、、百分号(%)或方括号或方括号( ) 解释为解释为通配符。通配符。0=禁用模式匹配。禁用模式匹配。1=启用模式匹配。启用模式匹配。存储过程的特点和类型存储过程的特点和类型l存储过程是一个可重用的代码模块，可以存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。在高效率地完成指定的操作。在Microsoft SQL Server 2008系统中，可以使用系统中，可以使用Transact-SQL语言编写存储过程，也可以语言编写存储过程，也可以使用使用CLR方式编写存储过程。使用方式编写存储过程。使用CLR编编写存储过程是写存储过程是Microsoft SQL Server 2008系统与系

32、统与.NET框架紧密集成的一种表现形式。框架紧密集成的一种表现形式。2022年6月4日第29页使用使用CREATE PROCEDURE语句语句l在在Microsoft SQL Server 2008系统中，可以使系统中，可以使用用CREATE PROCEDURE语句创建存储过程。语句创建存储过程。需要强调的是，必须具有需要强调的是，必须具有CREATE PROCEDURE权限才能创建存储过程，存储过程权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创是架构作用域中的对象，只能在本地数据库中创建存储过程。建存储过程。l在创建存储过程时，应该指定所有的输入参数、在创建存储过程

33、时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或执行数据库操作的编程语句、返回至调用过程或批处理表明成功或失败的状态值、捕捉和处理潜批处理表明成功或失败的状态值、捕捉和处理潜在错误的错误处理语句。在错误的错误处理语句。2022年6月4日第30页CREATE PROCEDURE语句语句lCREATE PROCEDURE语句的基本语法形语句的基本语法形式如下所示：式如下所示：lCREATE PROCEDURE procedure_namelparameter_name data_type, lWITH procedure_optionlASlsql_statement2022

34、年6月4日第31页创建带有创建带有OUTPUT类型参数的存储过程类型参数的存储过程2022年6月4日第32页例例1：创建存储过程，实现查询某系学生人数的功能，：创建存储过程，实现查询某系学生人数的功能，以以output参数返回。参数返回。Create procedure mp_count_xs num int output,dept char(20)AsBeginSelect num=count(*) from student where sdept=dept; End 执行：执行：Declare n intExec mp_count_xs n output,CSPrint n建立带默认值使用

35、通配符的存储过程建立带默认值使用通配符的存储过程例例2：建立带默认值使用通配符的存储过程，实现按姓氏查询学生信息：建立带默认值使用通配符的存储过程，实现按姓氏查询学生信息。Create procedure mp_name_xs name varchar(20)= %AsBeginSelect * from student where sname like name; End 执行：执行：Declare n intExec mp_name_xs 张张%Print n注意：注意：name的数据类型是可变长。的数据类型是可变长。修改和删除存储过程修改和删除存储过程l在在Microsoft SQL S

36、erver 2008系统中，可以使系统中，可以使用用ALTER PROCEDURE语句修改已经存在的存语句修改已经存在的存储过程。修改存储过程，不是删除和重建存储过储过程。修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。程，其目的是保持存储过程的权限不发生变化。但是，如果删除存储过程并且重新创建同名的存但是，如果删除存储过程并且重新创建同名的存储过程，那么与该存储过程对象相关的权限都需储过程，那么与该存储过程对象相关的权限都需要重新定义。要重新定义。2022年6月4日第34页嵌套调用存储过程嵌套调用存储过程例例3：创建调用：创建调用mp_name_xs的存储过程。的

37、存储过程。create procedure call_procedureasexec mp_name_xs 刘刘%不显示影响行数的通知不显示影响行数的通知SET NOCOUNT ON/OFF 关闭或打开影响关闭或打开影响行数的通知行数的通知存储过程的执行过程存储过程的执行过程l存储过程创建之后，在第一次执行时需要存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段经过语法分析阶段、解析阶段、编译阶段和执行阶段。和执行阶段。l执行时可以重新编译存储过程执行时可以重新编译存储过程2022年6月4日第37页查看存储过程的信息查看存储过程的信息l在在Microsoft SQL Se

38、rver 2008系统中，可以使用系统存系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。储过程和目录视图查看有关存储过程的信息。l使用使用sp_stored_procedures查看存储过程，使用查看存储过程，使用sp_helptext查看指定存储过程的源代码信息。查看指定存储过程的源代码信息。l注意：不能查看加密后的存储过程源代码。注意：不能查看加密后的存储过程源代码。l使用使用OBJECT_DEFINITION元数据函数查看存储过程：元数据函数查看存储过程：lSelect OBJECT_DEFINITION(OBJECT_ID(mp_prcudure)2022年6月4日第38

39、页加密存储过程加密存储过程加密之后的存储过程可以正常使用，但是无法查看加密之后的存储过程可以正常使用，但是无法查看该存储过程的内容，在该存储过程的内容，在SSMS中也不能修改存储中也不能修改存储过程的内容（只能通过过程的内容（只能通过SQL语句修改）。语句修改）。例例4：创建一个加密的存储过程。：创建一个加密的存储过程。Create proc mp_prcudure xh char(10)With encryptionAsSelect * from student where sno=xh4.2 触发器触发器lMicrosoft SQL Server 2008系统提供了两系统提供了两种强制业务

40、逻辑和数据完整性的机制，即种强制业务逻辑和数据完整性的机制，即约束技术和触发器技术。约束技术和触发器技术。2022年6月4日第40页触发器的概念和类型触发器的概念和类型l一般地认为，触发器是一种特殊类型的存一般地认为，触发器是一种特殊类型的存储过程，它包括了大量的储过程，它包括了大量的Transact-SQL语语句。但是触发器又与存储过程不同，存储句。但是触发器又与存储过程不同，存储过程可以由用户直接调用执行，而触发器过程可以由用户直接调用执行，而触发器不能被直接调用执行，它只能自动执行。不能被直接调用执行，它只能自动执行。l 按照触发事件的不同，可以把按照触发事件的不同，可以把Microso

41、ft SQL Server 2008系统提供的触发器分成系统提供的触发器分成两种类型，即两种类型，即DML触发器和触发器和DDL触发器。触发器。2022年6月4日第41页l当数据库中发生数据操纵语言当数据库中发生数据操纵语言(Data Manipulation Language，简称为，简称为DML)事事件时将调用件时将调用DML触发器。触发器。l当数据库中发生数据定义语言当数据库中发生数据定义语言(Data Definition Language，简称为，简称为DDL)事件事件时将调用时将调用DDL触发器。触发器。DML触发器触发器lDML事件包括在指定表或视图中修改数据的事件包括在指定表或

42、视图中修改数据的INSERT语句、语句、UPDATE语句或语句或DELETE语句。在语句。在DML触发器中，可以执触发器中，可以执行查询其他表的操作，也可以包含更加复杂的行查询其他表的操作，也可以包含更加复杂的Transact-SQL语句。语句。DML触发器将触发器本身和触发事件的语句触发器将触发器本身和触发事件的语句作为可以在触发器内回滚的单个事务对待。也就是说，在作为可以在触发器内回滚的单个事务对待。也就是说，在执行触发器操作过程中，如果检测到错误发生，则整个触执行触发器操作过程中，如果检测到错误发生，则整个触发事件语句和触发器操作的事务自动回滚。发事件语句和触发器操作的事务自动回滚。20

43、22年6月4日第43页DML触发器的类型触发器的类型按照触发器事件类型的不同，可以把按照触发器事件类型的不同，可以把Microsoft SQL Server 2008系统提供的系统提供的DML触发器分成触发器分成3种类型，即种类型，即INSERT类型、类型、UPDATE类型和类型和DELETE类型。这也是类型。这也是DML触发器的基本类型。触发器的基本类型。2022年6月4日第44页创建创建DML触发器触发器创建创建DML触发器的触发器的CREATE TRIGGER语句的基本语句的基本语法形式如下：语法形式如下：CREATE TRIGGER trigger_name ON table_name

44、_or_view_name WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS sql_statement2022年6月4日第45页DML触发器的工作原理触发器的工作原理向表中插入数据时，向表中插入数据时，INSERT触发器触发执行。当触发器触发执行。当INSERT触发器触发时，新的记录增加到触发器表中和触发器触发时，新的记录增加到触发器表中和inserted表表中。中。inserted表是一个逻辑表，保存了所插入记录的备份，表是一个逻辑表，保存了所插入记录的备份，允许用户参考允许用户参考INSERT语句中数

45、据。触发器可以检查语句中数据。触发器可以检查inserted表，来确定该触发器的操作是否应该执行和如何表，来确定该触发器的操作是否应该执行和如何执行。在执行。在inserted表中的记录，总是触发器表中一行或多表中的记录，总是触发器表中一行或多行记录的冗余。行记录的冗余。2022年6月4日第46页DELETE触发器触发器当触发一个当触发一个DELETE触发器时，被删除的记触发器时，被删除的记录放在一个特殊的录放在一个特殊的deleted表中。表中。deleted表是一个逻辑表，用来保存已经从表中删表是一个逻辑表，用来保存已经从表中删除的记录。该除的记录。该deleted表允许参考原来的表允许参

46、考原来的DELETE语句删除的已经记录在日志中的语句删除的已经记录在日志中的数据。数据。2022年6月4日第47页UPDATE触发器触发器修改一条记录就等于插入一条新记录同时删除一条修改一条记录就等于插入一条新记录同时删除一条旧记录。同样，旧记录。同样，UPDATE语句也可以看成是由删语句也可以看成是由删除一条记录的除一条记录的DELETE语句和增加一条记录的语句和增加一条记录的INSERT语句组成。当在某一个有语句组成。当在某一个有UPDATE触发触发器表的上面修改一条记录时，表中原来的记录移器表的上面修改一条记录时，表中原来的记录移动到动到deleted表中，修改过的记录插入到了表中，修改

47、过的记录插入到了inserted表中。触发器可以检查表中。触发器可以检查deleted表和表和inserted表以及被修改的表，以便确定是否修改表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。了多个行和应该如何执行触发器的操作。2022年6月4日第48页一般地，开发触发器的过程包括用户需求分一般地，开发触发器的过程包括用户需求分析、确定触发器的逻辑结构、编写触发器析、确定触发器的逻辑结构、编写触发器代码和测试触发器。代码和测试触发器。2022年6月4日第49页2DML触发器的分类触发器的分类(1)After触发器：在记录改变完之后，才会被触发器：在记录改变完之后，才会被激

48、活执行，主要用于记录变更后的处理或激活执行，主要用于记录变更后的处理或检查，一旦发现错误，也可以用检查，一旦发现错误，也可以用rollback transaction语句回滚本次操作。语句回滚本次操作。(2)Insead of触发器：一般用于取代原本的操触发器：一般用于取代原本的操作，在记录变更之前发生，它并不执行原作，在记录变更之前发生，它并不执行原来来SQL语句里的操作，而去执行触发器本语句里的操作，而去执行触发器本身定义的操作。身定义的操作。3定义定义DML触发器的注意事项触发器的注意事项lAfter触发器只能用于数据表中，触发器只能用于数据表中，instead of触发器可以用触发器可

49、以用于数据表和视图上，但两种触发器都不可以建立在临时表于数据表和视图上，但两种触发器都不可以建立在临时表上。上。l在同一个数据表中，对每个操作而言可以建立多个在同一个数据表中，对每个操作而言可以建立多个after触触发器，但发器，但instead of触发器针对每个操作只能建立一个。触发器针对每个操作只能建立一个。l如果针对某个操作既设置了如果针对某个操作既设置了after触发器又设置了触发器又设置了instead of触发器，那么触发器，那么instead of触发器一定会激活，触发器一定会激活，after触发触发器就不一定会激活。器就不一定会激活。lTruncate table语句虽然像语

50、句虽然像delete一样可以删除记录，但一样可以删除记录，但不能激活不能激活delete类型触发器，因为类型触发器，因为truncate table语句是语句是不记入日志的。不记入日志的。4定义定义DML触发器触发器例：如果例：如果sc表发生更改，以下示例将向指定人员表发生更改，以下示例将向指定人员 (gfma) 发送电子邮件发送电子邮件。USE database_demo;CREATE TRIGGER reminderON scAFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail profile_name = xs Admi

51、nistrator, recipients = , body = 成绩表发生变动成绩表发生变动, subject = Reminder;使用使用SQL Server邮件服务步骤邮件服务步骤 (1)配置数据库邮件配置数据库邮件(2)启用数据库邮件启用数据库邮件我们使用我们使用 SQL Mail XPs 选项可在此服务器上启用选项可在此服务器上启用 SQL Mail(该选项默认是关闭的该选项默认是关闭的)。sp_configure show advanced options, 1 Reconfigure; sp_configure SQL Mail XPs, 1 Reconfigure;设置立即生

52、效，无需停止并重新启动服务器。设置立即生效，无需停止并重新启动服务器。5设置设置after触发器的顺序触发器的顺序在在SQL Server2008中，可以用存储过程中，可以用存储过程sp_settriggerorder为每一个操作指定一个最先为每一个操作指定一个最先执行的执行的after触发器和最后执行的触发器和最后执行的after触发器。触发器。语法：语法：sp_settriggerorder triggername,triggerorder,triggeractionTriggername：触发器名，要用单引号括起来；：触发器名，要用单引号括起来；Triggerorder：激活次序，可以为

53、：激活次序，可以为first，Last和和None。None代表不指定激活次序，由程序任意代表不指定激活次序，由程序任意触发。触发。Triggeraction：激活触发器的动作。：激活触发器的动作。注意：注意：l每个操作最多只能设一个每个操作最多只能设一个First触发器和一个触发器和一个Last触发器。触发器。l如果要取消已经设好的如果要取消已经设好的First触发器和触发器和Last触发器触发器，只要把他们设为，只要把他们设为None触发器即可。触发器即可。l如果用如果用alter命令修改触发器内容后，该触发器会命令修改触发器内容后，该触发器会自动变成自动变成none触发器。触发器。l只有

54、只有after触发器可以设置激活顺序，触发器可以设置激活顺序，instead of触发器不可以设置激活次序。触发器不可以设置激活次序。l激活触发器的动作必须和触发器内部的激活动作激活触发器的动作必须和触发器内部的激活动作一致。一致。6设计设计instead of触发器触发器（1）使用范围）使用范围通常以下几种情况下，建议使用通常以下几种情况下，建议使用Instead of触触发器：发器：l数据库里的数据禁止修改；数据库里的数据禁止修改；l有可能要回滚修改的有可能要回滚修改的SQL语句；语句；l在视图中使用触发器；在视图中使用触发器；l用自己的方式去修改数据。用自己的方式去修改数据。例子例子例：

55、设计触发器，使得只有在指定时间段内可以录入例：设计触发器，使得只有在指定时间段内可以录入sc表中的成绩。表中的成绩。Create trigger time_sc_trigger on scInstead of insert AsIf getdate() between 2015-6-20 and 2015-6-30beginDeclare sno char(10),cno char(10),grade dec(10,1)Select sno=sno,cno=cno,grade=grade from insertedinsert into sc values(sno,cno,grade)endE

56、lseRaiserror(此时间不能录入数据此时间不能录入数据,10,1)7查看触发器查看触发器方法一：方法一：使用使用sys.triggers对象目录视图可以查看触发对象目录视图可以查看触发器：器：Select * from sys.triggers方法二：使用方法二：使用sp_help系统存储过程系统存储过程方法三：使用方法三：使用sp_helptext系统存储过程可以系统存储过程可以查看触发器的文本信息。查看触发器的文本信息。使用使用sys.triggers对象目录视图对象目录视图2022年6月4日第62页使用使用sp_helptext系统存储过程系统存储过程2022年6月4日第63页8

57、修改触发器修改触发器修改触发器内容：使修改触发器内容：使alter trigger命令命令 ；修改触发器名称，使用修改触发器名称，使用sp_rename系统存储系统存储过程。过程。9禁用与启用禁用与启用DML触发器触发器ALTER TABLE table_name Disable/enable trigger trigger_name例：禁用触发器例：禁用触发器time_sc_trigger。ALTER TABLE SC DISABLE TRIGGER TIME_SC_TRIGGERDDL 触发器触发器 lDDL触发器与触发器与DML触发器有许多类似的地触发器有许多类似的地方，都可以自动触发完

58、成规定的操作或使方，都可以自动触发完成规定的操作或使用用CREATE TRIGGER语句创建等，但是也语句创建等，但是也有一些不同的地方。例如，有一些不同的地方。例如，DDL触发器的触发器的触发事件主要是触发事件主要是CREATE、ALTER、DROP以及以及GRANT、DENY、REVOKE等等语句，并且触发的时间条件只有语句，并且触发的时间条件只有AFTER，没有没有INSTEAD OF。1.使用时机使用时机一般来说，在以下几种情况下可以使用一般来说，在以下几种情况下可以使用DDL触发器：触发器：l数据库里的架构或数据表架构很重要，不数据库里的架构或数据表架构很重要，不允许修改；允许修改；

59、l防止数据库或数据表被误删除；防止数据库或数据表被误删除；l在修改某个数据表结构的同时修改另一个在修改某个数据表结构的同时修改另一个数据表的相应结构；数据表的相应结构；l要记录对数据库结构操作的事件。要记录对数据库结构操作的事件。2定义定义DDL触发器触发器创建创建DDL触发器的触发器的CREATE TRIGGER语句语句的基本语法形式如下：的基本语法形式如下：CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER event_type AS sql_statementCREATE TRI

60、GGER trigger_name ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER event_type AS sql_statementALL SERVER：将：将 DDL或登录触发器的作用域应用于当前服务器。如果指定了或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现此参数，则只要当前服务器中的任何位置上出现event_type或或event_group，就会激发该触发器。，就会激发该触发器。DATABASE：将：将DDL触发器的作用域应用于当前数据库。如果指定了此参数，触发器的作用域应用于当