-
Marking deleted rows in a database table
In one of our applications, there is a requirement that data not be permanently deleted (or at least not initially).
The original way I implemented this was to add a DELETED bit column to each table, and if that column was set to 1 (true), then I would treat that row as if it didn’t exist.
This is achieved through ensuring access is via a View that excluded those rows, stored procedures, and also adding an INSTEAD OF DELETE trigger that intercepted regular DELETEs and converted them into UPDATEs to flip the column bit.
That works pretty well, but I’m trying to weigh it up against the other approach I can think of, which is to have a separate table that only holds the deleted rows.
eg. if the original table was FRED, then the other table could be named DELETED_FRED (just to be clear what it was for!).
I’d guess you could still use a trigger to catch any deletes to table FRED, and use them to copy the deleted row over to DELETED_FRED.
I suspect that the latter approach probably makes it easier to use some kind of ORM framework, as you don’t have to be careful about avoiding the deleted rows.
The advantage of the former though is that you maintain your referential integrity for all of your data. Because of the relationships of other tables besides FRED, I don’t think I could do this with the DELETED_* tables.
I’m curious as to what other pros and cons are of these approaches, and whether there is a formal name for this kind of thing?
There are some resources I have found:
-
Installing Roxio Creator Plus on Vista
Up until yesterday, I’ve resisted installing the OEM version of Roxio that came bundled with my Dell Optiplex GX620 workstation, as I found the combination of the inbuilt CD/DVD burning along with ISO Recorder handled all my requirements.
I was putting together a backup of my Documents and latest PST file, and there must be something funny about the files, but both the latter solutions croaked on them.
The problem with installing Roxio is that the installer blows up with an error saying you must be an administrator to install the product.
In desperation, I followed these steps to enable the builtin Administrator account (it is disabled by default on a clean install of Vista). I did have to update the local policy too.
I then logged in as the Administrator account (using runas didn’t work), and the install proceeded happily.
The Roxio media has a date of 2005, and the “About this Software” dialog lists the following versions:
Module Name: Roxio Creator Plus - Dell Edition Build: 2.4.32a Serial Number: SC-204B28D Copyright: Sonic Solutions 2005
Module Name: PX Engine Build: 2.2.54a, 68 Copyright: Sonic Solutions 2004
Module Name: Roxio RecordNow Audio Version: 2.0.4 Build: 204B28C, RCP Serial Number: CQDKR2PD6YT5KBYHS Copyright: Sonic Solutions 2004
Module Name: Home Copyright: Sonic Solutions 2004
Module Name: Roxio RecordNow Data Version: 2.0.4 Build: 204B37C, RCP Serial Number: CEEWK9329SNNFA84H Copyright: Sonic Solutions 2004
Module Name: Launch Backup Build: 204B37C, RCP Copyright: Sonic Solutions 2004
Module Name: Roxio RecordNow Copy Version: 2.0.4 Build: 204B28D, RCP Serial Number: CUY8FVJQ32XQJBRJB Copyright: Sonic Solutions 2004
Even though it is a few years old, the good news is that it was able to burn the files successfully.
I also tried running the “Check for Updates..” option, but either there are no updates or the updater can’t find it’s way through our proxy here at work.
-
Replacement iPAQ
As I mentioned previously, my iPAQ has been sent off to get its audio fixed.
I thought I might borrow this from my daughter as a temporary replacement.It doesn’t have any telephony functionality, but you can write notes on it, and most importantly play a version of “Pop the Balloons” (aka the Balloon Breaker game).
Battery life is very good too :-)
A blog of software development, .NET and other interesting things
