Perpetual Newbie

An Alternative to Cascade Deletes

by Matt Hamilton - MHamilton@bunge.com.au

If you're like me, you write a lot of database apps with "Master/Detail" relationships between tables. And if you're like me, you run into the problem of what to do when the user deletes a record out of the "master" table.

Generally there are two options:

1. Simply delete the record, and "orphan" any records in the detail table which refer to the one you're deleting.
2. Delete the detail records as well (known as "cascade deletes").

Well, in many cases neither of these options are good enough, so here's a third alternative:

3. Prompt the user to reassign the detail records to another master.

The idea makes sense if you're talking about, say, stock items in a location. If the user deletes the location, what happens to the stock? If a shelf is taken down in real life, the items on it don't "disappear" - they're moved to another shelf.

The first step towards cleanly prompting the user is to make a new TQuery, which will return all the "master" records except for the one we're about to delete. To do this, add a TDataSource to your project, and set its DataSet property to the table you are deleting from. Now add a TQuery, and set its DataSource property to the TDataSource we just created.

In my location/stock example, the SQL will look like this:

select *
from location
where LocationNum <> :LocationNum

Here, LocationNum is the primary key of the "location" table. Now we can use this query in a TDBLookupCombo, or even a TDBGrid, to prompt the user for a "destination" location. The dialog will ask a question like this:

There a stock items in this location! What would you like to do with them?

(o) Delete them
(o) Move them to: _________

The last thing to do is the actual move. This is easily done with another TQuery, whose SQL (in my example) will look something like this:

update stock
set LocationNum = :NewLocationNum
where LocationNum = :OldLocationNum

The two parameters are as follows:

OldLocationNum: The location we're about to delete
NewLocationNum: The location the user has selected from our query.

And there you have it! Now, when the user attempts to delete a "master" record, he is presented with the option of moving the "detail" records to another master! This gives the user a bit more flexibility, which (I'm sure we all agree) is a good thing.

Copyright © 2007 undu.com Powered by Engineer Partner The One Stop Outsource