--- In firebird-support@yahoogroups.com, "william_yuwei"
<william_yuwei@y...> wrote:

> Hi, All
>
> The following query is very simple:
>
> SELECT COUNTRY_CODE, COUNTRY_NAME FROM COUNTRIES
> ORDER BY COUNTRY_NAME
>
> However, I need to add a extra field, DELETABLE, the field which
> indicate if the country record has been used/referenced by records

in

> 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).

Adam