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:

Temperory Table and Cursor and while loop to Fill and generate Custom report in Sql.

This Type of report are very useful in case of slow network. As all the customization of data takes place at Database server end. there is only one fetching of data from the database server hence less time needed to generate the report.

This Concept uses Temporary Table to Get the data from the different Transaction and detail table.  

The Temporary table have a scope of only equal to the scope of it Process id. When creating a local temporary table the table is persisited in the tempdb, but its scope is specific to the spid of the process that created it, ie. you can open up multiple query windows within sql server management studio and create a local temporary table,
CREATE TABLE #temp(
col1       INT

),

and this will not cause any issue as the table is specific to the process id.

 Cursor  gets the data (single column at a time) from some select statement which is stored in the local variable of same data type as select statement. For While loop.

Defining Cursor:

Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

....use the row data in statements here.

Close Row
Deallocate Row 

While Loop using data fetched from Cursor 

syntax:

 Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

FETCH NEXT FROM Row into @Row  

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #temp col1  values(@Row)

 FETCH NEXT FROM Row into @Row

END

 Close Row
Deallocate Row

The Complete Code Should Look Like:

CREATE TABLE #temp(
col1       INT
);

 Declare Row Cursor for
select distinct col2 from  tblname ;
open Row;

FETCH NEXT FROM Row into @Row  

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #temp col1  values(@Row)

 FETCH NEXT FROM Row into @Row

END

 Close Row
Deallocate Row
Drop table #temp -- Donot forget to clear un wanted temp data.