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

>
> Thanks for the help. This is working fine:
>
> /* Trigger body */
> select first 1 ID from addresses where updater_id = old.id into

Key_ID;

> if (Key_ID is not null) then
> Exception Code_ID 'Updater ID in use in table Addresses where

ID = ' ||

> Key_ID;
>
> The text of the exception Code_ID is: "Code in use"
>
> When the exception is raised, I get:
>
> Exeption 1
> Updater ID in use in table Addresses where ID = 1
>
>
> Shouldn't I get:
>
>
>
> Code in use
> Updater ID in use in table Addresses where ID = 1

No, that is not how it behaves. You can specify an optional message to
go with the exception, but the code is Exception1 or whatever it is
called.

Perhaps a better approach than to customise each message is to store
in a table each exception and the plain english (or whatever language)
explanation for the exception. For example, if it fails to delete
because of a violation in tableB, then you can simply read the FK to
this table and lookup the information of the specific tableB record.

As Dimitry pointed out, this mechanism while it should catch 99% of
cases, due to isolation limitations, you may not be able to see the
record that is referenced. For example, if moments before you delete
the record another transaction (perhaps another user) adds a record
that references the one you are about to delete but has yet to commit,
then the delete will fail, but the lookup will not be able to show the
record until they commit.

Adam