1、第9章 .存储过程和触发器,9,本章要点,存储过程概述 创建和执行存储过程 修改和删除存储过程 参数化存储过程 存储过程中的错误处理 触发器概述 管理触发器 触发器的工作原理 Instead Of 触发器 触发器的应用 触发器的高级应用,9.1 存储过程概述,存储过程是预先编译好的一组Transact-SQL语句，这些语句作为一个单元存储。SQL Server中的存储过程与其他编程语言中的过程类似，可以接受输入参数，并以输出参数的形式将单个值或多个值返回给调用过程或批处理。存储过程中的语句，包含执行数据库操作以及调用其他过程的语句，向调用过程或批处理返回状态值，返回成功信息或错误码。 存储过程
2、在被创建时，会被进行语法分析，判断语法的准确性。如果没有语法问题，存储过程的名称会被保存到sysobjects系统表中，存储过程的内容保存到syscomments系统表中。如果发现语法错误，就不会创建存储过程。存储过程在第一次被执行时，会被优化编译并且保存在高速缓冲中。,存储过程的种类,局部存储过程 局部存储过程由数据库用户创建。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 系统存储过程 在SQL Server中很多的管理活动都可以通过系统存储过程执行，系统存储过程名称有前缀“sp_”，强烈建议不要以“sp_”为前缀创建任何存储过程。“sp_”前缀是SQL Serv
3、er用来指定系统存储过程的。自定义的名称可能会与以后的某些系统存储过程发生冲突。如果应用程序引用了不符合架构的名称，而自定义的存储过程名称与系统存储过程名称相冲突，则该名称将绑定到系统存储过程而非自定义的存储过程，这将导致应用程序中断。 临时存储过程 临时存储过程可以在过程名称前添加“#”和“#”前缀的方法进行创建。“#”表示本地临时存储过程，“#”表示全局临时存储过程。SQL Server关闭后，这些存储过程将不再存在。局部临时存储过程在创建它的会话中可用，全局临时存储过程在所有的会话中可用。,CLR存储过程 在SQL Server 2005中，可以用Microsoft.Net Framew
4、ork支持的公共语言运行库（Common Language Runtime，CLR）的编程语言创建存储过程。这种存储过程的用法类似于Transact-SQL用户自定义存储过程的用法。它们能够利用由CLR提供的众多编程模型的数据库对象，返回表格形式的结果、整数返回值或输出参数，并可以修改数据和某些数据库对象。 扩展存储过程 在SQL Server环境外部执行的DLL称为扩展存储过程。扩展存储过程名称有前缀“xp_”，可将参数传递给扩展存储过程。扩展存储过程可返回结果，也可返回状态。,存储过程的优势,模块化程序设计 用户在创建存储过程后便可以将其保存在数据库中，以后可以反复调用，并进行后期的修改和
5、维护，提高开发效率和开发质量。 提高执行速度 当需要执行大量Transact-SQL代码时，存储过程的执行速度要比大量Transact-SQL代码的执行速度快。因为存储过程会被进行分析和优化，在执行时使用的是在高速缓冲中的内容，而客户端的Transact-SQL语句每次要被发送、编译和优化，效率较低。,减少网络流量 当需要执行大量Transact-SQL代码时，对于存储过程，只有调用命令和执行的结果在网络中传输，用户端不需要在网络中传输大量的代码，也不需要将数据库中的数据传输到本地进行计算，所以使用存储过程可以减少网络中的数据流量。 提供安全机制 用户可以被授予执行存储过程的权限，即使用户没有
6、存储过程中引用到的表或视图的权限。既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。,9.2 创建和执行存储过程,创建存储过程 可使用CREATE PROCEDURE语句在当前数据库中创建存储过程，其中可以将PROCEDURE简写成PROC。存储过程名称需要符合标识符规范，并且对于数据库中的对象名是唯一的，存储过程名不可与已经存在的存储过程重名，也不可与已经存在的表和视图等其他数据库对象重名。如果创建局部临时过程，可以在存储过程名前面加前缀“#”；如果要创建全局临时过程，可以在存储过程名前面加前缀“#”。完整的名称（包括“
7、#”或“#”）不能超过128个字符,创建存储过程,语法 CREATE PROCEDURE 存储过程名(参数定义部分) AS(主体部分) 使用SQL Server Management Studio中的对象资源管理器创建Transact-SQL存储过程,调用存储过程,可使用EXECUTE语句执行存储过程，其中可以将EXECUTE简写成EXEC。执行存储过程的用户必须被授予该存储过程上的EXECUTE权限。 语法 EXECUTE 存储过程名(参定义部分) 例如： USE Northwind; GO EXEC GetEmployeesLastName= NDavolio, FirstName=NNa
8、ncy; GO,9.3 修改存储过程,使用ALTER PROCEDURE语句可以修改已创建存储过程的参数和内容。 可以先删除再重新创建存储过程。 如果先删除再重新创建存储过程，与该存储过程相关的权限都会被删除。如果使用修改存储过程，与该存储过程的相关权限都会被保留。 语法： ALTER PROCEDURE 存储过程名(参数定义部分) AS(主体部分),修改存储过程,ALTER PROCEDURE dbo.GetEmployees - Add the parameters for the stored procedure hereLastName nvarchar(50) = NULL, . F
9、irstName nvarchar(50) = NULL . AS . BEGIN . - SET NOCOUNT ON added to prevent extra result sets from . - interfering with SELECT statements. . SET NOCOUNT ON; . . - Insert statements for procedure here . SELECT LastName, FirstName, Title . FROM Employees . WHERE LastName like LastName + % . OR First
10、Name like FirstName + %; . END,删除存储过程,使用DROP PROCEDURE语句可以删除已创建存储过程 语法： DROP PROCEDURE 存储过程名 例如： USE Northwind; GO DROP PROC GetEmployees GO,9.4 参数化存储过程,使用参数可以在存储过程和调用存储过程的应用程序之间交换数据。 输入参数可以将外部数据传递到存储过程的内部，存储过程也可以通过输出参数将数据返回到外部。,带输入参数的存储过程,每个存储过程参数都必须用唯一的名称进行定义。 与标准的Transact-SQL变量相同，存储过程名称必须以单个“”字符开
11、头，并且必须遵从对象标识符规则。 可在存储过程中使用参数名称以获得参数值并更改它。 存储过程中的参数要定义数据类型，这与表中的字段的数据类型几乎一样。,例如 . ALTER PROCEDURE dbo.GetEmployees . - Add the parameters for the stored procedure here . LastName nvarchar(50) = NULL, . FirstName nvarchar(50) = NULL . AS 执行该存储过程，得到LastName为Davolio，FirstName为Nancy的雇员数据。 EXEC GetEmploye
12、esLastName= NDavolio, FirstName=NNancy;,指定存储过程参数的默认值,通过为可选参数指定默认值，可创建带有可选参数的存储过程。 执行该存储过程时，如果未指定其他值，则使用默认值。如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误，因此指定默认值是比较重要的。 如果不能为参数指定合适的默认值，则可以指定NULL作为参数的默认值，并在未提供参数值而执行存储过程的情况下，进行合适的处理。,例如： EXEC GetEmployeesLastName=NULL, FirstName=NULL; 在执行该存储过程时
13、，以下代码的执行结果是一样的。 EXEC GetEmployees;,带输出参数的存储过程,如果存储过程为参数指定OUTPUT关键字，存储过程退出时可以将保存在参数变量中的值返回给调用函数，并且调用程序也必须指定OUTPUT关键字。 例如： CREATE PROC my_GetCompanyNameSupplierID Integer,CompanyName nvarchar(40) OUTPUT-输出参数公司名称 ASSELECT CompanyName = CompanyName FROM SuppliersWHERE SupplierID = SupplierID GO,执行该存储过程
14、DECLARE CName NVarChar(40) EXEC my_GetCompanyName 1,CName OUTPUT PRINT CName GO 存储过程的OUTPUT参数，也可以指定输入值，这样调用程序可以传递给存储过程一个值，存储过程可以接收这个值，修改这个值，然后返回给调用程序。 如果执行时指定了OUTPUT关键字，而在存储过程中没有为参数定义OUTPUT关键字，那么在调用时会得到一个错误信息。,在执行带有OUTPUT参数的存储过程时，没有指定OUTPUT关键字，执行后参数的值不会被修改 DECLARE CName NVarChar(40) Select CName = n
15、o company name EXEC my_GetCompanyName 1,CName 没有指定OUTPUT关键字 PRINT CName GO 最后CName中的内容仍然是no company name。,存储过程中的错误处理,在存储过程对错误进行处理时，可以使用全局变量ERROR。 ERROR返回最后执行的Transact-SQL语句的错误代码。ERROR的类型为integer，在master.dbo.sysmessages系统表中可以查看与ERROR错误代码对应的错误的文本信息。 ERROR在每一个Transact-SQL语句执行后都会被重置，如果最后的语句执行成功，则返回0；如果最
16、后执行的语句产生错误，则返回错误号。 在处理中可使用两种方法，语句执行后，马上检查ERROR，或者在语句执行完后将ERROR保存的一个整型变量中，供以后错误处理中使用。 在SQL Server中，批处理、存储过程和触发器唯一能使用的错误信息就是ERROR。同时ERROR只由错误产生，不由警告产生，因此，批处理、存储过程和触发器对警告没有可见性。,错误处理,例如： CREATE PROC my_UpdatePriceProductID Integer,UnitPrice Money ASUPDATE Products SET UnitPrice = UnitPriceWHERE ProductI
17、D = ProductID 执行“EXEC my_UpdatePrice 1,-10”语句时，显示错误信息： 服务器: 消息 547，级别 16，状态 1，过程 my_UpdatePrice，行 5 UPDATE 语句与 COLUMN CHECK 约束 CK_Products_UnitPrice 冲突。该冲突发生于数据库 Northwind，表 Products, column UnitPrice。 语句已终止。,将上面存储过程改为： CREATE PROC my_UpdatePriceProductID Integer,UnitPrice Money ASUPDATE Products SE
18、T UnitPrice = UnitPriceWHERE ProductID = ProductIDIF ERROR = 547 Print 产品单价必须大于等于0 通过执行“EXEC my_UpdatePrice 1,-10”语句时，显示错误信息后，显示提示信息“产品单价必须大于等于0”。程序可以根据错误号做出相应的错误处理。,用户自定义错误信息,SQL Server在遇到问题时，根据严重级别，将把sysmessages系统表中的消息写入SQL Server错误日志和操作系统的应用程序日志，或者将消息发送到客户端。可以使用RAISERROR语句手工生成错误信息。 RAISERROR语句提供集
19、中错误信息管理。RAISERROR可以从sysmessages表检索现有条目，也可以使用硬编码（用户定义）消息。 消息可以包括C语言printf样式的格式字符串，该格式字符串可在运行时由RAISERROR指定的参数填充。这条消息在定义后就作为服务器错误信息发送回客户端。用户定义错误信息的错误号应大于50 000。,显示错误信息 CREATE PROC my_UpdatePrice1ProductID Integer,UnitPrice Money ASIF UnitPrice = 0 UPDATE Products SET UnitPrice = UnitPriceWHERE ProductI
20、D = ProductIDELSERAISERROR (产品单价必须大于等于0,16,1) GO 执行“EXEC my_UpdatePrice1 1,-10”语句时，显示错误信息： 服务器: 消息 50000，级别 16，状态 1，过程 my_UpdatePrice1，行 10 产品单价必须大于等于0,格式化错误信息,格式化错误信息，消息字符串可以包含替代变量和参量。这与C语言中的printf功能相似。 CREATE PROC my_UpdatePrice2ProductID Integer,UnitPrice Money ASIF UnitPrice = 0 UPDATE Products
21、SET UnitPrice = UnitPriceWHERE ProductID = ProductIDELSERAISERROR (产品(ProductID:%d)单价为必须大于等于0,16,1,ProductID) GO 执行“EXEC my_UpdatePrice2 1,-10”语句时，显示错误信息： 服务器: 消息 50000，级别 16，状态 1，过程 my_UpdatePrice2，行 10 产品(ProductID:1)单价为必须大于等于0,触发器概述,在数据库管理系统中，保持数据的完整性是一个很重要的事情。我们用数据类型、约束、主键或唯一索引等方法来保持数据的完整性，但是，对于
22、复杂数据的完整性问题，如需要根据其他表的数据来决定的用户自定义完整性，则触发器可以作为解决这种完整性的一种方法。 触发器是一种特殊的存储过程，当数据表或视图的数据发生改变，一般来说，当数据表或视图发生插入、删除或修改时，触发器将被唤醒执行。通过触发器可以做许多的事情，维护通过约束不能实现的复杂的数据完整性。当数据表的数据被修改后，自动执行需要的操作。 触发器的优点在于它能自动执行，而不管什么原因引起的数据修改，都会被触发。触发器和启动它的语句作为单个事务处理，可以从触发器中回滚事务，如果发生严重错误，整个事务会自动回滚。 SQL Server提供两种类型的触发器：AFTER触发器和INSTEA
23、D OF触发器。AFTER触发器作用在表上，晚于约束处理；INSTEAD OF触发器作用在表或视图上，早于约束处理。对于AFTER触发器，如果一个表同时具有约束和触发器，在进行数据操作时，首先进行约束检查，检查成功后再激活触发器；如果检查失败将中止数据操作，并且无法激活触发器。INSTEAD OF触发器将替代数据操作语言，执行INSTEAD OF触发器中的代码。,管理触发器,创建触发器 使用CREATE TRIGGER语句在当前数据库中创建触发器，创建触发器前应考虑下列问题： （1）CREATE TRIGGER语句必须是批处理中的第一个语句。将该批处理中随后的其他所有语句解释为CREATE T
24、RIGGER语句定义的一部分。 （2）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 （3）触发器是数据库对象，其名称必须遵循标识符的命名规则。 （4）虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 （5）虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。 （6）在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。 （7）虽然TRUNCATE TABLE语句类似于没有WHERE子句（用于删除行）的DELETE语句，但它并不会引发DELETE触发器，因为TRUN
25、CATE TABLE语句没有记录。 （8）WRITETEXT语句不会引发INSERT或UPDATE触发器。 创建触发器时，必须指定触发器名、定义触发器的表名或视图名以及有效选项INSERT、UPDATE或DELETE。有效选项可以选择一个或多个，但至少选择一个。,创建触发器,创建触发器的语法： CREATE TRIGGER 触发器名 ON 表或视图名 For (有效选项) AS(主体部分) 例如： CREATE TRIGGER Shippers_Change ON Shippers FOR INSERT ASPRINT 运输公司表插入了新记录 当执行以下语句时，触发器将激活。 INSERT I
26、NTO Shippers values(飞鸟速递, 0519-12345678),删除触发器,删除触发器 当不再需要某个触发器时，可将其删除。当触发器被删除时，它所基于的表和数据并不受影响，但删除表将自动删除其所有的触发器。删除触发器的权限默认授予在该触发器所在表的所有者。 删除触发器的语法： DROP TRIGGER 触发器名 例如： USE Northwind GO DROP TRIGGER Shippers_Change GO,触发器的工作原理,SQL Server将数据写入数据库之前，先校验规则和默认值，对数据信息预先过滤，避免某些数据项会影响到数据库的完整性，造成数据库中数据的冗余。
27、 约束在INSTEAD OF触发执行之后与AFTER触发器执行之前被检查。如果与约束冲突，INSTEAD OF触发器将被回滚，并且AFTER触发器不会被执行。AFTER触发器进行后过滤，它在数据通过了规则、默认值之后执行；如果触发器处理中失败，将拒绝修改数据，并返回错误信息。,inserted表和deleted表,激活触发器的语句所执行的操作不同，将会创建一个或者两个临时表inserted表和deleted表。 表9-1说明了随着触发器的类型不同，inserted表和deleted表的创建情况。 触发器类型inserted表deleted表INSERT插入的记录不创建UPDATE修改后的记录修
28、改前的记录DELETE不创建删除的记录inserted表和deleted表只能够被创建它们的触发器引用，inserted表和deleted表的作用范围仅限于该触发器。 当记录插入表中时，相应的插入触发器创建inserted表，该表映射了与该触发器对应的表的列结构。例如，往Shippers表中插入了一条记录，Shippers表的插入触发器使用Shippers的列结构创建inserted表，插入Shippers表中的每个记录，都会相应地插入inserted表中。,当删除记录时，被删除的记录也会被复制到由删除触发器创建的deleted表中。与inserted表相同，deleted表的列结构与删除触发
29、器对应的表的列结构相同。 当用UPDATE修改数据时，激活更新触发器。更新触发器将同时创建inserted表和deleted表，这两个表和该触发器对应的表有着同样的列结构，其中deleted表中的数据是被修改的记录的修改前的数据，inserted表中的数据是修改后的记录。,例如： CREATE TRIGGER Shippers_Change ON Shippers FOR INSERT,UPDATE,DELETE ASSELECT * FROM deletedSELECT * FROM inserted GO 当执行下列语句时，deleted表为空，inserted表中的内容为插入的记录 IN
30、SERT INTO Shippers values(飞鸟速递,0519-12345678) 执行下列语句时，deleted表中的内容为修改前的记录，运输公司的名称为“飞鸟速递”，inserted表中的内容为修改后的记录，运输公司的名称为“快马速递” UPDATE Shippers SET CompanyName = 快马速递 WHERE ShipperID = 5 执行下列语句，deleted表中的内容为删除的记录，inserted表为空 DELETE FROM Shippers WHERE ShipperID = 5,触发器的类型,在创建触发器时，SQL Server提供了两种选择： INS
31、TEAD OF触发器。它的执行替代了通常触发器所起的作用，并且它可以定义在视图上。视图可以是基于多个基本表创建的。 AFTER触发器。它是在INSERT、UPDATE或DELETE等语句执行之后执行的。指定AFTER关键字和指定FOR关键字是一样的，FOR选择是早期版本中唯一可以使用的选项。AFTER触发器只能够定义在表上。,触发器限制,CREATE TRIGGER必须是批处理中的第一条语句，而且只能够应用于一个表中。触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象。 如果一个表的外键在DELETE/UPDATE操作上定义了级联，则不能在该表上定义INSTEAD OF D
32、ELETE/UPDATE触发器。例如，如果为Northwind数据库的Order Details表定义了以OrderID为外键（Orders的主键）的级联更新或级联删除，就不能为Order Details表定义INSTEAD OF类型的更新或删除触发器。 在触发器中可以指定任意的SET语句。所设置的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。 当触发器激活时，将向调用应用程序返回结果。若要避免因触发器而向应用程序返回结果，不要包含返回结果的SELECT语句，也不要在触发器中进行变量赋值。如果需要在触发器中进行变量赋值，可以在触发器的开始使用SET NOCOUNT语句，避
33、免返回任何结果集。 DELETE删除触发器不能被TRUNCATE TABLE语句激活，所以在TRUNCATE TABLE删除表记录时，不会激活DELETE触发器。 在触发器中，有些Transact-SQL指令不能被使用。,触发器的嵌套调用,如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，这些触发器就是嵌套触发器（nested triggers）。触发器可嵌套至32层，并且可以设置嵌套触发器服务器配置选项，控制是否允许触发器嵌套。 如果允许使用嵌套触发器，链中的一个触发器开始一个无限循环，则超出嵌套级别时，触发器将终止。 可使用嵌套触发器执行一些有用的日常工作，
34、如保存前一触发器所影响行的一个备份。 例如，在没有外键约束的情况下，可以在Orders表上创建一个触发器，当删除Orders表中的记录时，该触发器将删除Order Details表中相同的OrderID的记录，同时在Order Details表上建立删除触发器，将被删除的数据备份。这样，当应用程序删除Orders表中的记录时，将触发Orders表的删除触发器，该触发器在删除Order Details表的数据的同时触发Order Details的删除触发器。,递归触发器,直接递归 直接递归即触发器激发并执行一个操作，而该操作又使同一个触发器再次激发。例如，应用程序更新了表Table1，从而引发触
35、发器Trig1；Trig1再次更新表Table1，使触发器Trig1再次被引发。 间接递归 间接递归即触发器激发并执行一个操作，而该操作又使另一个表中的某个触发器激发；第二个触发器使原始表得到更新，从而再次引发第一个触发器。例如，应用程序更新了表Table1，并引发触发器Trig1；Trig1更新表Table2，从而使触发器Trig2被引发；Trig2转而更新表Table1，从而使Trig1再次被引发。 当将RECURSIVE_TRIGGERS数据库选项设置为OFF时，仅防止直接递归。若要也禁用间接递归，请将nested triggers服务器选项设置为 0。,INSTEAD OF触发器,IN
36、STEAD OF触发器可以替代触发语句的标准操作（INSERT、UPDATE或DELETE）。 例如，可以定义INSTEAD OF触发器在一列或多列上的执行错误或值的检查，然后在插入记录之前执行其他操作。举例说明，当工资表中小时工资列的更新值超过指定值时，可以定义触发器或者产生错误信息并回滚该事务，或者在审核日志中插入新记录（在工资表中插入该记录之前）。 可以在表或视图上定义INSTEAD OF触发器，然而，INSTEAD OF触发器对扩展视图能支持的更新类型最有用。例如，INSTEAD OF触发器能够通过视图修改多个基表，或者修改包含以下列的基表： timestamp数据类型； 计算列； 标
37、识列。,INSTEAD OF INSERT触发器,可以在视图或表上定义INSTEAD OF INSERT触发器来代替INSERT语句的标准操作。通常，在视图上定义INSTEAD OF INSERT触发器以在一个或多个基表中插入数据。 视图中的列可为空也可不为空，但如果视图中的列不允许为空，则INSERT语句必须为该列提供值。如果定义视图中的列表达式包括以下项目，则视图的该列允许为空。 对任何允许为空的基表列的引用； 算术运算符； 对函数的引用； 具有可为空的子表达式的CASE或COALESCE； NULLIF。,引用具有INSTEAD OF INSERT触发器的视图的 INSERT 语句必须为
38、每个不允许为空的视图列提供值。这包括不能指定输入值的基表列的视图列： 基表中的计算列； IDENTITY INSERT为OFF的基表中的标识列； 具有timestamp数据类型的基表列。 如果INSTEAD OF INSERT视图触发器使用inserted表中的数据对基表生成INSERT，则它应当通过排除INSERT语句选择列表中的列忽略这些类型的列值。INSERT语句可为这些类型的列生成虚值。,通过下面的语句创建表、视图和触发器，来理解这一过程。 CREATE TABLE BaseTable(PrimaryKey int IDENTITY(1,1)Color varchar(10) NOT
39、NULL,Material varchar(10) NOT NULL,ComputedCol AS (Color + Material) GO 其中，PrimaryKey是标识列，ComputedCol是计算列。,-创建包含BaseTable所有列的视图 CREATE VIEW InsteadView AS SELECT PrimaryKey, Color, Material, ComputedCol FROM BaseTable GO -在视图InsteadView创建INSTEAD OF INSERT触发器 CREATE TRIGGER InsteadTrigger on InsteadV
40、iew INSTEAD OF INSERT AS BEGININSERT INTO BaseTableSELECT Color, MaterialFROM inserted END GO,直接往BaseTable插入数据时，INSERT语句不能为PrimaryKey和ComputedCol列提供值。 例如： INSERT INTO BaseTable (Color, Material) VALUES (Red, Cloth) 引用InsteadView的INSERT语句必须为PrimaryKey和ComputedCol列提供值。 INSERT INTO InsteadView (Primary
41、Key, Color, Material, ComputedCol) VALUES (999, Blue, Plastic, XXXXXX),传递到InsteadTrigger的inserted表由不可为空的PrimaryKey和ComputedCol列构成，所以引用该视图的INSERT语句必须提供那些列的值。值999和XXXXXX传递到InsteadTrigger，但是触发器中的INSERT语句没有选择inserted、PrimaryKey或ComputedCol，因此忽略该值。实际插入BaseTable表时，列PrimaryKey自动赋值，列ComputedCol为BluePlastic。
42、 在表上指定的INSTEAD OF INSERT触发器和在视图上指定的INSTEAD OF触发器，其inserted 表中包含的计算列、标识列和timestamp列的值不同。,INSTEAD OF UPDATE触发器,可在视图上定义INSTEAD OF UPDATE触发器以代替UPDATE语句的标准操作。 通常，在视图上定义INSTEAD OF UPDATE触发器以便修改一个或多个基表中的数据。 引用带有INSTEAD OF UPDATE触发器的视图的UPDATE语句必须为SET子句中引用的所有不可为空的视图列提供值。该操作包括在基表中引用列的视图列（该基表不能指定输入值） 如： 基表中的计算
43、列； IDENTITY INSERT设置为OFF的基表中的标识列； 具有timestamp数据类型的基表列。,当引用表的UPDATE语句试图设置计算列、标识列或timestamp列的值时会产生错误信息，因为这些列的值必须由SQL Server决定。这些列必须包含在UPDATE语句中，以便满足该行的NOT NULL需要。然而，如果UPDATE语句引用带INSTEAD OF UPDATE触发器的视图，该视图中定义的逻辑能够回避这些列并避免错误。为此，INSTEAD OF UPDATE触发器必须不更新基表中相应列的值。通过不将这些列包含在UPDATE语句的SET子句中即可达到此目的。在inserte
44、d表中处理记录时，计算列、标识列或timestamp列可以包含虚值以满足NOT NULL列的需要，但是INSTEAD OF UPDATE触发器忽略这些列值并且由SQL Server设置正确的值。,由于INSTEAD OF UPDATE触发器不必在未更新的inserted列中处理数据，因此该解决方法起作用。在传递到INSTEAD OF UPDATE触发器的inserted表中，SET子句中指定的列遵从与 INSTEAD OF INSERT触发器中的inserted列相同的规则。对于在SET子句中未指定的列，inserted 表包含在发出UPDATE语句前已存在的值。触发器可以通过使用IF UPD
45、ATED(column)子句来测试特定的列是否已被更新。,INSTEAD OF DELETE触发器,可以在视图或表中定义INSTEAD OF DELETE触发器，以代替DELETE语句的标准操作。通常，在视图上定义INSTEAD OF DELETE触发器以便在一个或多个基表中修改数据。 DELETE语句不指定对现有数据类型的修改。DELETE语句只指定要删除的行。传递给DELETE触发器的inserted表总是空的。发送给DELETE触发器的deleted表包含在发出UPDATE语句前就存在的行的映像。如果在视图或表上定义INSTEAD OF DELETE触发器，则deleted表的格式以为视
46、图定义的选定列表的格式为基础。,触发器的应用,INSERT型触发器的应用 在Northwind数据库中的数据表Orders内，保存着客户的订单信息。在Order表创建INSERT触发器Orders_Insert，如果在插入数据时未指定预订日期和需求日期，触发器将系统时间设置为预订日期，并将需求日期设置为一星期后，当然也可以根据业务需要进行设计。 存储过程的代码如下： CREATE TRIGGER Orders_Insert ON Orders FOR INSERT ASUPDATE ORDERS SET OrderDate = GetDate() , RequiredDate = GetDat
47、e() + 7WHERE OrderID in (SELECT OrderID FROM inserted) GO,当执行如下插入操作时，如当前时间为2004年10月24日，由于没有指定预订日期和需求日期，触发器将被触发设置这些日期。 INSERT INTO ORDERS (CustomerID,EmployeeID) values (LILAS,1) GO SELECT * FROM Orders WHERE OrderID = identity GO,UPDATE型触发器的应用,当对Orders表进行更新操作时，如果系统的日期比需求日期晚，禁止该更新操作。在此创建UPDATE触发器Orde
48、rs_Update，触发器根据更新前的记录，只有需求日期在系统日期之前的记录才允许被更新。 存储过程的代码如下： CREATE TRIGGER Orders_Update ON Orders FOR Update ASif (SELECT Count(*) FROM deleted WHERE RequiredDate GetDate()(SELECT Count(*) FROM deleted)BEGINRAISERROR(需求日期在当前日期之前的订单才可以被修改。,16,1) ROLLBACK TRANSACTIONEND GO,当执行如下更新操作时，前提是订单号为11082的订单的需求日
49、期比系统时间小，该更新操作将被取消。 UPDATE Orders SET RequiredDate = 2004/10/31 WHERE OrderID = 11082 GO,DELETE型触发器的应用,在Order Details表上创建DELETE触发器OrderDetails_Delete，对Order Details表进行删除操作时，如果删除后的订单的明细为空，触发器将自动删除对应的订单。 存储过程的代码如下： CREATE TRIGGER OrderDetails_Delete ON Order Details FOR Delete ASDELETE FROM Orders WHERE OrderID IN (SELECT DISTINCT OrderID FROM deleted)AND (SELECT COUNT(*) FROM Order Details WHERE Orders.OrderID = Order Details.OrderID) = 0 GO,
