create log for table to capture changes by users

[megatronixs]

Hi all,

Is there a way of creating a log to capture changes in a table by users?
In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.
So, if I would be able to have a log that recores all the actions taken by users, I could at least see who did what.
Any ideas to get me started and some links?

Greetings.

 

[Mile-O]

It's going to take a lot of coding, most likely in a Class Module, to create some sort of in-app audit trail.

First things first, is how it was possible to delete records. Do users have direct access to the tables? If so, nip that in the bud.

On my tables I add fields for DateCreated, UserCreated, DateUpdated, UserUpdated, DateExpired, and UserExpired. Rather than delete records I update the DateExpired so that it is still on record, but not in use within the database.

On the audit trail, I suppose you'd need to think about what elements you want to capture, how detailed you want it, etc

It really does seem - via this post and all the others - that you are going through this database design phase without any concrete plan of what you are building (save for a few data entry firms and reports), if ideas are occurring as you go. Walk, don't run. With some planning, you can go farther.

 

[megatronixs]

Hi,
Thanks for the info.
I took over a database (well, almost till the actual hand over). There are some points open to add more improvements and some things that I found useful. So, now I go through all the point and try to implement them. As my knowledge is not the best ever, but can find somehow my way, they decided to have me in the team.
It is a lot of work and I'm still learning.
After the unexpected data loss, I will need to implement something that will catch what happened and it would be also good if I could have a duplicte table in a hidden folder that gets info from the main table, but the records there are not able to be deleted.
Are you aware if that is possible?

Greetings.

 

[Mile-O]

Browsing another thread, I've just seen a link to this page

http://allenbrowne.com/appaudit.html

 

[megatronixs]

Hi Mile-o,

I will try to study it and put it to work (guess it will take some time)

Greetings

 

[gemma-the-husky]

the best way going forward is

a) prevent users seeing tables directly
b) limit deletes very carefully. generally, set a "deleted/dead item" flag rather than delete records. Then there won't be any deletions.
c) never set cascading updates/deletes, and do set RI on tables where possible, to prevent orphans

 

[megatronixs]

Hi all,

Big thanks for your input. I will try to work something out to avoid data loss in the future.


Greetings.

 

[gemma-the-husky]

just a thought.

whatever audit processes you have, anyone opening tables and editing directly in a table will bypass all of the audit processes. you just cannot let users edit directly in tables with impunity.

 

[spikepl]

I second Dave's last post. Meaning - before you get into a lot of coding, thinking that an audit trail can cure al the ills, THINK!

 

[RainLover]

I always ask this question because I used to track some of these items.

What are you going to do with the information when you have it. I believe you will simply cause a rift between users.

Your efforts may be best served by preventing wrong doings rather than laying blame.

 

[megatronixs]

Hi RainLover,

There are no intentions to blame one, just to figure out if there where no human factors in the deleted data (I'm new in my team and dont want to create bad atmosphere).
I was hoping that by making a "ghost copy" we could keep data save if one with bad intentions found the back end database.
It is something that I will need to think out and think about all possible scenarios.

Thank you all for your comments on this. Once I have a better idea of what I want to do, I will get back with this.

Greetings.

 

[RainLover]

It would be unusual for someone to deliberately do harm. Try thinking on a positive vein. Aim to look at ways of preventing or better still recovering if something does not go the way it should.
You could add 4 new fields on each table which would give you some history.
DateCreated
CreatedBy
DateUpdated
Updated by
These may help but don’t expect too much.
Post back if you need help with the code. Also what version you are using.

 

[RainLover]

It would be unusual for someone to deliberately do harm. Try thinking on a positive vein. Aim to look at ways of preventing or better still recovering if something does not go the way it should.
You could add 4 new fields on each table which would give you some history.
DateCreated
CreatedBy
DateUpdated
Updated by
These may help but don’t expect too much.
Post back if you need help with the code. Also what version you are using.

 

[megatronixs]

Hi all,

We are moving our database to Oracle, so for now I only need it to keep working and add some small functionality. I created the solution that you mention RainLover and it is great to have this log

Greetings.