利用Sql server 2005的資料庫觸發器開發的資料庫稽核追蹤系統

建立跟蹤的相關表

   --觸發事件記錄表
    CREATE TABLE [VER_EVENT](
        [SN] bigint identity(1,1),
        [LoginName] SYSNAME NULL,
        [PostTime] [datetime] NULL,
        [DatabaseName] SYSNAME NULL,
        [EventType] SYSNAME NULL,
        [ObjectType] SYSNAME NULL,
        [ObjectName] SYSNAME NULL,
        [CommandText] [nvarchar](max) NULL
    ) ON [PRIMARY]
    GO

    --版本控制表
    CREATE TABLE [VER_CONTROL](
        [SN] [bigint] IDENTITY(1,1) NOT NULL,
        [DatabaseName] [sysname] NULL,
        [ObjectType] [sysname] NULL,
        [ObjectName] [sysname] NULL,
        [Version] [int] NULL,
        [SN_EVENT] [bigint] NULL
    ) ON [PRIMARY]

 

建立觸發器

/************************************************************************
-- 功        能：資料庫的物件版本跟蹤系統
-- 日        期：2008-10-09
-- 作        者：
-- 參       數：
-- 返    回    值：
-- 說        明：有兩個相關的表
************************************************************************/
--CREATE TRIGGER VERSION_CONTROL
ALTER TRIGGER [VERSION_CONTROL]
ON DATABASE
FOR   
    CREATE_TABLE,ALTER_TABLE,DROP_TABLE,
    CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE,
    CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION
AS
BEGIN

    SET NOCOUNT ON
       
    DECLARE @ObjectName SYSNAME
    DECLARE @EventType SYSNAME
    DECLARE @DatabaseName SYSNAME
    DECLARE @ObjectType SYSNAME
    DECLARE @CommandText NVARCHAR(MAX)   
    DECLARE @Ver INT
    DECLARE @SN INT
   
    SET @Ver = NULL
    SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname')
    SET @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','sysname')
    SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')
    SET @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
    SET @CommandText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    INSERT INTO VER_EVENT([LoginName],[PostTime],[DatabaseName],[EventType],[ObjectType],[ObjectName],[CommandText])
    SELECT
            EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','sysname') as LoginName,
            EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','datetime') as PostTime,
            @DatabaseName,@EventType,@ObjectType,@ObjectName,@CommandText
    SET @SN = @@IDENTITY

    SELECT @Ver = [Version]
    FROM VER_CONTROL
    WHERE    [DatabaseName] = @DatabaseName    AND [ObjectType] = @ObjectType    AND    [ObjectName] = @ObjectName

    IF @EventType LIKE 'CREATE%' AND @ObjectName NOT LIKE 'T%'
    BEGIN
        IF @Ver IS NULL
            INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
            SELECT @DatabaseName,@ObjectType,@ObjectName,10001,0
        ELSE
            UPDATE  VER_CONTROL
            SET [Version] =  @Ver + 10000,[SN_EVENT] = @SN
            WHERE  [DatabaseName] = @DatabaseName    AND [ObjectType] = @ObjectType    AND    [ObjectName] = @ObjectName
    END
    IF @EventType LIKE 'ALTER%' AND @ObjectName NOT LIKE 'T%'
    BEGIN
        IF @Ver IS NULL
            INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
            SELECT @DatabaseName,@ObjectType,@ObjectName,10001,0
        ELSE
            UPDATE  VER_CONTROL
            SET [Version] = @Ver + 1,[SN_EVENT] = @SN
            WHERE  [DatabaseName] = @DatabaseName    AND [ObjectType] = @ObjectType    AND    [ObjectName] = @ObjectName
    END
    IF @EventType LIKE 'DROP%' AND @ObjectName NOT LIKE 'T%'
    BEGIN
        IF @Ver IS NULL
            INSERT INTO [VER_CONTROL]([DatabaseName],[ObjectType],[ObjectName],[Version],[SN_EVENT])
            SELECT @DatabaseName,@ObjectType,@ObjectName,-10001,0
        ELSE
            UPDATE  VER_CONTROL
            SET [Version] =  @Ver * (-1),[SN_EVENT] = @SN
            WHERE  [DatabaseName] = @DatabaseName    AND [ObjectType] = @ObjectType    AND    [ObjectName] = @ObjectName
        RETURN
    END

原文：http://www.cnblogs.com/jinzhenshui/articles/1360236.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-598140/，如需轉載，請註明出處，否則將追究法律責任。