At 05:11 AM 27/08/2005 +0000, you 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^

Not, it's fine - as long as the exception exists.

>The exception fires every time I try to delete a row from LEVELS,
>even when there's patently no JOBS entry using that LEVEL.

I just reconstructed your setup and it works exactly as you intended.

Do not follow the advice to do a SELECT FIRST query on the Jobs
table!! This is a total No-No in a trigger.

The check you are doing in this trigger is the right one.

The assessment that your exists() query would return true for every record
is also totally wrong. It will return true only if a JOBS record is found
where the value in JOBS.LVL matches the the value of LVL in the LEVELS row
currently under consideration, *** according to your transactions' view of
the JOBS table***.

Check up on whether you are deleting the JOBS record(s) in a different
transaction to the one where you are deleting the LEVELS record, and
whether the operation on LEVELS is in a concurrency transaction.

./heLen