Alan McDonald 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.
>>
>>Thanks,
>>Steve
>>FB 1.5.2.4731
>>
>>
>
>you'll always be able to select 1 from a table.
>Try
>select first 1 from JOBS
>Alan
>

Alan,
i think you are mistaken. There is a "where" clause that he is
certain should make the query return no rows, therefore, fail the
exists, therefore, not raise the exception.

Steve, the trigger looks fine to me.

As a sanity check ...
Did you try to force it with a script like...

Delete from JOBS where Lvl=<yourvalue>
commit;
delete from LEVEL where Lvl=<Yourvalue>

Perhaps because you are in different transactions making you think they
are not there, but are????

Otherwise, we have to be looking over something. Helen will no likely
set us straight if we are.



[Non-text portions of this message have been removed]