Andre wrote:
> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<slrncl0j67.28k.rene.nyffenegger_at_zhnt60m34.netarchitects.com>...
>
>>In article <f858471c.0409210721.3a828218_at_posting.google.com>, Ben Graham wrote:
>>
>>>avanrossem_at_hotmail.com (Andre) wrote in message news:<4d32d1be.0409210219.26642641_at_posting.google.com>...
>>>
>>>>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
>>>DECLARE
>>>CC number;
>>>BEGIN
>>> IF INSERTING
>>> THEN
>>> select count(*) into CC from client WHERE of_id = :new.of_id;
>>> If CC > 0 then
>>> --do not insert
>>> raise_application_error(-20000,'Invalid of_id. Your message here');
>>> end if;
>>> END IF;
>>>END;
>>>/
>>
>>
>>That's most probably not what the OP wants
>>
>>Consider
>>
>>create table client (
>> of_id number,
>> foooo number
>>);
>>
>>
>>CREATE OR REPLACE TRIGGER iu_cli BEFORE insert or update on client
>>FOR EACH ROW
>>DECLARE
>>CC number;
>>BEGIN
>> IF INSERTING
>> THEN
>> select count(*) into CC from client WHERE of_id = :new.of_id;
>> If CC > 0 then
>> --do not insert
>> raise_application_error(-20000,'Invalid of_id. Your message here');
>> end if;
>> END IF;
>>END;
>>/
>>
>>--- Run the following PL/SQL block
>>--- simultanously in two SQL*Plus
>>--- sessions
>>
>>begin
>> for i in 1 .. 50000 loop
>> begin
>> insert into client values
>> (mod(i,20), i);
>> exception when others then null;
>> end;
>> end loop;
>>end;
>>/
>>
>>commit;
>>
>>-------------- Then....
>>
>>select count(*), of_id from client group by of_id;
>>
>>
>> COUNT(*) OF_ID
>>---------- ----------
>> 2 0
>> 2 1
>> 2 2
>> 2 3
>> 2 4
>> 2 5
>> 2 6
>> 2 7
>> 2 8
>> 2 9
>> 2 10
>> 2 11
>> 2 12
>> 2 13
>> 2 14
>> 2 15
>> 2 16
>> 2 17
>> 2 18
>> 2 19
>>
>>
>>
>>>...or create an unique constraint on client.of_id:
>>>
>>>alter table client
>>>add constraint client_of_id_uni
>>>unique (of_id);
>>
>>Definitely the better approach.
>>
>>Rene
>
>
>
> Hello all,
>
> Well, the unique index is not an option.
Why not?
> So I will not do that. I had
> implementen the raise-application-error but I was wondering if there
> was a more elegenat sollution.
But the raise-application-error trigger doesn't work! The only way it
could work would be to serialize the process i.e. only allow one
transaction to look at the table at a time.
--
Andy - Opinions are mine and do not necessarily reflect those of
Advantica Ltd
Received on Wed Sep 22 2004 - 04:05:51 CDT
