SQLServer中的DDL触发器

use AdventureWorks2019;
SELECT 
    name AS TriggerName,
    parent_class_desc,
    is_disabled
FROM 
    sys.triggers
WHERE 
    parent_class = 0 -- 表示数据库层级的触发器
    AND type = 'TR' -- 表示DDL触发器
ORDER BY 
    name;

SELECT 
    name AS TriggerName,
    is_disabled
FROM 
    sys.server_triggers
WHERE 
    type = 'TR' -- 表示DDL触发器
ORDER BY 
    name;

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;

IF EXISTS (SELECT * FROM sys.server_triggers  
    WHERE name = 'ddl_trig_database')  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  

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)')  

DROP TRIGGER ddl_trig_database ON ALL SERVER;  

-- 注意：它不会记录#或##这类的临时表相关的任何DDL语句

USE AdventureWorks2019;  
GO  

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));  
GO  

CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT ddl_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(nvarchar(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;  
GO  

--Test the trigger  
CREATE TABLE TestTable (a int)  
DROP TABLE TestTable ;  
GO  

SELECT * FROM ddl_log ;  
GO