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
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.)
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
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.
Code:
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;
/
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.
Code:
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
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..
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.
Reply With Quote
Bookmarks