11-2 第11章 数据库编程（二）.pptx

第1页：第11章数据库编程（二）

第11章数据库编程（二）

第2页：复习：一

复习：一

• 一、T-SQL概述
  • 特点：综合统一、非过程化且面向集合、不同使用方式语法结构相同、易理解和掌握
  • 分类：数据定义语言DDL、数据操作语言DML、数据控制语言DCL和附加语言元素
• 二、T-SQL基础
  • 标识符(常规、分隔)、变量(局部、全局)、运算符（算术、赋值、字符串、比较、逻辑、位运算等）、函数（系统内置、用户定义）
• 三、T-SQL编程
  • 批处理(GO)、流程控制(BEGIN…END,IF,CASE,WHILE,WAITFOR)、错误捕获(TRY…CATCH,@@ERROR )、注释(,--)

第3页：事务编程

事务编程

• 数据库系统的主要特点之一是实现了数据共享，允许多个用户对数据进行同时访问。
• 当多个用户同时操作相同的数据时，如果不采取任何措施，则会造成数据异常。事务是为避免这些异常情况的发生而引入的一个概念。

第4页：什么是事务？

什么是事务？

• 事务(Transaction)是用户定义的一个数据库操作的序列，这些操作要么全做要么全不做，绝不能值完成部分操作，而另一部分操作没有执行。
• 事务中任何一条语句执行时出错，事务都会返回到事务开始前的状态。一个事务中的所有操作是一个不可分割的逻辑工作单元。

第5页：事务的ACID特性

事务的ACID特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

第6页：原子性

原子性

事务必须

数据库的逻辑工作单元，也是工作的最小单位。

一个事务包括的所有操作

一个逻辑上不可分割的单位，其所进行的操作要么全都执行，要么全都不执行。

第7页：一致性

一致性

• 事务执行的结果必须是使数据库从一个一致性状态转换到另一个一致性状态。
• 如果当数据库中只包括成功事务提交的结果时，数据库就处于一致性状态。
• 如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所做的修改有一部分已经写入物理数据库，这时数据库就处于一种不正确状态。
• 为了保证数据库处于一致性状态，所有的规则都必须应用于事务的修改，以保证所有数据的完整性和数据库的一致性。可见数据库的一致性和原子性是密不可分的。

第8页：隔离性

隔离性

• 一个事务的执行不能被其他事务干扰。即一个事务内部的操作和使用的数据对其他并发事务是隔离的。
• 并发执行的各个事务之间不能相互干扰，即事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是其他事务修改它之后的状态，事务不会识别中间状态的数据。

第9页：持久性

持久性

• 事务的持久性也称永久性，是指一个事务一旦提交，它对数据库中数据的改变就应该是永久的。接下来的其他操作或故障不应该对其执行结果有任何影响。

第10页：事务模式

事务模式

• 自动提交事务
• 显式事务
• 隐式事务
• 批处理级事务

第11页：自动提交事务

自动提交事务

• 自动提交事务是系统默认的事务管理模式，它是指每条单独的Transact-SQL语句都是一个事务，即在每条Transact-SQL语句成功执行后自动提交；如果遇到错误，则自动回滚该语句。

第12页：显式事务

显式事务

• 显式事务是指由用户可以用BEGINTRANSACTION语句显式地定义事务开始和COMMIT语句或ROLLBACK语句显式地定义事务的结束。

第13页：隐式事务

隐式事务

• 隐式事务是指在前一个事务完成（提交或回滚）时新事物隐式启动。但每个事务仍以COMMIT或ROLLBACK语句显式定义事务的结束来结束。

第14页：批处理级事务

批处理级事务

• 批处理级事务只能应用于多个活动结果集(ARS)，在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。
• 当批处理完成时没有提交或回滚的批处理级事务自动由SQL Server进行回滚。

第15页：事务处理语句

事务处理语句

• BEGIN TRANSACTION
• COMMIT TRANSACTION
• ROLLBACK TRANSACTION
• SAVE TRANSACTION

第16页：BEGIN TRANSACTION语句

BEGIN TRANSACTION语句

• BEGIN TRANSACTION语句定义一个显式事务的开始。其语法格式为：BEGIN { TRANSACTION | TRAN }[ {transaction_name|tran_name_variable}[ WITH MARK[ 'description' ] ] ]
• 参数说明：
  • transaction_name：指定显式定义事务的名称。
  • tran_name_variable:指定用户定义的、含有有效事务名的变量名，必须是CHAR、VARCHAR、NCHAR或N VARCHAR数据类型声明。
  • description：指定在日志中标记事务的字符串。

第17页：COMMIT TRANSACTION语句

COMMIT TRANSACTION语句

