DDL触发器学习整理
像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与 DML 触发器不同的是，它们不会为响应针对表或视图的 UPDATE、INSERT 或 DELETE 语句而激发。相反，它们将为了响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。
1.了解触发器的作用域
在响应当前数据库或服务器上处理的 Transact-SQL 事件时，可以触发 DDL 触发器。触发器的作用域取决于事件。例如，每当数据库中或服务器实例上发生 CREATE_TABLE 事件时，都会激发为响应 CREATE_TABLE 事件创建的 DDL 触发器。仅当服务器上发生 CREATE_LOGIN 事件时，才能激发为响应 CREATE_LOGIN 事件创建的 DDL 触发器。
在下面的示例中，每当数据库中发生 DROP TABLE 或 ALTER TABLE 事件时，都会激发 DDL 触发器 safety。
CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ; | |
在下面的示例中，如果当前服务器实例上发生任何 CREATE_DATABASE 事件，DDL 触发器将输出消息。此示例使用 EVENTDATA 函数检索相应的 Transact-SQL 语句的文本。有关如何将 EVENTDATA 与 DDL 触发器配合使用的详细信息，请参阅使用 EVENTDATA 函数。
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
| |
本主题后面的“选择触发 DDL 触发器的特定 DDL 语句”一部分中提供了一些链接，通过这些链接可以找到将 Transact-SQL 语句映射到为它们指定的作用域的列表。
数据库范围内的 DDL 触发器都作为对象存储在创建它们的数据库中。可以在 master 数据库中创建 DDL 触发器，这些触发器的行为与在用户设计的数据库中创建的 DDL 触发器的行为类似。可以通过查询 sys.triggers 目录视图获取有关 DDL 触发器的信息。可以在创建触发器的数据库上下文中或通过指定数据库名称作为标识符（例如 master.sys.triggers），查询 sys.triggers。
服务器范围内的 DDL 触发器作为对象存储在 master 数据库中。然而，可以通过在任何数据库上下文中查询 sys.server_triggers 目录视图，获取有关服务器范围内的 DDL 触发器的信息。
2.DDL事件
下表列出了可用于激发 DDL 触发器或事件通知的 DDL 事件。注意，每个事件都对应于一个 Transact-SQL 语句或存储过程，并且语句语法修改为在关键字之间加入了一个下划线字符 (_)。
| 重要提示： | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 执行类似 DDL 的操作的系统存储过程也可以激发 DDL 触发器和事件通知。请测试您的 DDL 触发器和事件通知以确定它们是否响应运行的系统存储过程。例如，CREATE TYPE 语句和 sp_addtype 存储过程都将激发针对 CREATE_TYPE 事件创建的 DDL 触发器或事件通知。 具有服务器或数据库作用域的 DDL 语句 可以创建 DDL 触发器或事件通知，以便在其中创建了它们的数据库发生以下事件（或服务器实例中的任何位置发生以下事件时）激发它们以做出响应。
具有服务器作用域的 DDL 语句可以创建当服务器实例中的任何位置发生以下事件时被激发以响应这些事件的 DDL 触发器或事件通知。
|
下图展示DDL 事件组
3.创建DDL触发器：
语法：
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
参数
-
schema_name
-
DML 触发器所属架构的名称。DML 触发器的作用域是为其创建该触发器的表或视图的架构。对于 DDL 或登录触发器，无法指定 schema_name。
-
trigger_name
-
触发器的名称。trigger_name 必须遵循标识符规则，但 trigger_name 不能以 # 或 ## 开头。
-
table |
view
-
对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。可以根据需要指定表或视图的完全限定名称。视图只能被 INSTEAD OF 触发器引用。不能对局部或全局临时表定义 DML 触发器。
-
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)。
-
FOR | AFTER
-
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 触发器的有效事件。
-
event_group
-
预定义的 Transact-SQL 语言事件分组的名称。执行任何属于 event_group 的 Transact-SQL 语言事件之后，都将激发 DDL 触发器。DDL 事件组中列出了 DDL 触发器的有效事件组。
CREATE 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 或 logon 事件是否导致执行触发器操作。
尝试上述操作时，将执行 Transact-SQL 语句中指定的触发器操作。
触发器可以包含任意数量和种类的 Transact-SQL 语句，但也有例外。有关详细信息，请参阅“备注”。触发器的用途是根据数据修改或定义语句来检查或更改数据；它不应向用户返回数据。触发器中的 Transact-SQL 语句常常包含控制流语言。
DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。例如，若要检索
deleted表中的所有值，则使用：SELECT * FROM deleted
有关详细信息，请参阅使用插入的和删除的表。
DDL 和登录触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息。有关详细信息，请参阅使用 EVENTDATA 函数。
示例：
A. 运用具有数据库范围的 DDL 触发器
下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。
USE AdventureWorks;
GO
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
RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO
B.运用具有服务器范围的 DDL 触发器
在以下示例中，如果当前服务器实例上出现任何 CREATE DATABASE 事件，则使用 DDL 触发器输出一条消息，并使用 EVENTDATA 函数检索对应 Transact-SQL 语句的文本。
| 注意： |
|---|
| 若要查看在 DDL 触发器中使用 EVENTDATA 的更多示例，请参阅联机帮助使用 EVENTDATA 函数。 IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
C.使用登录触发器 下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试（如果在此登录名下已运行三个用户会话）。 USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END; D.查看导致触发器触发的事件 以下示例将查询 |
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
--4.修改DDL触发器：
语法：
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statement (DDL Trigger)
ALTER TRIGGER trigger_name
ON { DATABASE | ALL SERVER }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier>
[ ; ] }
5.禁用触发器
语法
DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ] |
以下示例在数据库范围创建 DDL 触发器 safety，然后禁用该触发器。
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_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK;
GO
DISABLE TRIGGER safety ON DATABASE;
GO
下例禁用在服务器范围内创建的所有 DDL 触发器。
USE AdventureWorks; GO DISABLE Trigger ALL ON ALL SERVER; GO
6.启用DDL触发器：
语法
ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ] 示例： A.启用 DDL 触发器 以下示例在数据库范围创建 DDL 触发器
| ||||||||
语法:
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)
DROP TRIGGER trigger_name [ ,...n ]
ON { DATABASE | ALL SERVER }
[ ; ]
Trigger on a LOGON event (Logon Trigger)
DROP TRIGGER trigger_name [ ,...n ]
ON ALL SERVER
示例：
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO