sql server DDL 触发器

sql server DDL触发器：监控数据库结构变化,并记录在 DBA_DatabaseLog表中

在每个需要监控的数据库里写建数据库触发器

USE [WMS]
GO

/****** Object:  DdlTrigger [ddlDatabaseTriggerLog]    Script Date: 11/10/2012 12:21:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
    DECLARE @logins sysname;
    DECLARE @hosts nvarchar(60);
--    DECLARE @ip varchar(32);
--
--set @ip =( SELECT client_net_address
--                FROM sys.dm_exec_connections
--                WHERE session_id = @@SPID );
 
    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;

    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);

    INSERT [DBA].[dbo].[DBA_DatabaseLog]
        (
        [DatabaseName],
        [PostTime],
        [Logins],
        [Hosts],
        [DatabaseUser],
        [Event],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent]
--        ,[ip] 
        )
    VALUES
        (
        CONVERT(sysname,DB_NAME()),
        GETDATE(),
        CONVERT(sysname,SYSTEM_USER),
        HOST_NAME(),
        CONVERT(sysname, CURRENT_USER),
        @eventType,
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @data
--        ,@ip
        );
END;

 

 

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO

ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO