Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: cancel an insert within the trigger?
In article <ciugr8$qrr$1_at_cronkite.cc.uga.edu>, jeff_at_work.com (Jeff) wrote:
>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
>The answer is to simply create a unique constraint on the of_id column.
Also, you say you wanted to "not insert" if the "count results in a 1 or higher," but (if the trigger could've worked at all) the checking of "cc > 1" still would've allowed 1 duplicate. Must've meant "cc >= 1", yes? :-) Received on Thu Sep 23 2004 - 08:03:24 CDT