Audit trail - referential integrity on deleted records
Audit trail - referential integrity on deleted records
(OP)
Hi,
I'm building a new app, and want an audit trail table.
I have a CPD_Log table and when anyone adds, edits, uploads, deletes a records or file I want the CPD_Audit to record the user's action.
However, how can this work if you allow deletion of a CPD_Log record?
The Audit table has an FK to the CPD_log table, I can't insert an 'deleted' action in the audit trail table that links to a non-existent CPD_Log record.
The two possible ways of doing this I have thought of is ...
1. Let it be a logical but not physical FK so no referential integrity is maintained, however that would leave orphaned records in the Audit table that shows a history of actions against a non-existent CPD_Log record!
2. Don't actually delete the record, instead have a deleted column (bit) , so the record is hidden in the app, but exists behind the scenes to maintain audit trail referential integrity.
What do you guys do to maintain audit trail against deleted records?
Thanks,
1DMF
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
I'm building a new app, and want an audit trail table.
I have a CPD_Log table and when anyone adds, edits, uploads, deletes a records or file I want the CPD_Audit to record the user's action.
However, how can this work if you allow deletion of a CPD_Log record?
The Audit table has an FK to the CPD_log table, I can't insert an 'deleted' action in the audit trail table that links to a non-existent CPD_Log record.
The two possible ways of doing this I have thought of is ...
1. Let it be a logical but not physical FK so no referential integrity is maintained, however that would leave orphaned records in the Audit table that shows a history of actions against a non-existent CPD_Log record!
2. Don't actually delete the record, instead have a deleted column (bit) , so the record is hidden in the app, but exists behind the scenes to maintain audit trail referential integrity.
What do you guys do to maintain audit trail against deleted records?
Thanks,
1DMF
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
RE: Audit trail - referential integrity on deleted records
==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)
RE: Audit trail - referential integrity on deleted records
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
RE: Audit trail - referential integrity on deleted records
Have fun.
---- Andy
A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
RE: Audit trail - referential integrity on deleted records
But as admin will be creating records on behalf of many users, the likely hood they cock up is quite big!
What I don't want is a ton of junk deleted records, hmmm, to Audit or not to Audit that is the question!
Perhaps I allow delete and cascade to the Audit table.
If they delete do we care? It's only CPD!
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
RE: Audit trail - referential integrity on deleted records
I do. (sometimes)
I write some information into a simple CSV file (so I don't care here about data integrity here) and deleting records is a part of that. I had users call me and ask: "What happened to my information?" So I look it up and say: "YOU deleted them last Friday just after lunch, at 1:02:45pm".
Sometimes they ask another question: "Could you give me a warning before delete?"
My response: "I already do that. You get the message 'Are you sure you want to delete it?'"
User: "Hmmmm.. Could you give me two warnings...?"
Have fun.
---- Andy
A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
RE: Audit trail - referential integrity on deleted records
I also have lots of 'are you sure' vbyesno msgbox questions attached to most functionality.
We call them 'Jackie Buttons' after an employee who just clicked stuff and never read messages, so I had to put 'Are you really sure' on everything!
Users drive you nuts!
I think I'll go with the 'Active' Boolean (Bit) flag, that way I could re-instate accidentally delete records , I can see the support calls now... broker contacting me 'Where's my CPD log gone' ... me.. 'err, your administrator XYZ deleted it on XYZ at XYZ - would you like me to recover it'... 'yes please oh your amazing'
"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music
RE: Audit trail - referential integrity on deleted records
Or you can say: "Aren't you really un-sure that you do not want to Delete it?" Yes/No/Maybe (custom message box)
Have fun.
---- Andy
A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
RE: Audit trail - referential integrity on deleted records
==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)