none
如何捕获SQL Server 中对象的改变? RRS feed

  • 问题

  • 大家好,

    在我们实际应用程序中,经常有这样的需求:如何捕获SQL Server 中对象的改变 ? 如:创建、修改、删除表、视图、存储过程等。

    谢谢。
    2011年3月1日 5:11
    版主

答案

  • 大家好,

    在此提供一个示例展示如何捕获SQL Server 中对象的改变。在SQL Server 2005或者以上版本,我们可以创建一个DDL触发器,DDL触发器用于相应各种数据定义语言(DDL)事件,这些事件主要对于Transact-SQL CREATEALTERDROP 语句。下面我们一步一步演示如何创建该类型的触发器。

    1. 创建一个数据库命名为AuditDB,请参阅下面的语句:

    CREATE DATABASE AuditDB;
    GO

     

    2. 创建一张表命名为DDLEvents去捕获这些事件(创建、修改、删除表、视图、存储过程等),请参阅下面的语句:

    USE AuditDB;

    GO

     

    CREATE TABLE dbo.DDLEvents

    (

        EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

        EventType    NVARCHAR(64),

        EventDDL     NVARCHAR(MAX),

        EventXML     XML,

        DatabaseName NVARCHAR(255),

        SchemaName   NVARCHAR(255),

        ObjectName   NVARCHAR(255),

        HostName     VARCHAR(64),

        IPAddress    VARCHAR(32),

        ProgramName  NVARCHAR(255),

        LoginName    NVARCHAR(255)

    );

    GO

     

    3. 创建一个DDL触发器,命名为DDLTriggertTrace,请参阅下面的语句:

     

    USE AuditDB;

    GO

     

    CREATE TRIGGER DDLTriggertTrace

    ON DATABASE

    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_TABLE,ALTER_TABLE,DROP_TABLE

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE

        @EventData XML = EVENTDATA();

     

    DECLARE

        @ip VARCHAR(32) =

        (

            SELECT client_net_address

                FROM sys.dm_exec_connections

                WHERE session_id = @@SPID

        );

     

    INSERT AuditDB.dbo.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

     

    4. 创建一张Test表,请参阅下面的语句:

     

    USE AuditDB;

    GO

     

    CREATE TABLE Test

    (

    ID INT,

    CDT DATETIME

    )

    GO

     

    5. 查阅结果

     

    USE AuditDB;

    GO

    SELECT *FROM DDLEvents

          

    6. 事件XML数据如下:

     

    <EVENT_INSTANCE>

      <EventType>CREATE_TABLE</EventType>

      <PostTime>2011-02-28T13:18:05.633</PostTime>

      <SPID>58</SPID>

      <ServerName>ServerName</ServerName>

      <LoginName>LoginName</LoginName>

      <UserName>dbo</UserName>

      <DatabaseName>AuditDB</DatabaseName>

      <SchemaName>dbo</SchemaName>

      <ObjectName>Test</ObjectName>

      <ObjectType>TABLE</ObjectType>

      <TSQLCommand>

        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

        <CommandText>CREATE TABLE Test

    (

    ID INT,

    CDT DATETIME

    )

    </CommandText>

      </TSQLCommand>

    </EVENT_INSTANCE>

    一些关于DDL的小贴士:

    1. 我们可以禁用或者启用 触发器,请参阅下面的语句:

     

    DISABLE TRIGGER DDLTriggertTrace ON DATABASE;

    GO

    ENABLE TRIGGER DDLTriggertTrace ON DATABASE;

    GO

     

    2. 更多信息,请参阅:
    CREATE TRIGGER (Transact-SQL)
    http://msdn.microsoft.com/zh-cn/library/ms189799(v=SQL.90).aspx

     

    谢谢。

     

    2011年3月1日 5:14
    版主