--- In firebird-support@yahoogroups.com, "nols_smit" <nols@...> wrote:

>
> I have a BEFORE DELETE trigger on table UPDATERS, giving the user a
> better indication why RI will not allow him to delete a record, e.g.
>
> AS
> BEGIN
> /* Trigger body */
> if (exists(Select Updater_ID from Addresses where Updater_ID =
> Old.id)) then
> exception Code_ID 'Updater_ID in use in table Addresses';
>
> if (exists(Select Updater_ID from Geosites where Updater_ID =
> Old.id)) then
> exception Code_ID 'Updater_ID in use in table Geosites';
>
> if (exists(Select Updater_ID from Photos where Updater_ID =
> Old.id)) then
> exception Code_ID 'Updater_ID in use in table Photos';
>
> if (exists(Select Updater_ID from SiteJunction where Updater_ID =
> Old.id)) then
> exception Code_ID 'Updater_ID in use in table SiteJunction';
>
> END
>
>
> Is there a way I can modify this trigger so the user can get an
> indication which records are using the primary key of table
> UPDATERS as a foreign key?
>
> Something like
>
> if (exists(Select Updater_ID from Addresses where Updater_ID =
> Old.id)) then
> exception Code_ID 'Updater_ID in use in table Addresses, ID = '
> || Addresses.ID;
>

I don't know what you want to do if it is used by more than 1 record
in Addresses (if this is indeed possible), so I just display one of
them (the first one I encounter).

Something like this should work.

ID = NULL;

select first 1 ID
from Addresses
where Updater_ID = Old.id
into :ID;

IF (:ID IS NOT NULL) THEN
BEGIN
exception Code_ID 'Updater_ID in use in table Addresses, ID = ' || :ID;
END

Adam