首頁>Program>source

我需要在我的SQL Server 2005資料庫中的两个表上實施更改跟蹤.我需要审核添加,删除,更新(详细說明更新的內容).我当時打算使用觸發器来執行此操作,但是在Google上四處瀏覽之後,我發現錯誤地執行此操作非常容易,我想一開始就避免這種情况。

任何人都可以發佈一个成功且優雅地完成更新的觸發器示例吗? 我希望最终得到一个具有以下結構的审核表:

  • ID
  • LogDate
  • TableName
  • TransactionType (update/insert/delete)
  • RecordID
  • FieldName
  • OldValue
  • NewValue

...但是我愿意征求意见。

谢谢!

最新回復
  • 6月前
    1 #

    我只想指出几點:

    Use code generators 您無法通過單个過程来跟蹤所有表,您將需要在每个被跟蹤的表上生成相似但截然不同的觸發器.這種工作最適合自動代碼生成.在您的位置,我將使用XSLT轉換从XML生成代碼,並且可以从元資料自動生成XML.這樣,您可以在每次更改审計邏輯/結構或添加/更改目標表時通過重新生成觸發器来轻松維護觸發器。

    考虑 capacity planning 进行审核.到目前為止,跟蹤所有value變化的审計表將是資料庫中最大的表:它將包含所有当前資料,並且 当前資料的所有歷史記錄.這樣的表会將資料庫大小增加2-3个數量級(x10,x100).审核表將很快成為一切的瓶颈:

      every DML operation will require locks in the audit table

      all administrative and maintenance operations will have to accommodate the size of the database due to audit

    考虑 schema changes .可以删除名為" Foo"的表,然後再建立另一个名為" Foo"的表.审核跟蹤必须能够區分两个不同的物件.最好使用尺寸變化缓慢的方法。

    考虑需要 efficiently delete 审核記錄.当應用程式主题策略規定的保留期限到期時,您需要能够删除到期的审核記錄.現在看来似乎没什麼大不了的,但是5年後,当第一笔記錄到期時,审計表已增长到9.5TB,這可能是一个問题。

    考虑需要 query the audit .必须準備审計表結構以有效地响應审計查詢.如果無法查詢您的审核,則没有任何value.這些查詢將完全由您的要求決定,只有您自己知道這些要求,但是大多數审計記錄都是按物件("此記錄對此記錄發生了什麼變化")查詢時間間隔("昨天晚上7點至晚上8點之間發生了什麼變化?").表?)或作者("資料庫中Bob做了什麼更改?").

  • 6月前
    2 #

    我们正在使用ApexSQL Audit生成审核觸發器,以下是此工具使用的資料結構.如果您不打算购买第三方解決方案,則可以在試用模式下安裝此工具,看看它们如何實現觸發器和儲存,然後為自己建立類似的东西。

    關於這些表的工作方式,我没有過多地花時間,但希望這可以帮助您入門。

  • 6月前
    3 #

    没有通用的方法可以按照您想要的方式进行操作.最终,您最终要為每个表編寫大量代碼.更不用說如果您需要比较每列的變化,這可能会很慢。

    另外,您可能同時更新多行這一事實意味着您需要打開一个遊標以遍歷所有記錄。

    我要做的方法是使用結構与您要跟蹤的表相同的表,稍後取消顯示它實際上是在更改哪些列.我還將跟蹤實際进行更改的会话.假設您在要跟蹤的表中具有主键。

    所以给了這樣一个表

    CREATE TABLE TestTable  
    (ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
    Name1 NVARCHAR(40) NOT NULL,  
    Name2 NVARCHAR(40))
    

    我將在审計架構中建立一个這樣的审計表。

    CREATE TABLE Audit.TestTable  
    (SessionID UNIQUEIDENTIFER NOT NULL,  
    ID INT NOT NULL,
    Name1  NVARCHAR(40) NOT NULL,  
    Name2  NVARCHAR(40),  
    Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),  
    RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),  
    ChangedDate DATETIME NOT NULL Default GETDATE(),  
    ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())
    

    還有這樣的更新觸發器

    CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS  
    BEGIN  
        SET NOCOUNT ON
        DECLARE @SessionID UNIQUEIDENTIFER
        SET @SessionID = NEWID()
        INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
        SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted
        INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
        SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted
    END
    

    執行速度非常快.在報告過程中,您只需根据sessionID和Primary key聯接行並生成報告.另外,您可以具有一个批處理作業,该作業会定期檢查审核表中的所有表,並準備一个顯示更改的名稱/值對。

    HTH

  • 6月前
    4 #

    每个要监视的表,都需要自己的觸發器.很明顯,正如公认的答案所指出的那樣,代碼生成將是一件好事.

    如果您類似這種方法,最好使用此觸發器並用每个表的生成代碼分別替換一些通用步骤。

    尽管如此,我還是建立了一个 fully generic Audit-Trigger .观察表 must have a PK ,但此PK甚至可能是多列

    某些列型別(例如BLOB)可能不起作用,但是您可以轻松排除它们。

    這並不是最佳的表現:-D

    To be honest: 這更像是一種练习...

    SET NOCOUNT ON;
    GO
    CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER
                          ,LogDate DATETIME
                          ,TableSchema VARCHAR(250)
                          ,TableName VARCHAR(250)
                          ,AuditType VARCHAR(250),Content XML);
    GO
    

    -某些表對此进行測試(故意使用了古怪的PK列...)

    CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL
                          ,ID2 UNIQUEIDENTIFIER NOT NULL
                          ,Test1 VARCHAR(100)
                          ,Test2 DATETIME);
    --Add a two column PK
    ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);
    

    -一些測試資料

    INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
     ({d'2000-01-01'},NEWID(),'Test1',NULL)
    ,({d'2000-02-01'},NEWID(),'Test2',{d'2002-02-02'});
    

    -這是当前內容

    SELECT * FROM dbo.Testx;
    GO
    

    -审計的觸發因素

       CREATE TRIGGER [dbo].[UpdateTestTrigger]
        ON [dbo].[Testx]
        FOR UPDATE,INSERT,DELETE
        AS 
        BEGIN
            IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;
            SET NOCOUNT ON;
            DECLARE @tableSchema VARCHAR(250);
            DECLARE @tableName   VARCHAR(250);
            DECLARE @AuditID UNIQUEIDENTIFIER=NEWID();
            DECLARE @LogDate DATETIME=GETDATE();
            SELECT @tableSchema = sch.name
                  ,@tableName   = tb.name
            FROM sys.triggers AS tr
            INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id 
            INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id
            WHERE tr.object_id = @@PROCID
           DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN 'upd'
                                   ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN 'del' ELSE 'ins' END END;
           SELECT * INTO #tmpInserted FROM inserted;
           SELECT * INTO #tmpDeleted FROM deleted;
           SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME
           INTO #tmpPKColumns
           FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG
                                                                AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA
                                                                AND tc.TABLE_NAME=kc.TABLE_NAME
                                                                AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME
                                                                AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
           WHERE [email protected]
             AND [email protected]
           ORDER BY kc.ORDINAL_POSITION;
           DECLARE @pkCols VARCHAR(MAX)=
           STUFF
           (
           (
            SELECT 'UNION ALL SELECT ''' + pc.COLUMN_NAME + ''' AS [@name] , CAST(COALESCE(i.' + QUOTENAME(pc.COLUMN_NAME) + ',d.' + QUOTENAME(pc.COLUMN_NAME) + ') AS VARCHAR(MAX)) AS [@value] '
            FROM #tmpPKColumns AS pc
            ORDER BY pc.ORDINAL_POSITION
            FOR XML PATH('')
           ),1,16,'');
           DECLARE @pkColsCompare VARCHAR(MAX)=
           STUFF
           (
           (
            SELECT 'AND i.' + QUOTENAME(pc.COLUMN_NAME) + '=d.' + QUOTENAME(pc.COLUMN_NAME) 
            FROM #tmpPKColumns AS pc
            ORDER BY pc.ORDINAL_POSITION
            FOR XML PATH('')
           ),1,3,'');
           DECLARE @cols VARCHAR(MAX)=
           STUFF
           (
           (
            SELECT ',' + CASE WHEN @tp='upd' THEN 
                   'CASE WHEN (i.[' + COLUMN_NAME + ']!=d.[' + COLUMN_NAME + '] ' +
                   'OR (i.[' + COLUMN_NAME + '] IS NULL AND d.[' + COLUMN_NAME + '] IS NOT NULL) ' + 
                   'OR (i.['+ COLUMN_NAME + '] IS NOT NULL AND d.[' + COLUMN_NAME + '] IS NULL)) ' +
                   'THEN ' ELSE '' END +
                   '(SELECT ''' + COLUMN_NAME + ''' AS [@name]' + 
                                 CASE WHEN @tp IN ('upd','del') THEN ',ISNULL(CAST(d.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@old]' ELSE '' END + 
                                 CASE WHEN @tp IN ('ins','upd') THEN ',ISNULL(CAST(i.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@new] ' ELSE '' END + 
                          ' FOR XML PATH(''Column''),TYPE) ' + CASE WHEN @tp='upd' THEN 'END' ELSE '' END
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE [email protected] AND [email protected]
            FOR XML PATH('')
           ),1,1,''
           );
            DECLARE @cmd VARCHAR(MAX)=   
            'SET LANGUAGE ENGLISH;
            WITH ChangedColumns AS
            (
            SELECT   A.PK' +
                   ',A.PK.query(''data(/PK/Column/@value)'').value(''text()[1]'',''nvarchar(max)'') AS PKVals' +
                   ',Col.*  
            FROM #tmpInserted AS i
            FULL OUTER JOIN #tmpDeleted AS d ON ' + @pkColsCompare +
           ' CROSS APPLY
            (
                SELECT ' + @cols + ' 
                FOR XML PATH(''''),TYPE
            ) AS Col([Column])
            CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT ' + @pkCols + ') AS tbl FOR XML PATH(''Column''), ROOT(''PK''),TYPE)) AS A(PK)
            )
            INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content)
            SELECT  ''' + CAST(@AuditID AS VARCHAR(MAX)) + ''',''' + CONVERT(VARCHAR(MAX),@LogDate,126) + ''',''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
            ,(
            SELECT ''' + @tableSchema + ''' AS [@TableSchema]
                    ,''' + @tableName + ''' AS [@TableName]
                    ,''' + @tp + ''' AS [@ActionType]
            ,(
                SELECT ChangedColumns.PK AS [*]
                ,(
                SELECT x.[Column] AS [*],''''
                FROM ChangedColumns AS x 
                WHERE x.PKVals=ChangedColumns.PKVals
                FOR XML PATH(''Values''),TYPE
                )
                FROM ChangedColumns
                FOR XML PATH(''Row''),TYPE
                )
            FOR XML PATH(''Changes'')
            );';
            EXEC (@cmd);
           DROP TABLE #tmpInserted;
           DROP TABLE #tmpDeleted;
        END
        GO
    

    -現在让我们通過一些操作對其进行測試:

    UPDATE dbo.Testx SET Test1='New 1' WHERE ID1={d'2000-01-01'};
    UPDATE dbo.Testx SET Test1='New 1',Test2={d'2000-01-01'} ;
    DELETE FROM dbo.Testx WHERE ID1={d'2000-02-01'};
    DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect
    INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
     ({d'2000-03-01'},NEWID(),'Test3',{d'2001-03-03'})
    ,({d'2000-04-01'},NEWID(),'Test4',{d'2001-04-04'})
    ,({d'2000-05-01'},NEWID(),'Test5',{d'2001-05-05'});
    UPDATE dbo.Testx SET Test2=NULL; --all rows
    DELETE FROM dbo.Testx WHERE ID1 IN ({d'2000-02-01'},{d'2000-03-01'});
    GO
    

    -檢查最终狀態

    SELECT * FROM dbo.Testx;
    SELECT * FROM AuditTest;
    GO
    

    -清理( carefull with real data!

    DROP TABLE dbo.Testx;
    GO
    DROP TABLE dbo.AuditTest;
    GO
    

    插入的結果

    <Changes TableSchema="dbo" TableName="Testx" ActionType="ins">
      <Row>
        <PK>
          <Column name="ID1" value="May  1 2000 12:00AM" />
          <Column name="ID2" value="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
        </PK>
        <Values>
          <Column name="ID1" new="May  1 2000 12:00AM" />
          <Column name="ID2" new="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
          <Column name="Test1" new="Test5" />
          <Column name="Test2" new="May  5 2001 12:00AM" />
        </Values>
      </Row>
      <Row>
        <PK>
          <Column name="ID1" value="Apr  1 2000 12:00AM" />
          <Column name="ID2" value="28625CE7-9424-4FA6-AEDA-1E4853451655" />
        </PK>
        <Values>
          <Column name="ID1" new="Apr  1 2000 12:00AM" />
          <Column name="ID2" new="28625CE7-9424-4FA6-AEDA-1E4853451655" />
          <Column name="Test1" new="Test4" />
          <Column name="Test2" new="Apr  4 2001 12:00AM" />
        </Values>
      </Row>
      <Row>
        <PK>
          <Column name="ID1" value="Mar  1 2000 12:00AM" />
          <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
        </PK>
        <Values>
          <Column name="ID1" new="Mar  1 2000 12:00AM" />
          <Column name="ID2" new="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
          <Column name="Test1" new="Test3" />
          <Column name="Test2" new="Mar  3 2001 12:00AM" />
        </Values>
      </Row>
    </Changes>
    

    selective 更新的結果

    <Changes TableSchema="dbo" TableName="Testx" ActionType="upd">
      <Row>
        <PK>
          <Column name="ID1" value="Feb  1 2000 12:00AM" />
          <Column name="ID2" value="D7AB263A-EEFC-47DB-A6BB-A559FE8F2119" />
        </PK>
        <Values>
          <Column name="Test1" old="Test2" new="New 1" />
          <Column name="Test2" old="Feb  2 2002 12:00AM" new="Jan  1 2000 12:00AM" />
        </Values>
      </Row>
      <Row>
        <PK>
          <Column name="ID1" value="Jan  1 2000 12:00AM" />
          <Column name="ID2" value="318C0A66-8833-4F03-BCEF-7AB78C91704F" />
        </PK>
        <Values>
          <Column name="Test2" old="##NULL##" new="Jan  1 2000 12:00AM" />
        </Values>
      </Row>
    </Changes>
    

    删除結果

    <Changes TableSchema="dbo" TableName="Testx" ActionType="del">
      <Row>
        <PK>
          <Column name="ID1" value="Mar  1 2000 12:00AM" />
          <Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
        </PK>
        <Values>
          <Column name="ID1" old="Mar  1 2000 12:00AM" />
          <Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
          <Column name="Test1" old="Test3" />
          <Column name="Test2" old="##NULL##" />
        </Values>
      </Row>
    </Changes>
    

  • 6月前
    5 #

    它看起来很簡單,並且應该很好地工作,直到表中包含image / varbinary等元素為止 您有整个旧記錄和整个新記錄作為xml。 應该也可以正常工作,以便批量插入多列。

    CREATE TABLE _AuditTable
    (Aud_Id int identity(1,1) primary key,
    Aud_TableName varchar(100), 
    Aud_ActionType char(1),
    Aud_Username varchar(100),
    Aud_OLDValues xml, 
    Aud_NEWValues xml,
    Aud_OperationDate datetime DEFAULT GETDATE()
    )
    

    並觸發代碼

    CREATE TRIGGER _test2_InsertUpdate on _test2
    FOR INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
     IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) 
        RETURN;
    declare @tablename varchar(100)
    SELECT @tablename = OBJECT_NAME(parent_object_id) 
                 FROM sys.objects 
                 WHERE sys.objects.name = OBJECT_NAME(@@PROCID)
    /*Action*/
    DECLARE @ActionType char(1)
    IF EXISTS (SELECT * FROM inserted)
           IF EXISTS (SELECT * FROM deleted)
                   SELECT @ActionType = 'U'
           ELSE
                   SELECT @ActionType = 'I'
    ELSE
           SELECT @ActionType = 'D'
    declare @inserted xml, @deleted xml 
    SET @inserted = (SELECT * FROM inserted FOR XML PATH)
    SET @deleted = (SELECT * FROM deleted FOR XML PATH)
                 INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues)
                 SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted
    END
    

    輸出

    Aud_Id | Aud_TableName  | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues |   Aud_OperationDate
    1      |_test2          |   I            |abc\mR        |   NULL        |<row><name>abc</name></row> |  2018-11-07 12:38:34.937
    

  • python:重複numpy陣列的每个元素5次
  • scala:从Spark DataFrame删除巢狀的列