--- In firebird-support@yahoogroups.com, "sdbeames" <s.beames@g...> wrote:

> G'day all,
>
> Is there anything wrong with this trigger....
>
> CREATE TRIGGER LEVELS_TG_BD FOR LEVELS
> BEFORE DELETE
> AS
> begin
> if (exists(select 1 from JOBS where LVL = old.LVL)) then
> exception CANT_DELETE_LEVEL;
> end^
>
> The exception fires every time I try to delete a row from LEVELS,
> even when there's patently no JOBS entry using that LEVEL.
>

Is it behaving as you expect?

What if another transaction has inserted a record into JOBS with
old.LVL that has not yet been committed? Your trigger will not be able
to see that record, when really you would need the exception to be
raised. This could be countered by running a dummy update on the
levels_tg table.

eg

update levels_tg set id = id where LVL = ?;
insert into jobs .....

This way, when your transaction goes to delete the record, it will
receive an exception because there is an uncommitted change to that
record.

Adam