• COMMIT TRANSACTION语句标志一个成功的显式事务或隐性事务的结束。提交当前事务，事务中所有数据的改变在数据库中都将永久有效。
• 语法格式：COMMIT { TRAN | TRANSACTION } [transaction_name]

第18页：ROLLBACK TRANSACTION

ROLLBACK TRANSACTION

• ROLLBACK TRANSACTION语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。它也标志一个事务的结束。其语法格式为：ROLLBACK { TRAN | TRANSACTION }[ transaction_name | savepoint_name]
• 参数说明：savepoint_name：指定检查点的名称。

第19页：SAVE TRANSACTION语句

SAVE TRANSACTION语句

• SAVE TRANSACTION语句是在事务内设置保存点，它类似于C语言中GOTO语句的标号。其语法格式为：SAVE { TRAN | TRANSACTION } { savepoint_name }

第20页：例1：定义一个事务score_ m

例1：定义一个事务score_ m

• 定义一个事务score_ manager，将所有选修了课程号为00100002的学生的成绩都减少5分，成功则提交事务，失败则取消事务。USE TeachingDataGOBEGIN TRAN score_managerUPDATE ScoreInfoSET Score = Score-5 WHERE CID = '00100002'IF @@ERROR!=0ROLLBACK TRAN score_managerELSECOMMIT TRAN score_manager

第21页：例2：定义一个事务stu_add

例2：定义一个事务stu_add

【例2】 定义一个事务stu_add，主要操作是向StuInfo表中添加一条学生记录，如果添加成功，设置保存点，然后再修改该生所在的院系，如果修改失败，回滚到事务的保存点，否则提交该事务。USE TeachingDataGOBEGIN TRAN stu_addINSERT INTO StuInfo(SID,Sname,Sex,Dept,Major)VALUES('07011103','林敏', '女','管理科学与工程系','多媒体') IF @@ERROR!=0ROLLBACK TRAN stu_addELSESAVE TRAN sp1 UPDATE StuInfoSET Dept = '计算机系' WHERE SID = '07011103' IF @@ERROR!=0ROLLBACK TRAN sp1ELSECOMMIT TRAN stu_add

第22页：对上述代码的执行结果

对上述代码的执行结果

• 对上述代码的执行结果进行分析可知，如果在使用ROLLBACK TRANSACTION语句时
  • 如果指定了检查点名称，则事务回滚到设置检查点的为位置；
  • 如果指定了事务名称，则回滚到该事务执行前的状态；
  • 如果没有指定事务名称或保存点名称，则将事务回滚到事务执行前，如果是嵌套事务时，则该语句将所有内层事务回滚到最外面的BEGINTRANSACTION语句。

第23页：存储过程1、存储过程概述

存储过程1、存储过程概述

• 存储过程(Stored Procedure)是一组完成特定功能的SQL语句的集合，经编译后存储在数据库中。用户通过指定存储过程的名称和参数来执行存储过程。

第24页：使用存储过程的优点

使用存储过程的优点

• (1)模块化程序设计存储过程只需创建一次并存储在数据库中，可被应用程序反复调用，用户可以独立于应用程序而对存储过程进行修改。
• (2)提高执行速度当执行Transact-SQL程序代码时，SQL Server必须先检查语法是否正确，接着进行编译、优化，然后再执行操作，因此每条SQL语句在执行前都要耗费一些时间。而在创建存储过程时要进行SQL语法的正确性检查、编译和优化，在执行存储过程时就无需再重复这些步骤。而且存储过程在第一次调用后就常驻内存，每次执行时不需要再将存储过程从磁盘调入内存，因此执行速度很快。

第25页：降低网络通信量如

降低网络通信量如

(3)降低网络通信量如果建立了一个为完成某项操作而包括了数百行T-SQL语句的存储过程，客户端应用程序只需要通过网络向服务器发送一条存储过程名称和参数的调用语句，就可以让SQLServer执行存储过程中包括的SQL语句并执行数据处理；否则，在客户端应用程序使用T-SQL语句完成的话，需要在网络中发送完成此项操作的数百行的代码。(4)保证系统的安全性数据库系统管理员通过设置用户对存储过程的操作权限，从而实现对相应的数据访问权的限制，避免非授权用户对数据的访问。

第26页：存储过程的类型

存储过程的类型

• 系统存储过程
• 用户自定义存储过程
• 扩展存储过程。

第27页：系统存储过程

系统存储过程

• 系统存储过程是在安装SQL Server 2005时自动创建的存储过程，主要用于管理SQL Server和显示有关数据库及用户的信息。
• 从物理意义上讲，系统存储过程主要存储在master数据库中，名称以“sp_”做前缀。从逻辑上看，系统存储过程出现在每个系统定义的数据库和用户定义的数据库的sys构架中。
• 在SQL Server 2005中，可将GRANT、REVOKE和DENY权限应用于系统存储过程。

