锐客网

  1. 首页 > it技术 > >

触发器的创建及使用(sqlserver|触发器的创建及使用(sqlserver 2005)

触发器的创建及使用(sqlserver 2005)
创建 DML 或 DDL 触发器。触发器是数据库服务器中发生事件时自动执行的特种存储过程。如果用户要通过数据操作语言 (DML) 事件编辑数据，则执行 DML 触发器。DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。DDL 触发器用于响应各种数据定义语言 (DDL) 事件。这些主要是 CREATE、ALTER 和 DROP 语句。通过 Transact-SQL 语句或使用 Microsoft .NET Framework 公共语言运行时 (CLR) 创建的程序集的方法，可以在 SQL Server 2005 Database Engine 中直接创建 DML 和 DDL 触发器，将其上传给一个 SQL Server 实例。SQL Server 允许为任何特定语句创建多个触发器。
安全性 注意：
触发器内的恶意代码可使用升级后的权限运行。有关如何减少这种隐患的详细信息，请参阅管理触发器安全性。

Transact-SQL 语法约定
语法
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement[ ; ] [ ...n ] | EXTERNAL NAME }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
::=
assembly_name.class_name.method_name
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement[ ; ] [ ...n ] | EXTERNAL NAME < method specifier >[ ; ] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
::=
assembly_name.class_name.method_name备注
DML 触发器
DML 触发器经常用于强制执行业务规则和数据完整性。SQL Server 通过 ALTER TABLE 和 CREATE TABLE 语句来提供声明性引用完整性 (DRI)。但是，DRI 不提供跨数据库引用完整性。引用完整性是指有关表的主键和外键之间的关系的规则。若要强制实现引用完整性，请在 ALTER TABLE 和 CREATE TABLE 中使用 PRIMARY KEY 和 FOREIGN KEY 约束。如果触发器表存在约束，则在 INSTEAD OF 触发器执行之后和 AFTER 触发器执行之前检查这些约束。如果违反了约束，则将回滚 INSTEAD OF 触发器操作，并且不激活 AFTER 触发器。
可以使用 sp_settriggerorder 来指定要对表执行的第一个和最后一个 AFTER 触发器。对于一个表，只能为每个 INSERT、UPDATE 和 DELETE 操作指定一个第一个和最后一个 AFTER 触发器。如果在同一个表上还有其他 AFTER 触发器，这些触发器将随机执行。
如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器，将删除所修改触发器上设置的第一个或最后一个属性，并且必须使用 sp_settriggerorder 重置顺序值。
只有在成功执行触发 SQL 语句之后，才会执行 AFTER 触发器。判断执行成功的标准是：执行了所有与已更新对象或已删除对象相关联的引用级联操作和约束检查。
如果为表定义的 INSTEAD OF 触发器对表执行了一般会再次触发 INSTEAD OF 触发器的语句，该触发器不会被递归调用，而是像表中没有 INSTEAD OF 触发器一样处理该语句，并启动一系列约束操作和 AFTER 触发器执行。例如，如果触发器定义为表的 INSTEAD OF INSERT 触发器，并且触发器对同一个表执行 INSERT 语句，则由 INSTEAD OF 触发器执行的 INSERT 语句不会再次调用该触发器。触发器执行的 INSERT 将启动执行约束操作的进程，并触发为表定义的任一 AFTER INSERT 触发器。
如果为视图定义的 INSTEAD OF 触发器对视图执行了一条通常会再次触发 INSTEAD OF 触发器的语句，该语句不会被递归调用，而是将该语句解析为对视图所依存的基本表进行的修改。在这种情况下，视图定义必须满足可更新视图的所有约束。有关可更新视图的定义，请参阅通过视图修改数据。
例如，如果触发器定义为视图的 INSTEAD OF UPDATE 触发器，并且触发器执行引用同一视图的 UPDATE 语句，则由 INSTEAD OF 触发器执行的 UPDATE 语句不会再次调用该触发器。对视图处理由该触发器执行的 UPDATE 语句时，就像该视图没有 INSTEAD OF 触发器一样。由 UPDATE 更改的列必须解析到一个基表。对基表的每次修改都将应用约束并触发为该表定义的 AFTER 触发器。
测试对指定列的 UPDATE 或 INSERT 操作
您可以设计一个 Transact-SQL 触发器，根据对特定列的 UPDATE 或 INSERT 修改来执行某些操作。可在触发器的主体中使用 UPDATE() 或 COLUMNS_UPDATED 来达到此目的。UPDATE() 可以测试对某个列的 UPDATE 或 INSERT 尝试。COLUMNS_UPDATED 可以测试对多个列执行的 UPDATE 或 INSERT 操作，并返回一个位模式，指示插入或更新的列。
触发器限制
CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。
触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。
如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。
在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。
如果一个表的外键包含对定义的 DELETE/UPDATE 操作的级联，则不能对为表上定义 INSTEAD OF DELETE/UPDATE 触发器。
在触发器内可以指定任意的 SET 语句。选择的 SET 选项在触发器执行期间保持有效，然后恢复为原来的设置。
如果触发了一个触发器，结果将返回给执行调用的应用程序，就像使用存储过程一样。若要避免由于触发器触发而向应用程序返回结果，请不要包含返回结果的 SELECT 语句，也不要包含在触发器中执行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理；这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。
DELETE 触发器不能捕获 TRUNCATE TABLE 语句。跃然 TRUNCATE TABLE 语句实际上就是不含 WHERE 子句的 DELETE 语句（因为它删除所有行），但它是无日志记录的，因而不能执行触发器。因为 TRUNCATE TABLE 语句的权限属于该表所有者且不可转移，所以只有表所有者才需要考虑无意中用 TRUNCATE TABLE 语句规避 DELETE 触发器的问题。
无论有日志记录还是无日志记录，WRITETEXT 语句都不触发触发器。
在 DML 触发器中不允许使用下列 Transact-SQL 语句。
ALTER DATABASE
CREATE DATABASE
DROP DATABASE
LOAD DATABASE
LOAD LOG
RECONFIGURE

