觸發審計表更改

我創建了一個觸發器來審核對錶的更改：

CREATE TRIGGER [dbo].[iudt_AutoAuditChanges] 
  ON  dbo.CPTCategoryMaster
  AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   Declare @v_AuditID bigint

   IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
       CREATE TABLE [dbo].[AutoAudit]
       (   [AuditID] bigint identity,
           [AuditDate] DateTime,
           [AuditUserName] varchar(128),
           [TableName] varchar(128) NULL,
           [OldContent] XML NULL,
           [NewContent] XML NULL
       )

       ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
       PK_AutoAudit PRIMARY KEY CLUSTERED 
       (
           [AuditID]
       ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

       CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit] 
       (   [TableName] ASC,
           [AuditDate] ASC
       )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   END

   Select * Into #AuditDeleted from deleted
   Select * Into #AuditInserted from inserted

   While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
   Begin

       INSERT INTO [dbo].[AutoAudit]
           ( [AuditDate], [AuditUserName], [TableName], [OldContent], [NewContent])
       SELECT 
           GETDATE(),
           SUSER_NAME(),
           [TableName]=object_name([parent_obj]),
           [OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
           [NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
       FROM sysobjects 
       WHERE
           [xtype] = 'tr' 
           and [name] = OBJECT_NAME(@@PROCID)

       Set @v_AuditID = SCOPE_IDENTITY()

       Delete from AutoAudit
       Where AuditID = @v_AuditID
           AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)

       Delete top(1) from #AuditDeleted
       Delete top(1) from #AuditInserted

   End
END

這會產生如下 XML 數據：

row CPTCategoryId="1" CPTCategoryName="NEW CHARGE" Remarks="" Status="1" CreatedBy="11" CreatedDate="2014-05-01T12:50:16.947" ModifiedBy="11" ModifiedDate="2014-05-01T14:20:47.793" LockVersion="0" 

如何分隔列數據（CPTCategoryId, CPTCategoryName, Remarks…等）以在網格視圖中顯示？或者有沒有更好的方法來實現這個要求？

您可以擷取一行中每個插入列的值，並將它們保存到您保存其他審計數據的表中。

有第三方工具，例如ApexSQL Audit，可以創建此類觸發器、擷取數據（用於新舊更新）並顯示報告。

當一行插入到具有以下列的表中時：

所有列值都顯示為單獨的列

您可以在此處找到更多有用的資訊：審計 SQL Server 數據庫中的觸發器

免責聲明：我作為支持工程師在 ApexSQL 工作

引用自：https://dba.stackexchange.com/questions/65644