Audit Trial For changed Column in perticular Row Only.

 In this post I am Going to explain a method to get the changes in perticular cell which will be saved transaction wise for audit in future for unnecessary/unwanted changes.
Since this method only captures changed cells and its new and old value the data in auditTrial Table will not increase very rapidly and will be precise for changes only.
Another thing to be noticed is that it saves transaction_ID which will make it easier for recovery of unwanted transaction in single query.

Here is the Create Statement for the AuditTrial table that will be used for the puropse of saving Modified data.


GO

/****** Object:  Table [dbo].[AuditTrial]    Script Date: 12/16/2013 3:03:55 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AuditTrial](
    [AuditTrialID] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [varchar](50) NOT NULL,
    [ColumnName] [varchar](50) NOT NULL,
    [OldValue] [nvarchar](max) NULL,
    [NewValue] [nvarchar](max) NULL,
    [ModifiedBy] [int] NULL,
    [ModifiedDate] [datetime] NOT NULL,
    [Status] [char](1) NOT NULL,
    [RowID] [int] NULL,
    [IDRowName] [varchar](50) NULL,
    [TransactionID] [int] NULL,
 CONSTRAINT [PK_AuditTrial] PRIMARY KEY CLUSTERED
(
    [AuditTrialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AuditTrial] ADD  CONSTRAINT [DF_AuditTrial_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO




Now as we have created our audit trail table now we need a trigger in each table that needs to be audited for modification.
The sql below is the create statement of the Trigger for the table in this case it is tbl_budgetmaster that needs to be audited. Here in this procedure Inserted and Deleted table are first stored in temporary tables then compared each columns for changes in loop. If there is change in old and new value, the Column along with first column's which is supposed to be the primary key is stored as rowID and the first Columns Name is stored as IDRowName.

----------------For all the tables needs to be audited-----------------------------------------
GO
/****** Object:  Trigger [dbo].[audit]    Script Date: 12/16/2013 1:46:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Gautam>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
Create TRIGGER [dbo].[audit]
   ON  [dbo].[tbl_BudgetMaster]---tableName
   AFTER  DELETE,UPDATE
AS
BEGIN
   



Declare @Row nvarchar(150),@RowID int=0,@IDRowname varchar(50)
select @IDRowname=column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster' and Ordinal_Position=1;

 Declare Row Cursor for
 select column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster';
--select name from  dbo.GetTableColumn('tbl_budgetmaster');
open Row;

FETCH NEXT FROM Row into @Row 
WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE @OldValue NVARCHAR(max),@NewValue NVARCHAR(max),@ModifiedBy INT,@SQL nvarchar(max)

SELECT * INTO #MYINSERTED FROM INSERTED
SELECT * INTO #MYdeleted FROM deleted
   
    Set @Sql= 'select @OldValue= '+@Row+'   FROM #MYdeleted '
      EXEC sp_executesql @Sql, N'@OldValue nvarchar(500) OUTPUT', @OldValue OUTPUT
     
    Set @Sql= 'select @RowID= '+@IDRowname+'   FROM #MYdeleted '
      EXEC sp_executesql @Sql, N'@RowID int OUTPUT', @RowID OUTPUT
  
    Set @Sql= 'select @NewValue= '+@Row+'   FROM #MYINSERTED '
      EXEC sp_executesql @Sql, N'@NewValue nvarchar(500) OUTPUT', @NewValue OUTPUT
    --set @NewValue=(select  QUOTENAME(@Row)   FROM #MYINSERTED    );
    DROP TABLE #MYINSERTED
    DROP TABLE #MYdeleted
if((@NewValue<>@OldValue ) or (@NewValue is NULL and @OldValue is not NULL) or (@NewValue is not NULL and @OldValue is NULL)  )
BEGIN



insert into audittrial(TableName,ColumnName,OldValue,NewValue,ModifiedBy ,Status,RowID,IDRowName,TransactionID )
    VALUES( 'tbl_budgetmaster',@Row,@OldValue,@NewValue,@ModifiedBy,'U',@RowID ,@IDRowname,((select transaction_id from sys.dm_tran_current_transaction)) );

END

 FETCH NEXT FROM Row into @Row

END
 Close Row
Deallocate Row

END


The Audit Trial table looks like this: