Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Audit trail - referential integrity on deleted records 2

Status
Not open for further replies.
1DMF

1DMF

Programmer
Jan 18, 2005
8,795
GB
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
 
Sort by date Sort by votes
Instead of physically deleting the record, you can have a separate field to record that the record is no longer active. Call it ActiveStatus or something like that. Active records have an 'A' (or blank), deleted records have a 'D'. This solves the referential integrity problem. Another option is to have a table of deleted records. When a record is deleted, write the deleted record to the "Delete" table before physically deleting it in the active table. You can have a view that lays over top of the Active and Deleted Tables to have all records to address the referential integrity issue. That is Create View ALL_TABLE as SELECT * from ACTIVE_TABLE UNION ALL SELECT * from DELETE_TABLE.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


 
Upvote 0 Downvote
Upvote 0 Downvote
If you introduce a field Status to your table (not null, default to 'A' Active), you may as well give the user (or an Admin) the possibility to 'un-delete' the record(s). :)

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.
 
Upvote 0 Downvote
Well I've even been toying with removing delete as a function.

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
 
Upvote 0 Downvote
If they delete do we care?"
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...?" [ponder]

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.
 
Upvote 0 Downvote
[lol] - yup, I have another audit trail in an app that is similar, so I can look up who edited , created, and deleted that is just text and no referential integrity.

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' [thumbsup2]

"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
 
Upvote 0 Downvote
'Jackie Buttons' - you can easily detect who is using you app and for this particular user you may run with several message boxes, and just one MsgBox for everyone else.

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.
 
Upvote 0 Downvote
The thing with deleted records is that someone could have written a report during the time that the record existed. If you physically delete the record, you can't reconcile to that report any longer. That's why most Data Warehouses do not physically delete, but rather "logically" delete the record using methods similar to what I outlined above. So, the question of "Do I care if they delete?" also ties to "How can I guarantee integrity of the data if I don't save the deleted records?".

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
942
SkipVought
S
monkeysee
  • Locked
  • Question
Field to count specific records
Replies
3
Views
650
dhookom
dhookom
03Explorer
  • Locked
  • Question
Refreshing a linked table (housed on MS SQL Server) now breaks
Replies
3
Views
580
dhookom
dhookom
BugZap13
  • Locked
  • Question
How to extract Access table schema with decimal places on numeric fields?
Replies
2
Views
4K
dhookom
dhookom
cortextual
  • Locked
  • Question
Referential integrity issue: no unique index found for the referenced field of the primary table
Replies
2
Views
660
MajP
MajP

Part and Inventory Search

Sponsor

Back
Top