Subject Re: Need Good Advise for the SELECT query
Author Adam
--- In, "william_yuwei"
<william_yuwei@y...> wrote:
> Hi, All
> The following query is very simple:
> However, I need to add a extra field, DELETABLE, the field which
> indicate if the country record has been used/referenced by records
> other tables or not, so to use this field to show a state on the
> screen(Grids) visually. I have a lot of SELECT queries which want
to do
> the same way. Is there any good way and how to fetch it?
> Thanks

Hi William,

There are a number of things you can do. Firstly, check that you
really need this functionality, or whether you are just trying to
prevent users accidentally deleting countries that have records
relying on them.

I can definately recommend that you do NOT use calculated fields to
acomplish it, they are far too slow. You could create a field and
populate it using triggers, but I would be inclined to investigate
what you can do with the before delete trigger and raising exceptions
if there are matching records in the child tables. Also, I do not
know if it would work, but could you define the field in the child
table as not null, then in the foreign key definition use on delete
set null. I would hope this would raise an exception (but test it
first to make sure).