Search code examples
oracletriggersreferential-integrity

Triggers on parent and child table and order of execution respectively

lets say I have table A and table B. Table B is the child table and has a foreign key to table A. I have other mirrored tables, lets say table C(mirror for table A) and table D(mirror for B). Both tables B and D have a foreign key to its respective table. I have created a trigger on table A in order when its inserted on table A, the trigger inserts the same on table C and the same for table B for table D. My doubt is that in the same transaction, I insert on tables A and B, and maybe the trigger for table B could execute first than the trigger for table A causing an error on table D because the parent foreign key is not found yet on table C. Can it happen and how can I fix it?.

enter image description here

Solution

  • That can't happen as you can't (normally) insert child record if its parent doesn't exist, so child table's trigger can't fire before master table's one.

    SQL> create table table_a
  2    (id_a    number primary key,
  3     name    varchar2(10));

Table created.

SQL> create table table_b
  2    (id_b    number primary key,
  3     id_a    number references table_a (id_a),
  4     address varchar2(10));

Table created.

    This will fail because there's no parent record:

    SQL> insert into table_b (id_b, id_a, address) values (1, 100, 'Croatia');
insert into table_b (id_b, id_a, address) values (1, 100, 'Croatia')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C00157817) violated - parent key not
found

    Let's create parent first:

    SQL> insert into table_a( id_a, name) values (100, 'Little');

1 row created.

    OK; child can now be inserted:

    SQL> insert into table_b (id_b, id_a, address) values (1, 100, 'Croatia');

1 row created.

    Another option is to use deferrable foreign key constraint which lets you insert child records although parent doesn't exist. Control is done at the moment of commit.

    SQL> rollback;

Rollback complete.

SQL> drop table table_b;

Table dropped.

SQL> create table table_b
  2    (id_b    number primary key,
  3     id_a    number references table_a (id_a) initially deferred deferrable,
  4     address varchar2(10));

Table created.

    Inserting child record: now it works!

    SQL> insert into table_b (id_b, id_a, address) values (1, 100, 'Croatia');

1 row created.

    However, if I commit, error is being raised:

    SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C00157819) violated - parent key not
found

    Is anything stored? Nope:

    SQL> select * from table_b;

no rows selected

    OK, so let's start over: child first:

    SQL> insert into table_b (id_b, id_a, address) values (1, 100, 'Croatia');

1 row created.

    Parent next (I didn't commit!):

    SQL> insert into table_a( id_a, name) values (100, 'Little');

1 row created.

    Commit at the end:

    SQL> commit;

Commit complete.

    Tables' contents:

    SQL> select * from table_a;

      ID_A NAME
---------- ----------
       100 Little

SQL> select * From table_B;

      ID_B       ID_A ADDRESS
---------- ---------- ----------
         1        100 Croatia

SQL>

    Therefore, do the same: use deferrable constraints and you should be OK, regardless of triggers' firing sequence.