Table lock for insert triggers?

[chikkodi]

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
DECLARE
dbcount INTEGER;
BEGIN
SELECT COUNT(*) INTO dbcount FROM offcparm;
IF dbcount > 1 THEN
raise_application_error(-20000, 'Only 1 row supported');
END IF;
END;
/

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

How can this be fixed? Please help!

[DaPi]

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.

(With aknowledgements to Anonymous.)

[gandolf989]

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
DECLARE
   :NEW.new_pkey := 1;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
      mylog('row already exists...BLAH...BLAH...BLAH...');
      RAISE;
   WHEN OTHERS THEN RAISE;
END;
/

[marist89]

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

Table created.

SQL> edit
Wrote file afiedt.buf

  1  create or replace trigger t1
  2  before insert on xyz
  3  for each row
  4  begin
  5     :new.id := 1;
  6* end;
SQL> /

Trigger created.

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;

        ID F2
---------- ------
         1 foo

[marist89]

damn that gandolf989.

[chikkodi]

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

[marist89]

Originally posted by chikkodi
So I was hoping for a generic solution, like lock the whole table before an insert?? not sure.

Sure, you can do that. However, now you've taken your highly scalable piece of software and turned it into a spreadsheet where only one user can access a particular piece of data at a time.

[gandolf989]

Originally posted by marist89
damn that gandolf989.

Great minds think alike!!!

[chikkodi]

But doesnt Oracle do the same for primary keys? It does not allow me to insert the same row from another session till the first one is committed.

Or we have to make our inserts single threaded, which is slow too. else we will have bad data in the db.