Thread: trigger for history data
04-18-08, 12:16 #1Registered User
- Join Date
- Oct 2007
Unanswered: trigger for history data
hi there all
i want to delete a record from my table and that table should be saved in another historical table...so to do this ..is by using trigger..
04-18-08, 17:34 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
Or in english: YES.The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
04-19-08, 05:46 #3Lost Boy
Provided Answers: 4
- Join Date
- Jan 2004
- Croatia, Europe
There's another way (which doesn't require additional (history) table) - alter existing table in order to add a column (let's call it CB_HISTORY (CB as "checkbox")). Its default value might be NULL or 0, but if there's 1 in there, it means that this record has been "deleted" (virtually; you wouldn't really do that, but only UPDATE the record setting CB_HISTORY = 1).
Or, if you want to do that using a database trigger and a history table, why not? Feel free to start coding. Come back when you find yourself in problems.
04-20-08, 16:10 #4Registered User
- Join Date
- Oct 2002
- Cape Town, South Africa
Carefull! There is a problem with using a delete "flag" on a record... You need to decide how to sort out child relationships (mark them deleted too? not allow the user to delete a record that has children? What about other foreign key relationships?).
My experience recently has made me decide to never use a delete "flag" on records. Essentially the system (from which I gained this learning experience) was written using delete "flags" and a user deleted a value from a lookup list. Of course the db did not give any errors stating that child records existed because the record was not being deleted, but only updating of the delete "flag" field. When the user tried to open a record that was related to the "deleted" lookup value, the software gave an error because the value was not in the lookup list (the developer obviously coded that logic into the front end). The software crashed.
There are ways of getting around all the problems with delete "flags", but I am starting to find that the better way is to move it to another table. Either way you choose to do it, you are going to need some coding. At least with moving it to another table you could auto generate the code.
I think you need to look at why you are keeping the history in order to determine what the best course of action should be.
1) I think most of the time, the history is kept as an audit trail. In this case, copy it to another table. You will keep your application running faster (less records in table) and keep it a lot less complex (don't need to remember to exclude the deleted rows every time you select). Also, make this something that happens behind the scenes and make it the databases responsiblity. That way your front end remains simple and if someone does get into your db without going through a front end, your audit trail will still do it's job.
2) Users want to see the history of an object. In this case investigate temporal database design. The delete "flag" will not give you "lifetime" information. With a temporal design, you would be able to determine when a change was made, what was changed and how long it remained in that state before another change was made.
3) Lookups that change frequently. I generally find that this type of "lookup" is actually a categorization for some data. In this case 9/10 you should be copying the actual value from the lookup into the data table. This may seem counter intuitive because of what you are always taught regarding normalization, but in reality it sorts out the problem of foreign key relationships and an ever changing lookup list. Sometimes, the right way is to denormalize here and there. If I think about it, this mostly occurs in reporting and BI applications where companies are looking at different groups of data, so denormalization is often a good thing in those environments.
Well, thats my opinion....