Re: cancel an insert within the trigger?

In article <4151842F.6C1B_at_yahoo.com>, connor_mcdonald_at_yahoo.com wrote:
>Andre wrote:
>>
>> Hello all,
>>
>> I have created an insert trigger:
>> If I do a count on the table and this count results in a 1 or higher I
>> would not insert the record. (of_id is not the primary key)
>>
>> CREATE OR REPLACE TRIGGER iu_cli BEFORE insert or update on client
>> FOR EACH ROW
>> BEGIN
>> CC number;
>> IF INSERTING
>> THEN
>> select count(*) into CC from client WHERE of_id = :new.of_id;
>> If CC > 1 then
>> --do not insert?
>> ????????????
>> end if;
>> END IF;
>> END;
>> /
>>
>> The Question is: Can I do this this way?
>> What code to put on the ???? marks?
>>
>> Thanks All,
>> Anneke
>
>You can't do the trigger this way...it won't work on a multi-user system
>
>hth
>connor

Conner, you should also explain WHY it won't work.

Andre, the "select count(*)" won't help you eliminate duplication of of_id (which seems to be the point of the trigger) because you cannot be certain that another uncommited transaction hasn't already inserted (or will insert before your transaction commits) that of_id, which your select statement would never see. Although you CAN cancel an insert by raising an exception in a before trigger, this will not accomplish what you're wanting.

The answer is to simply create a unique constraint on the of_id column.

If this has already been answered and is old news, sorry. Received on Thu Sep 23 2004 - 07:50:48 CDT