Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cancel an insert within the trigger?

Re: cancel an insert within the trigger?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 22 Sep 2004 07:08:35 -0400
Message-ID: <2JKdnW9rBKHRwMzcRVn-gg@comcast.com>

"Andrew Hardy" <junkmail@[127.0.0.1]> wrote in message news:cirfaj$1vh$1_at_sun-cc204.lut.ac.uk...
| 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

Andy's point is valid, oracle can access values in the index of a non-committed transaction to check for uniqueness, developers can't -- so here's the scenario:

User A attempts to insert a record with value 'X' -- insert succeeds, no 'X' in table
User B attempts to insert a record with value 'X' -- insert succeeds, no 'X' in table, except for the uncommited 'X' from User A
User A commits
User B commits
Table has duplicate values

Since your are apparently dealing with a dependent table (of_id looks like an FK), you would have to obtain a lock on the referenced table -- but that won't work in an update trigger, you get a mutating table error.

If you can describe why the unique index in not possible (why can you create triggers but not indexes?) i could probably propose an alternate solution -- but it would likely involve creating an additional table on which you can create constraints.

++ mcs Received on Wed Sep 22 2004 - 06:08:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US