数据库级触发器

CREATE TABLE [dbo].[DDLEvents](
	[EventDate] [DATETIME] NOT NULL CONSTRAINT [DF__DDLEvents__Event__6ED7E1DA]  DEFAULT (GETDATE()),
	[EventType] [NVARCHAR](64) NULL,
	[EventDDL] [NVARCHAR](MAX) NULL,
	[EventXML] [XML] NULL,
	[DatabaseName] [NVARCHAR](255) NULL,
	[SchemaName] [NVARCHAR](255) NULL,
	[ObjectName] [NVARCHAR](255) NULL,
	[HostName] [VARCHAR](64) NULL,
	[IPAddress] [VARCHAR](32) NULL,
	[ProgramName] [NVARCHAR](255) NULL,
	[LoginName] [NVARCHAR](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO 

CREATE TRIGGER [MetedataTracking]
    ON DATABASE
    FOR DDL_DATABASE_LEVEL_EVENTS/*CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_SCHEMA, RENAME, CREATE_TABLE, ALTER_TABLE, DROP_TABLE*/
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 --SELECT @EventData
    DECLARE 
        @ip VARCHAR(32) =
        (
            SELECT client_net_address
                FROM sys.dm_exec_connections
                WHERE session_id = @@SPID
        );
 
    INSERT DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END


GO

ENABLE TRIGGER [MetedataTracking] ON DATABASE
GO

　　在这个触发器定义完成之后，只要不删除此触发器，则 常见 DDL 操作 的详细记录 都被 保存在 DDLEvents 这张表里了。

补充：

EVENTDATA() 函数返回的 xml 内容 (图片挂了) 去官网找吧

 

转载于:https://www.cnblogs.com/Frank99/p/5971575.html