December 7, 2004 at 11:41 pm
Dear all,
I'm developing an internet application that uses MS-SQL server as a database.
The database contains customer information. The customer is able to create, update and detele records via forms in the application.
When a customer deletes a record I want to keep some history of the deleted record.
For the moment when a customer deletes a record the record is in fact not deleted but I update a field called "enddate" which I fill with the sysemdate.
Next time when the customer reads the information the select query will not select the "deleted" records because in my query I put "where enddate is null."
Would it be better to work with tables that have the same naming as my real data table but with an extention _HIST ?
So when a customer deletes a record it is actually deleted but via triggers inserted in the table with extention _HIST …
It concerns about 20 tables … (This will result in 20 history tables)
Any other suggestions to implement some history tracking ??
December 8, 2004 at 1:08 am
I would choose to have this 20 additional tables. That way you are not required to maybe put additional logic in your queries and it seems more natural and logical to me to have this "deleted" rows in an extra table separated from the "live" rows.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 8, 2004 at 5:08 am
I agree with Frank. Another reason for the history tables is these rows are no longer reported on and over time I am assuming that these rows MAY outrank the LIVE rows and will cause all sorts of slowdowns.
IF you have the chance to do it now take it. You will feel the pain later
Good Hunting!
AJ Ahrens
webmaster@kritter.net
December 9, 2004 at 7:01 am
I ran into this need a couple years back and did the exact thing you mention - a trigger for delete events on the appropriate tables. My issue at the time was because of a rogue query iterating through a few tables and deleting a few thousand records before stopping.
This method allowed an easy way to debug what the similarities were between the records and also enabled me to select them back into the original table.
Forge ahead - you're on a good track.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply