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 <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) >>
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;
begin
for i in 1 .. 50000 loop
begin
insert into client values (mod(i,20), i);
commit;
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
-- Rene Nyffenegger http://www.adp-gmbh.ch/Received on Tue Sep 21 2004 - 10:47:21 CDT