I am doing an Audit trail on a table say Customer Table
Customer c1=new Customer(){name="customer1" ,job="IT",city="USA"};
Say if i am doing an update on customer table and I am updating Customer City to CANADA
c1.city="CANADA";
So what i have to do is Audit this in a table like,
CustomerID 1
fieldname city
oldvalue USA
Newvalue CANADA
and so on.... whtever field is updated.
My Question is ,say I have more than Fields, How can I implement this?
How can I compare OLD RECORD WITH UPDATED RECORD TO CHECK WHICH FIELD IS CHANGED.HOW CAN I CAPTURE THOSE CHANGED FIELDS AND SAVE THEM IN AUDIT TABLE
I wouldn't. Use a database trigger so that whenever an UPDATE is run on the table in question, the state of the row before the update is captured and put into an audit table.
What I have done in the past is used stored procedures to handle data transactions. In the stored procedure I would insert the row being changed to an audit table before updating the target table. The audit table, however, isn't the easiest to read because every time a user clicks the save button it will create an audit record, but it is accurate and far less code/complexity than comparing every single row value to see specifically "what" changed. For the number of times we had to go into the audit trail, it wasn't worth the extra effort of a column-level comparison. Our audit trial was more to capture changed data so we could revert it back if needed, although we captured the who and when also.