第28页：查看系统存储过程

查看系统存储过程

• 通过SQL Server 2005中的SQL ServerManagement Studio管理器，在对象资源管理器下，逐级展开【服务器】/【数据库】/用户数据库（如TeachingData）/【可编程性】/【存储过程】/【系统存储过程】，单击【系统存储过程】节点可以看到系统提供的所有存储过程的列表。

第29页：用户自定义存储过程

用户自定义存储过程

• 用户自定义存储过程是由用户为完成某一特定功能自行创建并存储在用户数据库中的存储过程。用户存储过程的名称在数据库中必须唯一，可以附带参数，完全由用户创建和维护。
• 在SQL Server 2005中，按编写语言的不同又将用户存储过程分为Transact-SQL存储过程和CLR存储过程。
  • Transact-SQL存储过程是指保存的Transact-SQL语句的集合，可以接受和返回用户提供的参数；
  • CLR存储过程是指对Microsoft.NET Framework CommonLanguage Runtime（CLR）方法的一个引用，可以接受并返回用户所提供的参数。它们在.NET Framework程序集中是作为类的公共静态方法实现的。

第30页：注意：用户自定义存储过程的名称

注意：用户自定义存储过程的名称

• 用户自定义存储过程的名称不要以“sp_”开头，因为用户自定义存储过程与系统存储过程重名时，用户自定义存储过程永远不会被调用。

第31页：扩展存储过程

扩展存储过程

• 扩展存储过程允许使用外部程序设计语言(例如C语言)创建自己的外部例程，它可以由SQL Server 2005的实例动态加载和运行。以动态链接DLL的形式存在，直接在SQLServer实例地址空间中运行，可以使用SQLServer 2005扩展存储过程API完成编程。扩展存储过程是“xp_”开头。

第32页：说明：当初引入扩展存储过程的目

说明：当初引入扩展存储过程的目

• 当初引入扩展存储过程的目的是为了通过外部程序语言来扩充SQL Server的功能和弥补T-SQL的不足。现在SQL Server 2005提供了完整的.NET Framework CLR集成功能后，提供了更健全和安全的替代方案来编写扩展存储过程，因此扩展存储过程的使用在减少。

第33页：用户自定义存储过程与用户自定义函数比较

用户自定义存储过程与用户自定义函数比较

• 存储过程可以通过output参数来返回数据，而用户定义函数只可以接收参数，不能通过output类型的参数返回值。
• 在存储过程中可以对任何数据及对象进行修改，包括新建或删除数据表、修改数据库设置等，而在用户定义函数中只能对数据进行修改，不能对数据库对象进行修改。
• 用户定义函数可以返回除了text、ntext、image、cursor、timestamp类型外的其他所有数据类型，而存储过程只能返回整数值。
• 用户定义函数执行方式灵活，可以通过execute方式执行，也可以用在表达式中，并以返回值的方式来取代函数名，而存储过程只能通过execute来执行。

第34页：存储过程的创建

存储过程的创建

• 利用对象资源管理器创建
• 使用T-SQL创建

第35页：利用对象资源管理器创建

利用对象资源管理器创建

• 在“对象资源管理器”中，逐级展开要创建存储过程的数据库/【可编程性】，右击【存储过程】，选择【新建存储过程】命令。
• 根据对存储过程的功能要求，在代码编辑窗口中输入代码
• 完成后单击【执行】按钮。

第36页：例3：利用对象资源管理器在Teac

例3：利用对象资源管理器在Teac

【例3】 利用对象资源管理器在TeachingData数据库中创建一个显示“计算机系”学生的基本信息的存储过程。 操作步骤： 在“对象资源管理器”中，逐级展开【数据库】/【TeachingData】/【可编程性】，右击【存储过程】，选择【新建存储过程】。 输入命令：CREATE PROCEDURE Stu_ProcASSELECT * FROM StuInfo WHERE Dept=’计算机系’ 单击【SQL编辑器】工具栏中的【执行】按钮

第37页：使用T-SQL创建

使用T-SQL创建

