×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Audit trail - referential integrity on deleted records
2

thread700-1765249

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

RE: Audit trail - referential integrity on deleted records

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)


RE: Audit trail - referential integrity on deleted records

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). smile

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

(OP)
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

RE: Audit trail - referential integrity on deleted records

"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.

RE: Audit trail - referential integrity on deleted records

(OP)
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

RE: Audit trail - referential integrity on deleted records

'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.

RE: Audit trail - referential integrity on deleted records

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)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close