Table must have one row minimum and 1 row maximum. (singularity check) Oracle Headquarters Redwood Shores1 e1698667100526

Table must have one row minimum and 1 row maximum. (singularity check)

Imagine that you have to implement a table which must contain one row minimum and
may not have more than one rows. So, on insert of a row you can check if there’s
already a row in the table by joining the table to itself, like the next statement

select 1
from   table t1
,      table t2
where  t1.id != t2.id

The obvious way to do this is to create a set

of database triggers. You cannot use only one row trigger because the query is mutating,
so you need to put the rowid on a stack on a row event trigger and loop
over the rowid’s on stack on a after statement event.

Next create 3 triggers to prevent (sort of) that more than 1 row is
in the table at all times.
Because checking the record count involves a mutating table issue
the rowid is put on a stack and handled in the after statement trigger.
You need
1) a before statement trigger to initialize the stack
2) a (after) row trigger to put the new row on the stack.
I prefer to use an after row trigger above before row because when the after row event fires all
database constraints (when not flagged defered) are finished.
Failures on these constraints are handled immediately and after row triggers do not fire,
thus will cost nothing. It more expensive for a before row to fire when the row fails
due to a constraint violation. So I prefer to let the database do it’s work before I will throw in
some business rules checking.
3) a after statement trigger to perform the singularity check

Here’s the scripting:

create table ags_parameters
( par_id    number(4)      not null,
  par_name  varchar2(100 byte)
)
/
alter table ags_parameters
add constraint ags_par_pk primary key(par_id)
/

create or replace trigger ags_par_bs
before delete or insert or update
on ags_parameters 
declare
begin
  if inserting or updating
  then
    acc_rowid_stack_light.create_stack;
  end if;
exception
  when others
  then raise_application_error(-20010, sqlerrm );
end;
/
create or replace trigger ags_par_ar
after delete or insert or update
on ags_parameters
for each row
declare
  flg_par1  constant binary_integer := 1;
begin
  if inserting
  then
    acc_rowid_stack_light.push_flag(:new.rowid, flg_par1);
  elsif deleting
  then
    raise_application_error(-20010, 'Delete is not allowed' );
  end if;
exception
  when others
  then raise_application_error(-20010, sqlerrm );
end ags_par_ar;
/

create or replace trigger ags_par_as
after delete or insert or update
on ags_parameters 
declare
  flg_par1         constant binary_integer := 1;
  e_locked         exception;
  pragma           exception_init (e_locked, -54);
  t_rowid          rowid;
  t_value          binary_integer;
begin
  while acc_rowid_stack_light.pop_flagset( t_rowid, t_value )
  loop
    if acc_rowid_stack_light.check_flag(t_value, flg_par1)
    then
      declare
        cursor c_par(b_rowid in rowid)
        is
          select 1
          from   ags_parameters par1
          ,      ags_parameters par2
          where  par1.par_id != par2.par_id
          and    par1.rowid   = b_rowid
          for update of par1.par_id;
        r_par        c_par%rowtype;
        l_par_found  boolean;
        e_par        exception;
      begin
        open  c_par(t_rowid);
        fetch c_par into r_par;
        l_par_found  := c_par%found;
        close c_par;
        if l_par_found
        then
          raise e_par;
        end if;
      exception
        when e_par
        then raise_application_error(-20010, 'Only one row is allowed in this table');
      end;
    end if;
  end loop;
  acc_rowid_stack_light.delete_stack;
exception
  when others
  then raise_application_error(-20010, sqlerrm );
end ags_par_as;
/

Now the testing.
First try to insert 2 records and see if it works.

insert into ags_parameters (par_name) values ('test1');
insert into ags_parameters (par_name) values ('test2');

The second row will raise an excpetion “ORA-20010 – Only one row is allowed in this table”. That’s fine.
But is it?

Let’s try another experiment. This time I’m going to use 2 concurrent sessions.
In the first session I insert a row.

prompt Rollback previous test results.
rollback;
insert into ags_parameters (par_name) values ('session1');
pause;
commit;

It returns with “1 row created” Now do NOT commit! Open another session and also insert a row into ags_parameters and commit

prompt second session
insert into ags_parameters (par_name) values ('session2');
commit;

It also returns with “1 row created”

Return to the first session and commit that session also. Than count the records.

select count(*) from ags_parameters;

Oh no, 2 records ! ! ! !

Working alternative/solution

 

create table fbi_parameters
( par_id    number(4)      not null,
  par_name  varchar2(100 byte)
)
/
alter table fbi_parameters 
add constraint fbi_par_pk primary key(par_id)
/

Create a function based index. This index returns cq indexes value 1 for each and every record. Because it is
defined as a UNIQUE index, only one row is permitted in the table.

create unique index fbi_one_row_table_i on fbi_parameters (1)
/
insert into fbi_parameters (par_name) values ('session 1');
pause;
commit;

Do NOT commit! Open another session and also insert a row into fbi_parameters and commit

insert into ags_parameters(par_name) values ('session2');
pause;
commit;

It is immediately being locked by the other session.
Return to the first session and issue a commit;
Return to the second session and issue a commit;
The second session stops because “ORA-00001: unique constraint (fbi_one_row_table) violated”

 

select count(*) from fbi_parameters;

 

Yippie! Function based index to the rescue.
And as a side effect, less objects (1 table + 1 index + 3 triggers) vs (1 table + 2 indices + 0 triggers)

But that”s only part of the story: It is still possible to delete all record from fbi_parameters.
Here, I guess a database trigger is the only possible solution.

 

create or replace trigger fbi_par_br
after delete or insert or update
on ags_parameters
for each row
begin
  if deleting
  then
    raise_application_error(-20010, 'Deleting the last/only row in this table is not allowed');
  end if;
end fbi_par_br;
/

Table ags_parameters already has a check against deleting rows in the after row trigger. It might
as well be implemented as a before row trigger.

12 Comments

  1. Leon Cassion May 27, 2009
  2. Jaime Febres November 8, 2007
  3. Laura Coutts March 2, 2007
  4. Jon August 31, 2006
  5. Harm Verschuren August 21, 2006
  6. Tobias August 19, 2006
  7. Harm Verschuren March 14, 2006
  8. Harm Verschuren March 14, 2006
  9. Toon Koppelaars March 14, 2006
  10. Harm Verschuren March 13, 2006
  11. anton March 13, 2006
  12. LewisC March 11, 2006