像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与 DML 触发器不同的是，它们不会为响应针对表或视图的 UPDATE、INSERT 或 DELETE 语句而激发。相反，它们将为了响应各种数据定义语言 (DDL) 事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

1.了解触发器的作用域

在下面的示例中，每当数据库中发生 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 事件组

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

参数

示例：

A. 运用具有数据库范围的 DDL 触发器

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

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.查看导致触发器触发的事件

        
        

         
         
以下示例将查询 sys.triggers 和 sys.trigger_events 目录视图，以确定是哪个 Transact-SQL 语言事件导致触发了触发器 safety。其中的 safety 是在前一个示例中创建的。
        
        

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 } [ ; ]
 

    
    

    
    

     
      
    
    

    
    

     
     示例：
    
    

    
    

     
     A.

     
     
以下示例在数据库范围创建 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


    
    

    
    

     
      
    
    

    
    

     
     B.禁用以同一作用域定义的所有触发器

     
     


      
      
下例禁用在服务器范围内创建的所有 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 触发器 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
ENABLE TRIGGER safety ON DATABASE;
GO
 
B.启用以同一作用域定义的所有触发器
                      
                      

                       
                       
以下示例启用在服务器作用域级别创建的所有 DDL 触发器。
                       
                       

                        
                        
                         
                         
 
USE AdventureWorks;
GO
ENABLE Trigger ALL ON ALL SERVER;
GO
 

                       
                       

                      
                      
 

               
               

              
              
 

       
       

      
      


    
    
7.删除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

最后

以上就是复杂高跟鞋为你收集整理的DDL触发器学习整理具有服务器作用域的 DDL 语句的全部内容，希望文章能够帮你解决DDL触发器学习整理具有服务器作用域的 DDL 语句所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。