RESTORE DATABASE
RESTORE LOG

另外，如果对作为触发操作目标的表或视图使用 DML 触发器，则不允许在该触发器的主体中使用下列 Transact-SQL 语句。
重要事项：
尽管 SQL Server 2005 中引入了此限制，但是当向后兼容模式设置为 80 时，仍会强制实施。
CREATE INDEX
ALTER INDEX
DROP INDEX

DBCC DBREINDEX
ALTER PARTITION FUNCTION
DROP TABLE

用于执行以下操作的 ALTER TABLE：
添加、修改或删除列。
切换分区。
添加或删除 PRIMARY KEY 或 UNIQUE 约束。
注意：
因为 SQL Server 不支持针对系统表的用户定义的触发器，因此我们建议不要为系统表创建用户定义触发器。
DDL 触发器
DDL 触发器像标准触发器一样，在响应事件时执行存储过程。但与标准触发器不同的是，它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。它们主要在响应数据定义语言 (DDL) 语句执行存储过程。这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。有关 DDL 触发器的详细信息，请参阅 DDL 触发器。
与 DML 触发器不同，DDL 触发器的作用域不是架构。因此，不能将 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 用于查询有关 DDL 触发器的元数据。请改用目录视图。有关详细信息，请参阅获取有关 DDL 触发器的信息。
注意：
服务器作用域的 DDL 触发器显示在 SQL Server Management Studio 对象资源管理器中的“触发器”文件夹中。此文件夹位于“服务器对象”文件夹下。具有数据库作用域的 DDL 触发器位于 Database Triggers 文件夹中。此文件夹位于相应数据库的“可编程性”文件夹下。常规触发器注意事项
返回结果
SQL Server 的未来版本将删除从触发器返回结果的功能返回结果集的触发器可能会引起应用程序出现并非计划中与它们协同工作的意外行为。避免在新的开发工作中从触发器返回结果集，并计划修改当前执行此操作的应用程序。若要在 SQL Server 2005 中防止触发器返回结果集，请将disallow results from triggers 选项设置为 1。在 SQL Server 的未来版本中，该选项的默认设置将为 1。
多个触发器
SQL Server 允许为每个 DML 或 DDL 事件创建多个触发器。例如，如果为已经有了 UPDATE 触发器的表执行 CREATE TRIGGER FOR UPDATE，则将再创建一个 UPDATE 触发器。在 SQL Server 早期版本中，对于每个表，每个 INSERT、UPDATE 或 DELETE 数据修改事件只允许有一个触发器。
注意：
如果触发器名称不同，则 CREATE TRIGGER（兼容级别为 70）的默认行为是在现有的触发器中添加其他触发器。如果触发器名称相同，则 SQL Server 返回一条错误消息。但是，如果兼容级别小于或等于 65，那么，即使触发器名称不同，使用 CREATE TRIGGER 语句创建的任何新触发器都将替换同一类型的任何现有触发器。有关详细信息，请参阅 sp_dbcmptlevel (Transact-SQL)。
递归触发器
如果使用 ALTER DATABASE 启动了 RECURSIVE_TRIGGERS 设置，则 SQL Server 还允许递归调用触发器。
递归触发器可以采用下列递归类型：
间接递归
在间接递归中，一个应用程序更新了表 T1。这触发了触发器 TR1，从而更新了表 T2。在这种情况下，将触发触发器 T2，从而更新 T1。
直接递归
在直接递归中，应用程序更新了表 T1。这触发了触发器 TR1，从而更新了表 T1。由于表 T1 被更新，将再次触发触发器 TR1，依此类推。
以下示例同时使用了间接和直接触发器递归。假设对表 T1 定义了两个更新触发器 TR1 和 TR2。触发器 TR1 以递归方式更新表 T1。UPDATE 语句各执行 TR1 和 TR2 一次。另外，执行 TR1 将触发执行 TR1（递归）和 TR2。指定触发器的 inserted 和 deleted 表包含仅与调用触发器的 UPDATE 语句对应的行。
注意：
仅当使用 ALTER DATABASE 启用了 RECURSIVE_TRIGGERS 设置时，才能发生前述行为。执行为特定事件定义的多个触发器时，并没有确定的执行顺序。每个触发器都应是自包含的。禁用 RECURSIVE_TRIGGERS 的设置只能阻止直接递归。若要同时禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。
如果任一触发器执行了 ROLLBACK TRANSACTION 语句，则无论嵌套级是多少，都不会再执行其他触发器。
嵌套触发器
触发器最多可以嵌套 32 级。如果一个触发器更改了包含另一个触发器的表，则第二个触发器将被触发，然后该触发器又可以调用第三个触发器，依此类推。如果链中任意一个触发器引发了无限循环，则会超出嵌套级限制，从而导致取消触发器。若要禁用嵌套触发器，请用 sp_configure 将 nested triggers 选项设置为 0（关闭）。默认配置允许嵌套触发器。如果关闭嵌套触发器，则不管使用 ALTER DATABASE 设置的 RECURSIVE_TRIGGERS 设置如何，都将同时禁用递归触发器。
注意：
如果 Transact-SQL 触发器通过引用 CLR 例程、类型或聚合来执行托管代码，则此引用只算 32 级嵌套限制中的一级。从托管代码内部调用的方法不根据此限制进行计数。延迟名称解析
SQL Server 允许 Transact-SQL 存储过程、触发器和批处理引用编译时不存在的表。这种功能称为延迟名称解析。但是，如果 Transact-SQL 存储过程、触发器或批处理引用了存储过程或触发器中定义的表，则仅当容级别设置为 65 时，才会在创建时发出警告。如果使用了批处理，则将在编译时发出警告。如果引用的表不存在，则将在运行时返回错误消息。有关详细信息，请参阅延迟名称解析和编译。
参数
schema_name
DML 触发器所属架构的名称。DML 触发器的作用域是为其创建该触发器的表或视图的架构。对于 DDL 触发器，无法指定 schema_name。
trigger_name
触发器的名称。每个 trigger_name 必须遵循标识符规则，但 trigger_name 不能以 # 或 ## 开头。
table | view
对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。视图只能被 INSTEAD OF 触发器引用。
DATABASE
将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器。
ALL SERVER
将 DDL 触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现 event_type 或 event_group，就会激发该触发器。
WITH ENCRYPTION
对 CREATE TRIGGER 语句的文本进行加密。使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。不能为 CLR 触发器指定 WITH ENCRYPTION。
EXECUTE AS
指定用于执行该触发器的安全上下文。允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。
AFTER
指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被激发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
如果仅指定 FOR 关键字，则 AFTER 为默认值。
不能对视图定义 AFTER 触发器。
INSTEAD OF
指定 DML 触发器是“代替”SQL 语句执行的，因此其优先级高于触发语句的操作。不能为 DDL 触发器指定 INSTEAD OF。
对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。
INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
指定数据修改语句，这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。
对于 INSTEAD OF 触发器，不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。同样，也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项。
event_type
执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。用于激发 DDL 触发器的 DDL 事件中列出了在 DDL 触发器中可用的事件。
event_group
预定义的 Transact-SQL 语言事件分组的名称。执行任何属于 event_group 的 Transact-SQL 语言事件之后，都将激发 DDL 触发器。用于激发 DDL 触发器的事件组中列出了在 DDL 触发器中可用的事件组。
CREAT TRIGGER 运行完毕之后，event_group 还可通过将其涵盖的事件类型添加到 sys.trigger_events 目录视图中来作为宏使用。
WITH APPEND
指定应该再添加一个现有类型的触发器。仅当兼容级别等于或低于 65 时，才需要使用此可选子句。如果兼容级别等于或高于 70，则不需要使用 WITH APPEND 子句来添加现有类型的其他触发器。这是兼容级别设置等于或高于 70 的 CREATE TRIGGER 的默认行为。有关详细信息，请参阅 sp_dbcmptlevel (Transact-SQL)。
WITH APPEND 不能与 INSTEAD OF 触发器一起使用。如果显式声明了 AFTER 触发器，则也不能使用该子句。仅当为了向后兼容而指定了 FOR 时（但没有 INSTEAD OF 或 AFTER）时，才能使用 WITH APPEND。如果指定了 EXTERNAL NAME（即触发器为 CLR 触发器），则不能指定 WITH APPEND。
重要事项：
在 Microsoft SQL Server 的将来版本中将删除 WITH APPEND。请避免在新的开发工作中使用 WITH APPEND，并计划修改当前使用它的应用程序。

