Re: cancel an insert within the trigger?

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
> 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

The question truly is why do you want to? A UNIQUE constraint does the deed without your coding, and it ensures you have but ONE entry with each indexed value. You have coded to reject an insert if MORE than one entry exists; after your insert succeeds you will have duplicate entries, and scores of them, possibly. It doesn't do as you want ( I suspect since you can't really put into words what you do want ) and it's unnecessary, as a reliable mechanism already exists in the database.

Create a UNIQUE index on the column in question and leave well enough alone.

David Fitzjarrell Received on Tue Sep 21 2004 - 09:24:01 CDT