We have a trigger that ensures number of rows should not exceed a certain count. The trigger is as follows:
CREATE OR REPLACE TRIGGER test AFTER INSERT ON offcparm
SELECT COUNT(*) INTO dbcount FROM offcparm;
IF dbcount > 1 THEN
raise_application_error(-20000, 'Only 1 row supported');
Now, this works fine, except for cases where I do it through 2 sql sessions.
eg, open 2 sqlplus sessions
In session 1 do: insert into offcparm values ('1', '1');
In session 2 do: insert into offcparm values ('2', '2');
issue commit in session 1
issue commit in session 2
If the table itself is only supposed to have one row, then add a numeric Primary key that gets set to one during the INSERT. You might also have an update trigger that makes sure that it is always set to 1.
CREATE OR REPLACE TRIGGER test
BEFORE INSERT ON offcparm
FOR EACH ROW
:NEW.new_pkey := 1;
mylog('row already exists...BLAH...BLAH...BLAH...');
WHEN OTHERS THEN RAISE;
I've seen someone propose to solve this issue (single row) by adding a column with two constraints: it is unique AND is equal to (say) 1.
Yeah, I would kind of attack it with a unique index also. I would probably:
1. create a dummy field, say ID.
2. make ID a primary key
3. create an insert trigger that stuffs a "1" into the ID field unconditionally.
SQL> create table xyz (id number(10) primary key,
2 f2 varchar2(6));
Wrote file afiedt.buf
1 create or replace trigger t1
2 before insert on xyz
3 for each row
5 :new.id := 1;
SQL> insert into xyz (f2) values ('foo');
1 row created.
SQL> insert into xyz(f2) values ('bar');
insert into xyz(f2) values ('bar')
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C005255) violated
SQL> select * from xyz;
So, this appears to be a common problem in all our triggers. I gave a simple eg here. We also see it for cases where we do unique value checking (through triggers). So I was hoping for a generic solution, like lock the whole table before an insert?? not sure.
Interestingly, we dont have this problem for constraints. So, if I try to insert 2 exact same rows in 2 sessions, Oracle blocks the second session till the first one is committed and then errors out on the second. Thats the behavior we want..