NOT FOR REPLICATION
指示当复制代理修改涉及到触发器的表时，不应执行触发器。有关详细信息，请参阅使用 NOT FOR REPLICATION 来控制约束、标识和触发器。
sql_statement
触发条件和操作。触发器条件指定其他标准，用于确定尝试的 DML 或 DDL 语句是否导致执行触发器操作。
尝试 DML 或 DDL 操作时，将执行 Transact-SQL 语句中指定的触发器操作。
触发器可以包含任意数量和种类的 Transact-SQL 语句，但也有例外。有关详细信息，请参阅“备注”。触发器的用途是根据数据修改或定义语句来检查或更改数据；它不应向用户返回数据。触发器中的 Transact-SQL 语句常常包含控制流语言。
DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，则使用：
复制代码
SELECT *
FROM deleted有关详细信息，请参阅使用插入的和删除的表。
DDL 触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息。有关详细信息，请参阅使用 EVENTDATA 函数。
在 DELETE、INSERT 或 UPDATE 触发器中，如果兼容级别设置为 70，则 SQL Server 不允许在 inserted 和 deleted 表中存在 text、ntext 或 image 列引用。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。如果兼容级别等于或低于 65，则将为 inserted 或 deleted 的文本、ntext 或者允许空值的 image 列返回空值；如果列不能为空，则返回零长度的字符串。
如果兼容级别等于或高于 80，SQL Server 将允许通过表或视图的 INSTEAD OF 触发器来更新 text、ntext 或 image 列。
重要事项：
在 Microsoft SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。AFTER 和 INSTEAD OF 触发器均支持 inserted 和 deleted 表中的 varchar(MAX)、nvarchar(MAX) 和 varbinary(MAX) 数据。

