Audit trail - referential integrity on deleted records 2

[1DMF]

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

 

[johnherman]

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)

 

[1DMF]

Yes so like I was thinking either a flag or as you say, I could create a union view, interesting idea.


"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

 

[Andrzejek]

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.

 

[1DMF]

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

 

[Andrzejek]

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

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.

 

[1DMF]

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

"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

 

[Andrzejek]

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

 

[johnherman]

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)