Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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

This produces XML data like:

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" 

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?

share|improve this question
    
Plz give any suggestion or other example for this. –  user38790 May 22 at 8:47
    
< 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" > how can i display this in Gridview. this is present in the cloumn of database table, im not able to show it into gridview. –  user38790 Jun 2 at 7:33

1 Answer 1

up vote 0 down vote accepted

You can catch values for every inserted column in a row and save them into the table where you save other audit data.

There are third party tools, such as ApexSQL Audit, that create such triggers, captures data (for UPDATEs both old and new) and shows reports.

When a row is inserted into a table with the following columns:

enter image description here

All column values are shown as separate columns

enter image description here

You can find more useful info here: Auditing triggers in SQL Server databases

Disclaimer: I work for ApexSQL as a Support engineer

share|improve this answer
    
can i make this report with my own. can you tell me which procedure , query i have to use to make this. –  user38790 May 26 at 5:32
    
You should use the inserted, deleted, and updated tables. "DML triggers use the deleted and inserted logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action." You can find the complete explanation and examples here: msdn.microsoft.com/en-us/library/ms189799.aspx –  Milena Petrovic May 27 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.