< method_specifier >
对于 CLR 触发器，指定程序集与触发器绑定的方法。该方法不能带有任何参数，并且必须返回空值。class_name 必须是有效的 SQL Server 标识符，并且该类必须存在于可见程序集中。如果该类有一个使用“.”来分隔命名空间部分的命名空间限定名称，则类名必须用 [] 或“ ”分隔符分隔。该类不能为嵌套类。
注意：
默认情况下，SQL Server 无法运行 CLR 代码。您可以创建、修改和删除引用托管代码模块的数据库对象，但是，除非使用 sp_configure 启用了 clr enabled Option，否则不会在 SQL Server 实例中执行这些引用。

权限
若要创建 DML 触发器，则需要对要创建触发器的表或视图具有 ALTER 权限。
若要创建具有服务器作用域的 DDL 触发器 (ON ALL SERVER)，则需要对服务器有 CONTROL SERVER 权限。若要创建具有数据库作用域的 DDL 触发器 (ON DATABASE)，则需要在当前数据库中有 ALTER ANY DATABASE DDL TRIGGER 权限。
示例
A. 使用包含提醒消息的 DML 触发器
如果有人试图在 Customer 表中添加或更改数据，下列 DML 触发器将向客户端显示一条消息。
复制代码
USE AdventureWorks
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10)
GO
B. 使用包含提醒电子邮件的 DML 触发器
如果 MaryM 表发生更改，以下示例将向指定人员 (MaryM) 发送电子邮件。
复制代码
USE AdventureWorks
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the sales force.'
GO
C. 使用 DML 触发器在 PurchaseOrderHeader 和 Vendor 表之间强制实现业务规则
由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在本例中是业务规则）都必须定义为触发器。
以下示例将创建一个 DML 触发器。如果有人试图将一个新采购订单插入到 PurchaseOrderHeader 表中，此触发器将进行检查以确保供应商具有良好的信用等级。若要获取供应商的信用等级，必须引用 Vendor 表。如果信用等级太低，则显示信息，并且不执行该插入操作。
复制代码
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader p INNER JOIN inserted i ON p.PurchaseOrderID =
i.PurchaseOrderID JOIN Purchasing.Vendor v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
D. 使用延迟名称解析
以下示例将创建两个触发器，用于阐释延迟名称解析。
复制代码
USE AdventureWorks
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS
SELECT e.EmployeeID, e.BirthDate, x.info
FROM HumanResources.Employee e INNER JOIN does_not_exist x
ON e.EmployeeID = x.xID
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers t INNER JOIN sys.sql_modules m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE AdventureWorks
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig2
GO
CREATE TRIGGER trig2
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = 'AltPhone'
FROM HumanResources.Employee
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers t INNER JOIN sys.sql_modules m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO
E. 运用具有数据库作用域的 DDL 触发器
以下示例将使用 DDL 触发器来阻止从数据库中删除任何同义词。
复制代码
USE AdventureWorks
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
PRINT 'You must disable Trigger "safety" to drop synonyms!'
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE
GO
F. 运用具有服务器作用域的 DDL 触发器
在以下示例中，如果当前服务器实例出现任何 CREATE LOGIN、ALTER LOGIN 或 DROP LOGIN 事件，则将使用 DDL 触发器来打印消息，并使用 EVENTDATA 函数来检索相应的 Transact-SQL 语句的文本。
复制代码
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_login')
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Login Event Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
G. 查看导致触发触发器的事件
以下示例将查询 sys.triggers 和 sys.trigger_events 目录视图，以确定是哪个 Transact-SQL 语言事件导致触发了触发器 safety。其中的 safety 是在前一个示例中创建的。
【触发器的创建及使用(sqlserver|触发器的创建及使用(sqlserver 2005)】 复制代码
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO

    推荐阅读


    上一篇：Learning D3.js(5) d3的path讲解

    下一篇：修改pppd，提高openwrt中pppoe多拨成功率