Click here to Skip to main content
15,112,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi,

im new to .net..i want to know how to store history of data changes that key in by user.lets say they key in today work progress, then tomorrow they key in again to update status.how to store that history so that their manager can view it later

thanks in advance,
musiw.
Posted

Create an update trigger in your SQLDatabase and store the changes in a different table with the primarykey of your base table as a FK.Triggers -- Sql Server[^]will be useful for you.
   
v2
Comments
musiw 9-Aug-12 2:27am
   
i tried your solution. first i insert values then when i run trigger, i check audit table no data display.am i missing somewhere

insert into task_status (title_task,pic,start_date,due_date,status_remarks) values('fgh','aku',getdate(),getdate(),'la')

CREATE TRIGGER trgAfterInsert ON [dbo].[task_status]
FOR INSERT
AS
declare @ID int;
declare @task varchar(1000);
declare @status varchar(1000);
declare @audit_action varchar(100);

select @ID=i.id from inserted i;
select @task=i.title_task from inserted i;
select @status=i.status_remarks from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into task_status_audit
(id,title_task,status_remarks,audit_action,audit_date)
values(@ID,@task,@status,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'
GO
musiw 9-Aug-12 2:35am
   
sorry..after couple of try, it works..hehe..i just build testing table.for real one i need to explore the code more.thanks!
musiw 9-Aug-12 3:08am
   
after i try trigger for delete..it is not like the example.from example it is shows data still there in audit table.but mine it shows null in audit table
   
Can you be more clear about your question....
musiw 13-Aug-12 23:53pm
   
sorry for the late reply.lets say when i delete task from table.then it will run delete trigger right.ok then when i look at task_audit table, the data that has been deleted before display null, but in audit_action column display 'DELETE trigger fired' means the task is successfully deleted.ok fine.but is it suppose the deleted data insert into audit table?so that we know which task is deleted.
Audit Trail (Data Activity Monitoring) is a technique through which we can record all user’s activity on the database.

Please refer:
log4net Tutorial[^], This CP Article will give you demonstration of easy & flexible use of Log4net.

Also have a look on:
Create Simple Error Log Files using ASP.NET and C#[^]
This article demonstrates how to create a simple text based log file to log error messages.

How to use log4net[^]
This article describes the basic steps to be followed for using Log4net in a web application.

Configure Log4Net in ASP.NET 2.0[^]
This article explains how to configure Log4Net in an ASP.NET application.

For Database side, In CP you can find many articles. Here you go
Audit Trail Generator for Microsoft SQL[^]
Implementing Audit Trail using Trigger[^]
Auditing events on SQL Server 2008[^]
Audit Trail – Tracing Data Changes in Database[^]
Audits on MS SQL Server 2008[^]

For application side you may use the below one
Five methods to Logging[^]
   
Hi,

Although Solution 1 works for you as you are using SQLServer2005. But if you are planning for SQL Server 2008 then you should check Change Tracking in SQL Server 2008[^]. it will store high level of change history. you do not have to write anymore code.

Anyway hope you will create triggers for your tables.

Thanks
-Amit Gajjar.
   
dasf dsafsd
C#
public static void main
Response.Write("Error Message" + EX.exception);
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900