（2）使用T-SQL创建 语法格式如下：CREATE { PROC | PROCEDURE }procedure_name[ { @parameter data_type} [ = default ] [ [ OUTPUT ] ] [ ,...n ][ WITH { RECOMPLIE | ENCRYPTION | RECOMPLIE,ENCRYPTION } ]AS{  [ ...n ] }

第38页：参数说明

参数说明

procedure_name

指定存储过程的名称，存储过程的名称必须唯一。

@parameter

指定过程的输入和输出参数的名称，参数的名称必须以“@”开头，且符合标识符的命名规则。

data_type

指定参数的数据类型。

default

指定参数的默认值，它可以是一个常量或NULL。

OUTPUT

指定对应参数是一个输出参数。

RECOMPLIE

表明SQL Server不会缓冲该存储过程的执行计划，该过程将在执行时重新编译。

ENCRYPTION

表明SQL Server对该存储过程的源代码加密，用sp_helptext系统存储过程无法查看。

sql_statement

指定在存储过程中需要执行的Transact-SQL语句操作的集合。

第39页：例4：创建不带参数的存储过程

例4：创建不带参数的存储过程

【例4】 创建不带参数的存储过程。定义一个存储过程Sc_Proc，实现从ScoreInfo数据表中查询所有选修了00000001课程号的学生选课信息。USE TeachingDataGOCREATE PROCEDURE Sc_procASSELECT * FROM ScoreInfo WHERE CID='00000001'GO

第40页：存储过程的执行

存储过程的执行

• 在“对象资源管理器”窗口中，逐级展开【数据库】/【TeachingData】/【可编程性】/【存储过程】。
• 右击存储过程名，在弹出快捷菜单中选择【执行存储过程】选项，在打开的【执行过程】对话框中，单击【确定】按钮，即可执行该存储过程。

第41页：使用：参数说明

使用：参数说明

• 参数说明：schema_name：指定存储过程所属架构的名称。procedure_name：指定调用存储过程的名称。value：指定传递给各输入参数的值。

第42页：例5：调用存储过程Sc_Proc

例5：调用存储过程Sc_Proc

• 调用存储过程Sc_Proc，查询所有选修了00000001课程号的学生选课信息。EXECUTE Sc_Proc

第43页：例6：创建一个存储过程Sc_Pro

例6：创建一个存储过程Sc_Pro

【例6】 创建一个存储过程Sc_Proc1，实现从ScoreInfo表中查询某个学生某门课程的成绩。通过调用存储过程查看具体的结果。USE TeachingDataGOCREATE PROCEDURE Sc_proc1(@sid VARCHAR(8),@cid VARCHAR(8))ASSELECT SID,CID,ScoreFROM ScoreInfoWHERE SID=@sid AND CID=@cidGO

第44页：执行存储过程的Sc_Proc1

执行存储过程的Sc_Proc1

方法一

按位置传递参数值EXEC Sc_Proc1 '05000001', '00100002'

方法二

按参数名传递参数值EXEC Sc_Proc1 @cid='00100002', @sid='05000001'

第45页：例7：带默认值参数存储过程

例7：带默认值参数存储过程

【例7】 创建输入参数带默认值的存储过程。定义一个用于向CourseInfo表中插入记录的存储过程Add_proc，学分的默认值为3，课程类别默认为基础课。CREATE PROC Add_proc(@cid CHAR(8),@cname CHAR(20),@ccreditTINYINT=3,@cproperty CHAR(10)='基础课')ASINSERT INTO CourseInfoVALUES(@cid,@cname,@ccredit,@cproperty)

第46页：执行存储过程Add_proc

执行存储过程Add_proc

• 无缺省值的调用
• 缺省@cproperty参数的调用
• 缺省@ccredit参数的调用

第47页：无缺省值的调用

无缺省值的调用

无缺省值的调用 调用该存储过程插入一条课程号为00000004、课程名为大学计算机基础、学分为4课程记录，使用按位置传递参数值和按参数名传递参数值的调用语句均可。EXEC Add_proc '00000004','大学计算机基础',4,'基础课' 另外一种传递参数的方法是采用“参数=值”的形式，此时各个参数的顺序可以任意排列。例如上例也可以这样执行：EXEC Add_proc @cid= ‘00000006’, @ccredit =3,@cproperty=‘专业基础课’,@cname=‘企业管理'

第48页：缺省@cproperty参数的调用

缺省@cproperty参数的调用

• 调用该存储过程插入一条课程号为00000005、课程名为大学体育，2学分。调用语句为：EXEC Add_proc '00000005','大学体育', 2
• 或EXEC Add_proc @cid= '00000005', @cname='大学体育', @ccredit =2打开表CourseInfo可以看到在调用存储过程时，没有指定参数值时就自动使用相应的默认值，即这里的CProperty自动设为默认值“基础课”。

第49页：缺省@ccredit参数的调用

缺省@ccredit参数的调用

• 调用该存储过程插入一条课程号为00211003、课程名为软件工程，专业课。调用语句为：EXEC Add_proc @cid= '00211003', @cname='软件工程',@cproperty='专业课'
• 由于该调用缺省的中间参数，只能使用“参数=值”形式进行参数传递。

第50页：例8：创建带输出参数的存储过程

例8：创建带输出参数的存储过程

【例8】 创建带输出参数的存储过程。定义一个存储过程GetCredit_proc，实现从CourseInfo数据表中返回某门课程的学分。CREATE PROC GetCredit_proc (@cid VARCHAR(8),@ccredit TINYINT OUTPUT)ASSELECT @ccredit = Ccredit FROM CourseInfoWHERE CID =@cidGO 在该例中@cid为输入参数，用于传入课程号；@ccredit为输出参数，用于返回学分，请注意其后面的OUTPUT表明此参数为输出参数。

第51页：执行该存储过程

执行该存储过程

• 执行该存储过程，来查询课程号CID为“00200002”的课程学分：DECLARE @xf INTEXEC GetCredit_proc '00200002', @xf OUTPUTPRINT @xf

第52页：存储过程的修改

存储过程的修改

• 在“对象资源管理器”窗口中，逐级展开【数据库】/数据库名（如“TeachingData”）/【可编程性】/【存储过程】。
• 在存储过程列表中，右击要修改的存储过程名，在弹出的快捷菜单中选择【修改】选项，在SQL命令窗口中编辑T-SQL代码。
• 完成编辑后，单击标准工具栏中的命令按钮

第53页：用ALTER PROCEDURE修改

用ALTER PROCEDURE修改

（2）用T-SQL命令修改语法格式：ALTER { PROC | PROCEDURE }procedure_name[ { @parameterdata_type} [ = default ] [ [ OUT [ PUT ] ] [ ,...n ][ WITH { RECOMPLIE | ENCRYPTION | RECOMPLIE,ENCRYPTION } ]AS{  [ ...n ] }注意： 如果存储过程在创建时使用WITH ENCRYPTION或WITHRECOMPILE等选项，那么只有在ALTER PROCEDURE中也包含这些选项时，这些选项才有效。

第54页：触发器1、什么是触发器？

触发器1、什么是触发器？

• 触发器是数据库服务器中发生事件时自动执行的一种特殊的存储过程，为数据库提供了有效的监控和处理机制，确保了数据的完整性。
• 触发器基于一个表创建，但可以针对多个表进行操作，所以触发器常被用来实现复杂的商业规则。
• 在SQL Server中，一张表可以有多个触发器，用户可以根据数据操作语句对触发器进行设置。

第55页：触发器的优点：①强化完整性约束

触发器的优点：①强化完整性约束

2、触发器的优点：①强化完整性约束。触发器能够实现比CHECK语句更为复杂的约束。与CHECK相比，触发器可以引用其它表中的列，更适合在大型数据库管理系统中用来约束数据的完整性。②实现表的级联操作。触发器可以侦测数据库内的操作，并自动的级联影响整个数据库的各项内容。③可以禁止和回滚违反完整性约束的更改。触发器可以侦测数据库内的操作，从而可以取消数据库未经许可的更新操作，并返回自定义的错误信息。使数据库的修改、更新操作更安全，数据库的运行也更稳定。

第56页：触发器的类型

触发器的类型

• SQL Server 2005提供了两种类型的触发器：
  • DML触发器
  • DDL触发器

第57页：DML触发器

DML触发器

• DML触发器是在执行数据操作语句（DML）时被执行的触发器。
• DML事件包括指定表或者视图中修改数据的INSERT语句、UPDATE语句和DELETE语句。
• DML触发器中可以查询其他表，可以包含复杂的Transact-SQL语句。
• 可以将触发器和触发它的语句作为作为整体被看作一个事务，如果检测到错误时，整个事务即自动回滚。

第58页：DML触发器

DML触发器

根据操作事件的不同可以分为以下两种类型：①AFTER触发器②INSERTED OF触发器

第59页：①AFTER触发器

①AFTER触发器

• AFTER触发器是在执行了INSERT、UPDATE和DELETE语句操作之后才执行的触发器。
• 该触发器要求只有执行某一操作之后，触发器才被触发，且只能在表上定义。
• 可以针对表的同一操作定义多个触发器，还可以使用系统存储过程sp_settriggerorder定义触发器触发的顺序。

第60页：②INSTEAD OF触发器

②INSTEAD OF触发器

• INSERTED OF触发器是在触发事件发生前被调用，即INSERTED OF触发器执行时并不执行其发出触发事件的操作语句（INSERT、UPDATE或DELETE），而仅仅是执行触发器中的语句。
• 可为带有一个或多个基表的视图定义INSERTEDOF触发器，而这些触发器能够扩展视图可支持的更新类型。

原文写作「INSERTED OF触发器」，按 SQL Server 标准术语应为「INSTEAD OF触发器」，正文保留原文未改动。

第61页：DDL触发器

DDL触发器

DDL触发器

SQL Server 2005的新增功能，也是一种特殊的存储过程，它与DML中AFTER触发器类似。当服务器或数据库中发生数据定义语言(DDL)事件时将调用这些触发器。

与DML不同的

，它相应的触发事件是由数据定义语句CREATE、ALTER或DROP操作引发的。DDL触发器通常用于执行数据库的管理任务，如调节和审计数据库运转等。

由于DDL触发器

SQL Server 2005引入的新概念，主要应用于数据审计等工作，不属于数据库基本使用范围，

第62页：DML触发器

DML触发器

4、DML触发器的创建在创建触发器前需要注意以下几个问题：①CREATE TRIGGER语句必须是批处理的第一个语句，并且只能应用在一张表上。②创建触发器的权限默认分配给表的所有者，且不能把该权限传给其它用户。③触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。④在同一条CREATE TRIGGER语句中，可以为多种用户操作（如INSERT和UPDATE）定义相同的触发器操作。⑤如果一个表的外码设置为DELETE/UPDATE的级联操作，则不能再为该表定义INSTEAD OF DELETE/UPDATE触发器。⑥在DML触发器中不允许使用的T-SQL语句有：CREATE DATABASE、ALTER DATABASE、DROP DATABASE、LOAD DATABASE、LOADLOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。

第63页：使用对象资源管理器创建DML触发器

使用对象资源管理器创建DML触发器

（1）使用对象资源管理器创建DML触发器 若在TeachingData数据库的StuInfo表中创建一个触发器Stu_t1，旨在进行INSERT操作时给出提示信息。操作步骤： 在“对象资源管理器”窗口中，逐级展开【数据库】/【TeachingData】/【表】/【StuInfo表】,右击【触发器】，选择【新建触发器】。 在代码编辑窗口中的相应位置填入创建触发器的T-SQL语句。Stu_t1代码如下：CREATE TRIGGER Stu_t1ON StuInfoFOR INSERTASPRINT ‘欢迎新同学！’ 单击工具栏上的【执行】按钮。

第64页：用T-SQL语句创建DML触发器

用T-SQL语句创建DML触发器

（2）用T-SQL语句创建DML触发器 语法格式如下：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ][ UPDATE ] [ , ] [ DELETE ] }AS[ IF UPDATE(column)[ { AND | OR } UPDATE(column)]sql_statements}

第65页：参数说明

参数说明

参数说明: trigger_name：指定触发器的名字，其名字在当前数据库中必须是惟一，不能以#或##开头。 table| view：执行DML触发器的表或视图，若是视图只能被INSTEAD OF触发器引用。 WITH ENCRYPTION：表示对包含有CREATE TRIGGER文本的syscomments表进行加密； FOR | AFTER | INSTEAD OF：指定触发器的类型，FOR和AFTER等价。 [DELETE] [,] [INSERT] [,] [UPDATE]：指定激活触发器的数据操作，至少要指明一个选项。在触发器的定义中，三者的顺序不受限制，且各选项要用逗号隔开。 sql_statement：指定触发器被触发后将执行的操作，它包括触发器执行的条件和动作。触发器条件是指除了引起触发器执行的操作外的附加条件；触发器动作是指当用户执行激发触发器的某种操作并满足触发器的附加条件时，触发器所执行的操作。 IF UPDATE(column)：指定对表内某列增加或修改内容时触发器才起作用，它可以指定两个以上的列。

第66页：例9：为数据表StuInfo创建一

例9：为数据表StuInfo创建一

【例9】 为数据表StuInfo创建一个触发器Stu_t2，实现在更新操作中禁止修改学生姓名。CREATE TRIGGER Stu_t2 on StuInfoFOR UPDATEASIF UPDATE(Sname)BEGINPRINT '学生姓名不允许修改！'ROLLBACKEND

第67页：例10：创建一个触发器Tch_t1

例10：创建一个触发器Tch_t1

【例10】 创建一个触发器Tch_t1，实现禁止删除或更改工号为11010001的教师。CREATE TRIGGER Tea_t1ON tchinfoFOR UPDATE,DELETEASIF ((SELECT TID FROM Deleted)='11010001')BEGINPRINT '不允许删除该教师，操作失败！'ROLLBACKEND

第68页：DM触发器的工作原理

DM触发器的工作原理

• 两个特殊的临时表，即Inserted表和Deleted表。这两个表是在DML触发器执行时产生的临时表，驻留在内存中，它的结构和触发器所在的表的结构相同，由SQL Server 2005自动创建和管理这些表。用户可以使用这两个表中的数据，但不能直接对表中的数据进行修改。(1)Inserted表
• Inserted表用于存储被INSERT和UPDATE语句操作所影响的新数据行的副本。
• 在INSERT操作或UPDATE操作时，新的数据行被添加到基本表中，同时这些数据行的副本被添加到Inserted临时表中。(2)Deleted表
• Deleted表用于存储被DELETE和UPDATE语句操作所影响的旧数据行。
• 在执行DELETE或UPDATE操作时，指定的数据行从基本表中删除，并转移到Deleted表中。在基本表和Deleted表中一般不会出现相同的行。

第69页：Inserted表

Inserted表

• Inserted表和Deleted表存在于内存中，仅仅在触发器执行时存在，它们在某一特定时间和某一特定表相关。一旦某个触发器结束执行，相应的两个表内的数据都会消失。如果想将这些表内数据的永久保存，需要在触发器把这些表中的数据复制到一个永久表中。在对具有触发器的表进行操作时，其执行过程如下:
  • 执行INSERT操作时，插入到触发器表中的新行也被插入到Inserted表中。
  • 执行DELETE操作时，从触发器表中删除的行被插入到Deleted表中。
  • 执行UPDATE操作时，先从触发器表中删除旧行，然后再插入新行。其中删除的旧行被插入到Deleted表中，插入的新行插入到Inserted表中。
  • 在设置触发器条件时，应使用激发触发器的操作相应的Inserted或Deleted表。尽管在测试INSERT时引用删除的表或在测试DELETE时引用插入的表不会导致任何错误，但在这些情况下，这些触发器测试表将不包含任何行。

第70页：创建DDL触发器

创建DDL触发器

5、创建DDL触发器 DDL触发器与标准触发器一样，在响应事件时执行存储过程。 与标准触发器不同的是，它们并不响应对表或者视图的UPDATE、INSERT或者DELETE语句时执行存储过程。它们主要在响应数据定义语言（DDL）语句时执行存储过程，主要包括CREATE、ALTER、DROP、GRANT、DENY、REVOKE等语句。 它可以用于在数据库中执行管理任务。

第71页：语法格式

语法格式

CREATE TRIGGERtrigger_nameON { ALL SERVER | DATABASE }[ WITH ENCRYPTION]{{ FOR | AFTER }{ event_type|event_group}[ , ...n]AS{sql_statements[ , ...n]}

第72页：参数说明

参数说明

trigger_name

指定DDL触发器的名称。

ALL SERVER

将DDL或登录触发器的的作用域应用当前服务器。

DATABASE

将DDL触发器的的作用域应用当前数据库。

event_type

执行之后将导致激发DDL触发器的Transact-SQL语言事件的名称。

event_group

预定义的Transact-SQL语言事件分组的名称。

sql_statements

触发后的判断条件和操作。

第73页：例11：创建DDL触发器DB_tr

例11：创建DDL触发器DB_tr

【例11】 创建DDL触发器DB_tr，其功能为：当数据库中发生DROP TABLE事件或ALTER TABLE事件都将触发DDL触发器。CREATE TRIGGER DB_trON DATABASEFOR DROP_TABLE,ALTER_TABLEASPRINT‘无法删除或修改本数据库中的表！'ROLLBACKGO

第74页：例12：创建一个触发器Cou_t1

例12：创建一个触发器Cou_t1

【例12】 创建一个触发器Cou_t1，当向CourseInfo表插入课程记录时，先检查是否与该课程同名的课程已经存在，以避免课程的混淆。CREATE TRIGGER Cou_t1ON CourseInfoFOR INSERT,UPDATEASIF (SELECT COUNT(Cname) FROM InsertedWHERE Cname IN (SELECT Cname FROM CourseInfo ))>1BEGINPRINT('已经有同名课程存在，不能插入或修改！')ROLLBACKENDGO

第75页：触发器的管理

触发器的管理

• 逐级展开“对象资源管理器”窗口中的【数据库】/【TeachingData】/【表】/【StuInfo表】/【触发器】。
• 右击要查看的触发器（如Stu_t1）：
  • 选择【查看依赖关系】选项，则显示依赖该触发器的对象和该触发器依赖的其他数据库对象的名称。
  • 选择【修改】选项，在右窗格中显示要修改的触发器的代码，用户可以直接修改该触发器的T-SQL语句。最后单击【执行】按钮，可以执行修改后的触发器。
  • 选择【删除】选项，在弹出的【删除对象】对话框中显示了当前要删除触发器的相关信息，如果确认删除，则单击【确定】按钮，系统将删除触发器。

第76页：利用T-SQL命令管理触发器

利用T-SQL命令管理触发器

• 查看触发器
  • SQL Server2005使用系统存储过程sp_help、sp_helptext和sp_depends和系统表sysobjects浏览触发器的相关信息，也可以使用sp_rename系统存储过程来为触发器更名。

第77页：①sp_help

①sp_help

• 系统存储过程sp_help用于查看触发器的名称等一般信息，其语法格式为：
• EXEC sp_helptrigger_name

第78页：sp_helptext查看触发器

sp_helptext查看触发器

• 系统存储过程sp_helptext用于查看触发器的正文信息，其语法格式为：EXEC sp_helptexttrigger_name

第79页：sp_depends查看依赖关系

sp_depends查看依赖关系

③sp_depends 系统存储过程sp_ depends用于查看触发器所引用的表或指定表涉及的所有触发器。其语法格式为：EXEC sp_dependstrigger_name 或者：EXEC sp_ dependstable_name

第80页：用系统表sysobjects查看

用系统表sysobjects查看

④使用系统表sysobjectsUSE TeachingDataSELECT * FROM sysobjectsWHERE type='TR'

第81页：使用：语法格式

使用：语法格式

使用ALTER TRIGGER语句修改触发器 语法格式：ALTER TRIGGERtrigger_nameON (table| view)[ WITH ENCRYPTION ]( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [,] [ INSERT ] [,][ UPDATE ] }[ NOT FOR REPLICATION ]AS{ IF UPDATE(column)[ { AND | OR } UPDATE(column)]sql_statements}

第82页：使用T-SQL语句禁用和启用触发器

使用T-SQL语句禁用和启用触发器

使用T-SQL语句禁用和启用触发器 禁用触发器DISABLE TRIGGER{[schema.] trigger_name[,…n]|ALL} ON{object_name|DATABASE|ALL SERVER}[ ] 启用触发器ENABLE TRIGGER{[schema.] trigger_name[,…n]|ALL} ON{object_name|DATABASE|ALL SERVER}[ ]

第83页：参数说明

参数说明

Schema

触发器所属架构名。

Trigger_name

要启用或禁用的触发器名

ALL

指所有在ON子句作用域中定义的所有触发器

Object_name

要执行的DML触发器Trigger_name的表或视图的名称

DATABASE

对于DDL触发器Trigger_name指示所创建或修改的trigger_name将在数据库作用域内执行。

ALL SERVER

对DDL触发器Trigger_name指示所创建或修改的将在服务器作用域内执行。

第84页：使用T-SQL语句删除触发器

使用T-SQL语句删除触发器

• 触发器使用之后可以删除，但是只有触发器的所有者才有权删除触发器。语法格式：DROP TRIGGERtrigger_name[ , ...n]
• 若删除DDL触发器，其DROP TRIGGER语句的语法格式如下：DROP TRIGGERtrigger_name[ , ...n]ON { ALL SERVER | DATABASE }
• 其中ALL SERVER | DATABASE只是DDL触发器的作用域应用对象，如果在创建或修改触发器时指定了ALL SERVER，则删除时必须指定ALL SERVER；如果在创建或修改触发器时指定了DATABASE，则删除时必须指定DATABASE。
• 说明：当删除一个数据表时，SQL Server会自动删除与该数据表相关的触发器。

第85页：小结：事务

小结：事务

小结 事务 ACID特性（原子性、一致性、隔离性、持久性） 事务的控制语句（BEGIN TRANSACTION、COMMITTRANSACTION、ROLLBACK TRANSACTION、SAVETRANSACTION） 存储过程 创建(CREATE PROC)、 执行(EXEC) 管理(ALTER, sp_helptext ,DROP PROC) 触发器 创建(CREATE TRIGGER) 管理(sp_help , sp_helptext, sp_depends , sysobjects )

第86页：上机实验

上机实验

1.完成PPT中所有例题的操作。2.创建一个触发器CID_Update，要求当表（courseInfo）中的CID字段值被修改时，该字段在另一张表（ScoreInfo）中的对应值也做相应的修改。3.创建一个触发器stu_count，要求当表StuInfo中相同年级中的任一班级的人数达到45人时，不允许继续在这个班级插入新的记录。4.创建一个触发器Tch_t2，实现禁止删除表TchInfo中职称为教授的记录的功能。5.创建一个存储过程sc_unpass，其功能是输入某一门学课的课程名后，即可查看某一门课程不及格学生的学号、姓名、班级及任课教师。6.创建一个存储过程tch_dept，其功能是输入某一个院系（Dept）名后，即可查看该系中教师的姓名、性别、年龄和职称。7.创建一个存储过程pass_state，其功能是输入某一个学生姓名和课程名，查看该学生成绩，如果其成绩低于60分，则在一列中显示“很遗憾！”+学生姓名+课程名+“未及格”；如果其成绩大于等于60分，则在一列中显示“很高兴，”+学生姓名+课程名+“已合格”。