资源描述
,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,*,页,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2020/9/14,#,2024年8月9日,第,1,页,第,4,章 存储过程和触发器,本章概述,本章要点,本章内容,2024年8月9日,第,2,页,本章概述,如何提高,Transact-SQL,语句的执行效率呢？如何加强数据库中数据完整性的机制呢？这些问题的解决都依赖于数据库的编程对象。典型的数据库编程对象包括视图、存储过程、触发器、函数等。存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。触发器是一种特殊类型的存储过程，可以实现自动化的操作。,本章将全面研究存储过程、触发器特点和使用方式。,2024年8月9日,第,3,页,本章要点,存储过程的特点、类型和作用,使用,CREATE PROCEDURE,语句创建存储过程,存储过程的执行方式,DML,触发器的特点和创建方式,DML,触发器的工作原理,使用,CREATE TRIGGER,语句创建,DML,触发器,DDL,触发器的特点和创建方式,2024年8月9日,第,4,页,本章内容,4.1,存储过程,4.2,触发器,4.3,本章小结,4.1,存储过程,存储过程可以提高应用程序的设计效率和增强系统的安全性。本节将全面介绍存储过程的特点、类型、创建、执行等内容。,2024年8月9日,第,5,页,类型,在,Microsoft SQL Server 2008,系统中，提供了,3,种基本的存储过程类型，即用户定义的存储过程、扩展存储过程和系统存储过程。,2024年8月9日,第,6,页,系统存储过程,系统存储过程是方便查询系统信息或完成系统管理任务，一般以,sp_,开头，并存放在,sys,架构中。,常用的系统存储过程有：,1,Sp_help,报告有关数据库对象（,sys.sysobjects,兼容视图中列出的所有对象）、用户定义数据类型或某种数据类型的信息。,语法：,sp_help objname=name,objname=name,：某个对象的名称。,2.sp_helpdb,报告有关指定数据库或所有数据库的信息。,语法：,sp_helpdb dbname=name,dbname=name,：要报告其信息的数据库的名称。如果未指定,name,，则,sp_helpdb,将报告,sys.databases,目录视图中所有数据库的信息。,3.sp_helpfile,返回与当前数据库关联的文件的物理名称及属性。使用此存储过程确定附加到服务器或从服务器分离的文件名。,语法：,sp_helpfile =name,=name,是当前数据库中任意文件的逻辑名称。如果未指定,name,，则返回当前数据库中所有文件的属性。,4.sp_help,返回与当前数据库相关联的文件组的名称及属性。,语法：,sp_help =name,=name,：当前数据库中任意文件组的逻辑名称。如果没有指定,name,，则列出当前数据库中的所有文件组并只显示在结果集部分中显示的第一个结果集。,5.sp_helpindex,报告有关表或视图上索引的信息。,语法：,sp_helpindex objname=name,objname=name,：用户定义的表或视图的名称。,6.sp_helpsort,显示,SQL Server 2008,实例的排序顺序和字符集。,7.sp_helptext,显示用户定义规则的定义、默认值、未加密的,Transact-SQL,存储过程、用户定义,Transact-SQL,函数、触发器、计算列、,CHECK,约束、视图或系统对象（如系统存储过程）。,语法：,sp_helptext objname=name ,columnname=computed_column_name,参数：,objname=name,：架构范围内的用户定义对象的限定名称和非限定名称。仅当指定限定对象时才需要引号。如果提供的是完全限定名称（包括数据库名称），则数据库名称必须是当前数据库的名称。对象必须在当前数据库中。,name,的数据类型为,nvarchar(776),，无默认值。,columnname=computed_column_name,：要显示其定义信息的计算列的名称。,例,1,：查看函数,multisql_tablevalued_func,信息。,sp_helptext 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:,当前数据库中将为其返回触发器信息的表的名称。,triggertype=type:,将为其返回有关信息的,DML,触发器的类型。可以是,DELETE,、,INSERT,、,UPDATE,。,9.sp_monitor,：显示有关,Microsoft SQL Server,的统计信息。,10.sp_rename,在当前数据库中更改用户创建对象的名称。此对象可以是表、索引、列、别名数据类型或,Microsoft.NET Framework,公共语言运行时,(CLR),用户定义类型。,注意：,更改对象名的任一部分都可能破坏脚本和存储过程。建议删除该对象，然后使用新名称重新创建该对象。,语法：,sp_rename objname=object_name,newname=new_name ,objtype=object_type,参数：,objname=object_name,：用户对象或数据类型的名称。如果要重命名的对象是表中的列，则,object_name,的格式必须是,table.column,。如果要重命名的对象是索引，则,object_name,的格式必须是,table.index,。,newname=new_name,：指定对象的新名称。,objtype=object_type,：要重命名的对象的类型。,COLUMN,表示 要重命名的列，,DATABASE,表示要重命名用户定义数据库，,INDEX,表示重命名用户定义索引，,OBJECT,可用于重命名约束（,CHECK,、,FOREIGN KEY,、,PRIMARY/UNIQUE KEY,）、用户表和规则等对象，,USERDATATYPE,表示重命名通过执行,CREATE TYPE,或,sp_addtype,添加别名数据类型或,CLR,用户定义类型。,11.sp_who,提供有关,Microsoft SQL Server,数据库引擎实例中的当前用户、会话和进程的信息。可以筛选信息以便只返回那些属于特定用户或特定会话的非空闲进程。,语法：,sp_who loginame=login|session ID|ACTIVE,参数,loginame=login|session ID|ACTIVE,用于筛选结果集。,login,用于标识属于特定登录名的进程。,session ID,是属于,SQL Server,实例的会话标识号。,ACTIVE,排除正在等待用户发出下一个命令的会话。,如果没有提供任何值，则过程报告属于实例的所有会话。,12.sp_columns,返回当前环境中可查询的指定表或视图的列信息。,语法：,sp_columns table_name=object ,table_owner=owner,table_qualifier=qualifier,column_name=column,ODBCVer=ODBCVer,参数：,table_name=object,：用于返回目录信息的表或视图的名称。支持通配符模式匹配。,table_owner=owner,：用于返回目录信息的表或视图的对象所有者。支持通配符模式匹配。如果未指定,owner,，则是默认表或视图可见性规则。,如果当前用户拥有的表或视图具有指定名称，则返回该表的列。如果未指定,owner,，并且当前用户不拥有具有指定,object,的表或视图，则,sp_columns,将搜索数据库所有者所拥有的具有指定,object,的表或视图。如果有，则返回该表的列。,table_qualifier=qualifier,：表或视图限定符的名称。在,SQL Server,中，此列表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。,column_name=column,：一个单独的列，当只需要目录信息的一列时可使用该参数。如果未指定,column,，则返回所有列。支持通配符模式匹配。,ODBCVer=ODBCVer,：当前使用的,ODBC,版本。,例：查询,dbo,架构中的,student,表的列信息。,sp_columns student,dbo,database_demo,13.sp_databases,列出驻留在,SQL Server 2005,数据库引擎实例中的数据库或可以通过数据库网关访问的数据库。,14.sp_fkeys,返回当前环境的逻辑外键信息。该过程显示各种外键关系，包括禁用的外键。,语法,:,sp_fkeys pktable_name=pktable_name,pktable_owner=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=pktable_owner,：表（带主键）的所有者的名称。不支持通配符模式匹配。,在,SQL Server,中，如果当前用户拥有具有指定名称的表，则返回该表的列。如果未指定,pktable_owner,，并且当前用户没有具有指定名称,pktable_name,的表，则此过程将查找由数据库所有者拥有并具有指定名称,pktable_name,的表。如果有，则返回该表的列。,pktable_qualifier=pktable_qualifier,：表（带主键）限定符的名称。在,SQL Server,中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。,fktable_name=fktable_name,：表（带外键）的名称。不支持通配符模式匹配。必须提供该参数或,pktable_name,参数，或二者都提供。,fktable_owner=fktable_owner,：用于表（带外键）的所有者的名称。不支持通配符模式匹配。,fktable_qualifier=fktable_qualifier,：在,SQL Server,中，限定符表示数据库名称。在某些产品中，该列表示表所在数据库环境的服务器名。,例：查看引用,student,表的外建表信息。,Sp_fkeys student,15.sp_pkeys,返回当前环境中单个表的主键信息。,语法：,sp_pkeys table_name=name,table_owner=owner,table_qualifier=qualifier,参数：,table_name=name,：为其返回信息的表。不支持通配符模式匹配。,table_owner=owner,：为指定的表指定所有者。,在,SQL Server,中，如果当前用户拥有一个具有指定名称的表，则返回该表的列。如果未指定,owner,，并且当前用户没有名称为指定,name,的表，则此过程将查找由数据库所有者拥有，并且名称为指定,name,的表。如果有，则返回该表的列。,table_qualifier=qualifier,：表限定符。在,SQL Server,中，此列表示数据库名称。在某些产品中，它表示表所在数据库环境的服务器名称。,例：查看,student,表的主键信息。,Sp_pkeys student,16.sp_server_info,返回,SQL Server,、数据库网关或基础数据源的属性名称和匹配值的列表。,语法,:,sp_server_info attribute_id=attribute_id,attribute_id=attribute_id:,属性的整数,ID,。,17.sp_tables,返回可在当前环境中查询的对象列表。也就是说，返回任何能够在,FROM,子句中出现的对象（不包括同义词对象）。,语法,:,sp_tables table_name=name,table_owner=owner,table_qualifier=qualifier,table_type=type,fUsePattern=fUsePattern;,参数,:,table_name=name,：用来返回目录信息的表。支持通配符模式匹配。,table_owner=owner,：用于返回目录信息的表的所有者。,o,支持通配符模式匹配。如果未指定所有者，则遵循基础,DBMS,的默认表可见性规则。,table_qualifier=qualifier,：在,SQL Server,中，此列表示数据库名称。,table_type=type,type,：由逗号分隔的值列表，该列表提供有关所有指定的表类型的表的信息。,例：查看所有,dbo,架构中的表信息。,sp_tables%,dbo,18.sp_stored_procedures,返回当前环境中的存储过程列表。,语法：,sp_stored_procedures sp_name=name,sp_owner=schema,sp_qualifier=qualifier,fUsePattern=fUsePattern,参数：,sp_name=name,：用于返回目录信息的过程名。支持通配符模式匹配。,sp_owner=schema,：该过程所属架构的名称。支持通配符模式匹配。,在,SQL Server,中，如果当前架构包含具有指定名称的过程，则返回此过程。如果指定了非限定存储过程，则数据库引擎按以下顺序搜索此过程：当前数据库的,sys,架构，调用方的默认架构（在使用批或动态,SQL,执行时）；或者，如果非限定的过程名称出现在另一个过程定义的主体中，则接着搜索包含这一过程的架构，当前数据库中的,dbo,架构。,qualifier=qualifier,：在,SQL Server,中，,qualifier,表示数据库名称。,fUsePattern=fUsePattern,：确定是否将下划线,(_),、百分号,(%),或方括号,(),解释为通配符。,0=,禁用模式匹配。,1=,启用模式匹配。,存储过程的特点和类型,存储过程是一个可重用的代码模块，可以高效率地完成指定的操作。在,Microsoft SQL Server 2008,系统中，可以使用,Transact-SQL,语言编写存储过程，也可以使用,CLR,方式编写存储过程。使用,CLR,编写存储过程是,Microsoft SQL Server 2008,系统与,.NET,框架紧密集成的一种表现形式。,2024年8月9日,第,29,页,使用,CREATE PROCEDURE,语句,在,Microsoft SQL Server 2008,系统中，可以使用,CREATE PROCEDURE,语句创建存储过程。需要强调的是，必须具有,CREATE PROCEDURE,权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。,在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或批处理表明成功或失败的状态值、捕捉和处理潜在错误的错误处理语句。,2024年8月9日,第,30,页,CREATE PROCEDURE,语句,CREATE PROCEDURE,语句的基本语法形式如下所示：,CREATE PROCEDURE procedure_name,parameter_name data_type,WITH procedure_option,AS,sql_statement,2024年8月9日,第,31,页,创建带有,OUTPUT,类型参数的存储过程,2024年8月9日,第,32,页,例,1,：创建存储过程，实现查询某系学生人数的功能，以,output,参数返回。,Create procedure mp_count_xs num int output,dept char(20),As,Begin,Select num=count(*)from student where sdept=dept;,End,执行：,Declare n int,Exec mp_count_xs n output,CS,Print n,建立带默认值使用通配符的存储过程,例,2,：建立带默认值使用通配符的存储过程，实现按姓氏查询学生信息。,Create procedure mp_name_xs name varchar(20)=%,As,Begin,Select*from student where sname like name;,End,执行：,Declare n int,Exec mp_name_xs,张,%,Print n,注意：,name,的数据类型是可变长。,修改和删除存储过程,在,Microsoft SQL Server 2008,系统中，可以使用,ALTER PROCEDURE,语句修改已经存在的存储过程。修改存储过程，不是删除和重建存储过程，其目的是保持存储过程的权限不发生变化。但是，如果删除存储过程并且重新创建同名的存储过程，那么与该存储过程对象相关的权限都需要重新定义。,2024年8月9日,第,34,页,嵌套调用存储过程,例,3,：创建调用,mp_name_xs,的存储过程。,create procedure call_procedure,as,exec mp_name_xs,刘,%,不显示影响行数的通知,SET NOCOUNT ON/OFF,关闭或打开影响行数的通知,存储过程的执行过程,存储过程创建之后，在第一次执行时需要经过语法分析阶段、解析阶段、编译阶段和执行阶段。,执行时可以重新编译存储过程,2024年8月9日,第,37,页,查看存储过程的信息,在,Microsoft SQL Server 2008,系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。,使用,sp_stored_procedures,查看存储过程，使用,sp_helptext,查看指定存储过程的源代码信息。,注意：不能查看加密后的存储过程源代码。,使用,OBJECT_DEFINITION,元数据函数查看存储过程：,Select OBJECT_DEFINITION(OBJECT_ID(mp_prcudure),2024年8月9日,第,38,页,加密存储过程,加密之后的存储过程可以正常使用，但是无法查看该存储过程的内容，在,SSMS,中也不能修改存储过程的内容（只能通过,SQL,语句修改）。,例,4,：创建一个加密的存储过程。,Create proc mp_prcudure xh char(10),With encryption,As,Select*from student where sno=xh,4.2,触发器,Microsoft SQL Server 2008,系统提供了两种强制业务逻辑和数据完整性的机制，即约束技术和触发器技术。,2024年8月9日,第,40,页,触发器的概念和类型,一般地认为，触发器是一种特殊类型的存储过程，它包括了大量的,Transact-SQL,语句。但是触发器又与存储过程不同，存储过程可以由用户直接调用执行，而触发器不能被直接调用执行，它只能自动执行。,按照触发事件的不同，可以把,Microsoft SQL Server 2008,系统提供的触发器分成两种类型，即,DML,触发器和,DDL,触发器。,2024年8月9日,第,41,页,当数据库中发生数据操纵语言(Data Manipulation Language，简称为DML)事件时将调用DML触发器。,当数据库中发生数据定义语言(Data Definition Language，简称为DDL)事件时将调用DDL触发器。,DML,触发器,DML,事件包括在指定表或视图中修改数据的,INSERT,语句、,UPDATE,语句或,DELETE,语句。在,DML,触发器中，可以执行查询其他表的操作，也可以包含更加复杂的,Transact-SQL,语句。,DML,触发器将触发器本身和触发事件的语句作为可以在触发器内回滚的单个事务对待。也就是说，在执行触发器操作过程中，如果检测到错误发生，则整个触发事件语句和触发器操作的事务自动回滚。,2024年8月9日,第,43,页,DML,触发器的类型,按照触发器事件类型的不同，可以把,Microsoft SQL Server 2008,系统提供的,DML,触发器分成,3,种类型，即,INSERT,类型、,UPDATE,类型和,DELETE,类型。这也是,DML,触发器的基本类型。,2024年8月9日,第,44,页,创建,DML,触发器,创建,DML,触发器的,CREATE TRIGGER,语句的基本语法形式如下：,CREATE TRIGGER trigger_name,ON table_name_or_view_name,WITH ENCRYPTION,FOR|AFTER|INSTEAD OF DELETE ,INSERT ,UPDATE ,AS sql_statement,2024年8月9日,第,45,页,DML,触发器的工作原理,向表中插入数据时，,INSERT,触发器触发执行。当,INSERT,触发器触发时，新的记录增加到触发器表中和,inserted,表中。,inserted,表是一个逻辑表，保存了所插入记录的备份，允许用户参考,INSERT,语句中数据。触发器可以检查,inserted,表，来确定该触发器的操作是否应该执行和如何执行。在,inserted,表中的记录，总是触发器表中一行或多行记录的冗余。,2024年8月9日,第,46,页,DELETE,触发器,当触发一个,DELETE,触发器时，被删除的记录放在一个特殊的,deleted,表中。,deleted,表是一个逻辑表，用来保存已经从表中删除的记录。该,deleted,表允许参考原来的,DELETE,语句删除的已经记录在日志中的数据。,2024年8月9日,第,47,页,UPDATE,触发器,修改一条记录就等于插入一条新记录同时删除一条旧记录。同样，,UPDATE,语句也可以看成是由删除一条记录的,DELETE,语句和增加一条记录的,INSERT,语句组成。当在某一个有,UPDATE,触发器表的上面修改一条记录时，表中原来的记录移动到,deleted,表中，修改过的记录插入到了,inserted,表中。触发器可以检查,deleted,表和,inserted,表以及被修改的表，以便确定是否修改了多个行和应该如何执行触发器的操作。,2024年8月9日,第,48,页,一般地，开发触发器的过程包括用户需求分析、确定触发器的逻辑结构、编写触发器代码和测试触发器。,2024年8月9日,第,49,页,2DML触发器的分类,(1)After,触发器：在记录改变完之后，才会被激活执行，主要用于记录变更后的处理或检查，一旦发现错误，也可以用,rollback transaction,语句回滚本次操作。,(2)Insead of,触发器：一般用于取代原本的操作，在记录变更之前发生，它并不执行原来,SQL,语句里的操作，而去执行触发器本身定义的操作。,3定义DML触发器的注意事项,After,触发器只能用于数据表中，,instead of,触发器可以用于数据表和视图上，但两种触发器都不可以建立在临时表上。,在同一个数据表中，对每个操作而言可以建立多个,after,触发器，但,instead of,触发器针对每个操作只能建立一个。,如果针对某个操作既设置了,after,触发器又设置了,instead of,触发器，那么,instead of,触发器一定会激活，,after,触发器就不一定会激活。,Truncate table,语句虽然像,delete,一样可以删除记录，但不能激活,delete,类型触发器，因为,truncate table,语句是不记入日志的。,4定义DML触发器,例：如果,sc,表发生更改，以下示例将向指定人员,(gfma),发送电子邮件。,USE database_demo;,CREATE TRIGGER reminder,ON sc,AFTER INSERT,UPDATE,DELETE,AS,EXEC msdb.dbo.sp_send_dbmail,pro=xs Administrator,recipients=gfma,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;,设置立即生效，无需停止并重新启动服务器。,5设置after触发器的顺序,在,SQL Server2008,中，可以用存储过程,sp_settriggerorder,为每一个操作指定一个最先执行的,after,触发器和最后执行的,after,触发器。,语法：,sp_settriggerorder triggername,triggerorder,triggeraction,Triggername,：触发器名，要用单引号括起来；,Triggerorder,：激活次序，可以为,first,，,Last,和,None,。,None,代表不指定激活次序，由程序任意触发。,Triggeraction,：激活触发器的动作。,注意：,每个操作最多只能设一个,First,触发器和一个,Last,触发器。,如果要取消已经设好的,First,触发器和,Last,触发器，只要把他们设为,None,触发器即可。,如果用,alter,命令修改触发器内容后，该触发器会自动变成,none,触发器。,只有,after,触发器可以设置激活顺序，,instead of,触发器不可以设置激活次序。,激活触发器的动作必须和触发器内部的激活动作一致。,6设计instead of触发器,（,1,）使用范围,通常以下几种情况下，建议使用,Instead of,触发器：,数据库里的数据禁止修改；,有可能要回滚修改的,SQL,语句；,在视图中使用触发器；,用自己的方式去修改数据。,例子,例：设计触发器，使得只有在指定时间段内可以录入,sc,表中的成绩。,Create trigger time_sc_trigger on sc,Instead of insert,As,If getdate()between 2015-6-20 and 2015-6-30,begin,Declare sno char(10),cno char(10),grade dec(10,1),Select sno=sno,cno=cno,grade=grade from inserted,insert into sc values(sno,cno,grade),end,Else,Raiserror(,此时间不能录入数据,10,1),7查看触发器,方法一：,使用,sys.triggers,对象目录视图可以查看触发器：,Select*from sys.triggers,方法二：使用,sp_help,系统存储过程,方法三：使用,sp_helptext,系统存储过程可以查看触发器的文本信息。,使用,sys.triggers,对象目录视图,2024年8月9日,第,62,页,使用,sp_helptext,系统存储过程,2024年8月9日,第,63,页,8修改触发器,修改触发器内容：使,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_TRIGGER,DDL,触发器,DDL,触发器与,DML,触发器有许多类似的地方，都可以自动触发完成规定的操作或使用,CREATE TRIGGER,语句创建等，但是也有一些不同的地方。例如，,DDL,触发器的触发事件主要是,CREATE,、,ALTER,、,DROP,以及,GRANT,、,DENY,、,REVOKE,等语句，并且触发的时间条件只有,AFTER,，没有,INSTEAD OF,。,1.使用时机,一般来说，在以下几种情况下可以使用,DDL,触发器：,数据库里的架构或数据表架构很重要，不允许修改；,防止数据库或数据表被误删除；,在修改某个数据表结构的同时修改另一个数据表的相应结构；,要记录对数据库结构操作的事件。,2,定义,DDL,触发器,创建,DDL,触发器的,CREATE TRIGGER,语句的基本语法形式如下：,CREATE TRIGGER trigger_name,ON ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER event_type,AS sql_statement,CREATE TRIGGER trigger_name,ON ALL SERVER|DATABASE,WITH ENCRYPTION,FOR|AFTER event_type,AS sql_statement,ALL SERVER,：将,DDL,或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现,event_type,或,event_group,，就会激发该触发器。,DATABASE,：将,DDL,触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现,event_type,或,event_group,，就会激发该触发器。,FOR,或,AFTER,是同一个意思，指定的是,After,触发器。,event_type:,触发事件。,例,1,：建立一个,DDL,触发器，用于保护数据库中的数据表不被修改、删除。,Create trigger disable_table,ON DATABASE,FOR DROP_TABLE,ALTER_TABLE,AS,Print,对不起，您不能对数据表进行操作,ROLLBACK;,例,2,：建立一个,DDL,触发器，用于保护当前,SQL Server,服务器里的所有数据库不被删除。,Create trigger disable_db,ON all server,FOR DROP_DATABASE,AS,Print,对不起，您不能删除数据库,ROLLBACK;,例,3,：建立一个,DDL,触发器，用于记录数据库修改状态。,（,1,）先建立一个用于记录数据库修改状态的表：,CREATE TABLE,日志记录表,(,编号,int IDENTITY(1,1)NOT NULL PRIMARY KEY,事件,Varchar(5000)NULL,所用语句,varchar(5000)NULL,操作者,varchar(50)NULL,发生时间,datetime NULL),（,2,）建立,DDL,触发器：,Create trigger log_tri,ON DATABASE,FOR DDL_DATABASE_LEVEL_EVENTS,AS,DECLARE log XML,SET log=EVENTDATA(),INSERT into,日志记录表,(,事件,所用语句,操作者,发生时间,),VALUES(log.value(/EVENT_INSTANCE/EventType)1,varchar(100),log.value(/EVENT_INSTANCE/TSQLCommand)1,varchar(2000),CONVERT(varchar(100),CURRENT_USER),GETDATE();,其中,EVENTDATA,是个数据库函数，它的作用是以,XML,格式返回有关服务器或数据库事件的信息；,log.value,是返回,log,这个,XML,节点的值，节点的位置是括号里的第一个参数。,测试：,create table test(col1 char(2),3.启用/禁用触发器,使用,DISABLE TRIGGER,禁用触发器,ENABLE TRIGGER,启用触发器。,语法：,DISABLE TRIGGERschema_name.trigger_name,.n|ALL,ONobject_name|DATABASE|ALL SERVER;,参数：,schema_name,：触发器所属架构的名称。不能为,DDL,或登录触发器指定,schema_name,。,trigger_name,：要禁用的触发器的名称。,ALL,：指示禁用在,ON,子句作用域中定义的所有触发器。,object_name,：要对其创建要执行的,DML,触发器,trigger_name,的表或视图的名称。,DATABASE,：对于,DDL,触发器，指示所创建或修改的,trigger_name,将在数据库范围内执行。,ALL SERVER,：对于,DDL,触发器，指示所创建或修改的,trigger_name,将在服务器范围内执行。,ALL SERVER,也适用于登录触发器。,备注：,默认情况下，创建触发器后会启用触发器。禁用触发器不会删除该触发器。该触发器仍然作为对象存在于当前数据库中。但是，当执行编写触发器程序所用的任何,Transact-SQL,语句时，不会激发触发器。,可以使用,ENABLE TRIGGER,重新启用触发器。还可以通过使用,ALTER TABLE,来禁用或启用为表所定义的,DML,触发器。,例,1,：禁用所有基于数据库的触发器。,Disable trigger all on database,例,2,：禁用在服务器范围内创建的所有,DDL,触发器。,DISABLE Trigger ALL ON ALL SERVER;,四、触发器的使用技巧,1,如何知道触发器修改了多少条记录,一种操作类型在一次操作时，对一个触发器只触发一次。,例如：运行一个,Update,语句，有可能一次更新了,10,条记录，但对于,after update,触发器，只激活一次。但是在,inserted,和,deleted,里会有,10,条记录，这时，只要利用,Rowcount,这个系统变量就可以得知更新了多少条记录。,例：创建一个更新触发器，当修改,sc,表时，分别显示每一行,grade,修改后的值。,creata trigger update_sc on sc,after update,as,declare n int,i int,grade int,set n=rowcount,set i=1,declare grade_cur cursor for select grade from inserted;,open grade_cur;,while(i=n),begin,print,目前更新第,+cast(i as char(3)+,行,fetch grade_cur into grade;,print,新的成绩为,+cast(grade as char(10),set i=i+1,end,close grade_cur;,deallocate grade_cur;,测试：,update sc set grade=grade+3,2,如何知道插入记录的自动编号是多少,INDENTITY,可以获得刚插入的记录的标识值。,3,如何知道某个字段是否被修改,在,update,触发器和,insert,触发器里，可以用,UPDATE,（字段名）来判断某个字段是否被修改，返回的是一个布尔值。,4,如何返回错误信息,Print,输出的自定义信息只有在查询编辑器窗格中运行,SQL,语句才能看到，其他的前端应用程序都不会显示这些自定义信息。如果想要看到提示信息，使用,Raiserror,语句。,4.3,本章小结,本章研究了存储过程、触发器的内容。首先，分析了存储过程的概念、类型和特点，讲述了存储过程的创建和执行方式。其次，研究了触发器的作用和特点，详细讨论了触发器的创建过程和工作原理。,结束语,当,你尽了自己的最大努力,时,，,失败,也是伟大,的，所以不要放弃，坚持就是正确的。,When You Do Your Best,Failure Is Great,So DonT Give Up,Stick To The,End,谢谢,大家,荣幸,这,一路，与你同行,ItS An Honor To Walk With You All The,Way,演讲人：,XXXXXX,时 间：,XX,年,XX,月,XX,日,
展开阅读全文