• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1622
  • Last Modified:

ORACLE CHECK TABLE CONSTRAINT WITH TRIGGER

Hi,

I have a table 'MYTABLE'  with a primary key column 'ID' and a another column 'REPLACE_WITH' which contains a reference to another row of the table.
Example:
  ID=1    REPLACE_WITH=NULL
  ID=2    REPLACE_WITH=1
  ID=3    REPLACE_WITH=1
  ID=4    REPLACE_WITH=NULL

I want to add a table constraint which checks that the REPLACE_WITH value cannot be set to reference a row which is already referenced by another row. In the example the REPLACE_WITH column cannot be set for row with ID=1.

I have tried to create a trigger (before insert or update) which check it, but I get the error 'TABLE MUTATING'. If I use a separate transaction (PRAGMA autonomous_transaction) then the check does not work when multiple rows modifications done in the same transaction.

Someone has the solution?
 

0
getronics-grenoble
Asked:
getronics-grenoble
1 Solution
 
plamen73Commented:
Unfortunatelly, you can not afford this in ORACLE.
You can do it only in your application level:

1. Serialize the code which writes in that table
2. Select the number of references to the PK you are going to insert references
3. if it is 0 proceed, i.e.
0
 
schwertnerCommented:
This happens with triggers. It occurs because the trigger is trying to update a row
it is currently using. The usual fix involves either use of views or temporary tables so the database
is selecting from one while updating the other.

0
 
plamen73Commented:
But you are happy in your particular situation as the id is actually the PK.
If you design a Unique key on replace_with column you will achieve the task as NULL values in non composite key are not part of the key actually.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
plamen73Commented:
Sorry, this was stupid. Consider the Izolation level. Here is an example:

----------------------------------------------------------------------------------------
create table t_tree(id number primary key, replace_with references t_tree(id));
create or replace procedure ins_tree(in_ID in number, in_Replace_With in number) is
  v_Dummy number;
begin
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
  select count(1) into v_Dummy
  from t_tree where replace_With = in_Replace_With;

  if(v_Dummy = 0) then
    insert into t_tree(id, replace_with)values(in_ID, in_Replace_With);
    commit;
  else
    raise_application_error(-20010, 'Too many Childs!');
  end if;
exception
  when others then rollback;raise;
end ins_tree;
/

exec ins_tree(1, null);
PL/SQL procedure successfully completed

exec ins_tree(2, 1);
PL/SQL procedure successfully completed

exec ins_tree(3, 1);
begin ins_tree(3, 1); end;

ORA-20010: Too many Childs!
ORA-06512: at "PLAMEN.INS_TREE", line 16
ORA-06512: at line 1
-----------------------------------------------------------
0
 
plamen73Commented:
Not in very good shape today :))
Your particular case can be solved with UK, see:
SQL> drop table t_tree;

Table dropped.

SQL> create table t_tree(id number primary key, replace_with references t_tree(i
d));

Table created.

SQL> create unique index uk_t_tree on t_tree(replace_with);

Index created.

SQL> insert into t_tree values(1, null);

1 row created.

SQL> insert into t_tree values(2,1);

1 row created.

SQL> insert into t_tree values(3,1);
insert into t_tree values(3,1)
*
ERROR at line 1:
ORA-00001: unique constraint (PLAMEN.UK_T_TREE) violated

SQL> insert into t_tree values(4,null);

1 row created.
0
 
dsr789Commented:
Try with this..

SQL> select * from tab1;

       ID1        ID2  
    --------   --------
      1000        100
      1100        101
      1200        102
      1300        103

create or replace trigger trg_tab1_chk BEFORE INSERT OR UPDATE OF id2 ON tab1 FOR EACH ROW
declare
var_t1 char;
BEGIN
    begin
         select distinct 'Y' into var_t1 from tab1 where id2 = :new.id2;
    exception
      when others then
        var_t1 := 'N';
    end;
    if var_t1 = 'Y' then
         RAISE_APPLICATION_ERROR(-20000, 'id2 value already exists in the database');
    END IF;
END;


SQL> insert into tab1 values(1400,100);
insert into tab1 values(1400,100)
            *
ERROR at line 1:
ORA-20000: id2 value already exists in the database


DSR
0
 
plamen73Commented:
dsr789s suggestion will work in one user environment only.
Imagine two transactions in the same time:
Transactions 1 inserts
ID1 = 1400, ID2=104;

Transaction 2 inserts
ID1 = 1500, ID=104
No exceptions will be raised as the Transaction 1 is still not committed, therefore the statement:
        select distinct 'Y' into var_t1 from tab1 where id2 = 104;
will return nothing

Both transaction commit you end with two references to the same PK. Actually this concurency which Oracle provides prevents us to solve with Trigger a task like:
Only ID=100 can be referenced more than once,

but the current case can easily be solved by UK on replace_with column

0
 
getronics-grenobleAuthor Commented:
Hi dsr789 and plamen73,

The example from Comment from dsr789 works if transaction is commited after each insert. But does not work for multiple insertion into a unique transaction.

0
 
riazpkCommented:
Ok...Here we go:

SQL> create table test (id number, replace_With number);

Table created.

SQL> insert into test(id) select rownum from tab;

4 rows created.

SQL> commit;

Commit complete.

SQL> select * From test;

        ID REPLACE_WITH
---------- ------------
         1
         2
         3
         4

SQL> ed
Wrote file afiedt.buf

  1* select * From test
SQL> update test set replace_with=1 where id=2;

1 row updated.

SQL> commit;

Commit complete.


SQL> alter table test add primary key(id);

Table altered.


SQL> ed
Wrote file afiedt.buf

  1  create materialized view log on test with rowid, (replace_With)
  2* including new values
SQL> /

Materialized view log created.


SQL> ed
Wrote file afiedt.buf

  1  create materialized view emp_mv
  2  refresh fast on commit
  3  as
  4* select count(*) cnt, replace_With from test where replace_With is not null group by replace_with
SQL> /

Materialized view created.

SQL> alter table emp_mv
  2   add constraint check_cnt
  3   check ( cnt<=1)
  4   deferrable
  5  /

Table altered.

SQL> select * from test;

        ID REPLACE_WITH
---------- ------------
         1
         2
         3            1
         4

SQL> update test set replace_with=1 where id=2;

1 row updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (M8S.CHECK_CNT) violated
0
 
getronics-grenobleAuthor Commented:
I admire your solution but it does'nt work exacly as specified.

The check must not be one the replace_with count but on the id. The following list indicates which query are OK or not:
UPDATE test SET replace_with=1 WHERE id=4;---> OK
UPDATE test SET replace_with=2 WHERE id=5;---> OK;
UPDATE test SET replace_with=3 WHERE id=1;---> NO OK because 1 cannot be replaced because it is itsel used as
replacement value
UPDATE test SET replace_with=2 WHERE id=6;---> OK;



0
 
plamen73Commented:
Again: Unique Key on replace_with field:
create unique index idx_.... on <table>(replace_with);
It will not allow you to insert (or update) more than one record with the same replace_with value...except if it is null
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now