transaction log

[mguwc]

Is there any way to track changes to a record in a table.

I have a form called frmCaller, recordsource tblCaller
The fields are:
CallerID
Name
Address
City
etc, etc, etc
DateCreated
UserIDCreated
DateChanged
UserIDChanged

I would like to have a table that would track the changes made to these "already-created" records. I have created a table called tblChanges. The fields are:

ChangesID
CallerID
UserIDChanged
DateChanged
ChangesMade

The last field [ChangesMade] should be a string made up of the changes....example address 19 main street update 20 broad street. If there are more than one field change to the table it would be as follows in a datasheet

ChangesID: 1
CallerID: 202
UserIDChanged: MariaG
DateChanged: 9/30/04 11:00:05
ChangesMade: Address 19 Main Street update 20 Broad Street

ChangesID: 2
CallerID: 202
UserIDChanged: MariaG
DateChanged: 9/30/04 11:00:53
ChangesMade: City Hartford update Newington

ChangesID: 3
CallerID: 202
UserIDChanged: MariaG
DateChanged: 9/30/04 11:01:15
ChangesMade: ZipCode 06114 update 06111

Then I would have the last DateChanged show up in the field that is in my tblCaller, but if someone would need the detail they could click on a button to get the Changes Log.

I think my idea is correct, I just don't know if the system does this automatically and I would just be rewriting this function.

Also, what code would I use to document the changes that are made?

- maria

 

[chiph]

Nothing will do this automatically.

Best practice would be to write stored procedures to do all your data changes through, and it can write out the "before" and "after" values, and "who made this change" to an audit table.

You would then set the DB permissions to only allow the stored procedure to write updates, delete, truncate, etc. to your tables, and not allow ordinary users to do it.

I've also seen people use DB triggers for this, and that works as long as you don't have too complex a schema.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first