I make the Trigger function and apply over the table where i want to use the AUDIT or LOGs. the following is the Trigger which i used..

Then i get the Column in which the data is in XML format like this

so i am unable to seprate these column(CPTCategoryId,CPTCategoryName,Remarks... etc) so that i can so these into the Gridview.

So plz tell me how can i change or any edit in above code or suggest me how can i seprate the data. OR tell me another concepts of this Problem.

I have created a trigger to audit changes to a table:

This produces XML data like:

How can I separate the column data (CPTCategoryId, CPTCategoryName, Remarks... etc) for display in a grid view? Or is there a better way to implement this requirement?

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

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"

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

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"