i have 2 tables abc and z_abc(z_abc is a replica of the abc table with a few extra columns). i have created a trigger on the abc table which inserts data into the z_abc table when inserts are done on the abc table. there is another table xyz which is exactly the same as the abc table. now i exec a procedure which picks data from z_abc table and inserts into xyz table which is on another server.
now my question. why is the sequence in which data inserted into the abc table not maintained in the z_abc table even though the trigger is designed to do the same. the xyz table references itself and so when the order is not maintained in the z_abc table , foreign key constraint errors occurs in the xyz table.
what could be the reason for the sequence not being maintained.
One of the reason could be with the way you are trying to populate the data on table XYZ. Since you are dealing with hierarchies, they demand a constraint being applied for each of the rows you are trying to insert. Each of the childs/parent on the row must be previously present before you insert it.
Take this example:
I have added a primary and foreign key constraints to both, the emp table and emp_copy table ( which is an empty copy of emp table ).
SQL@8i> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Suppose that I want to insert data of the emp SCOTT into emp_copy, I would do.
SQL@8i> insert into emp_copy select * from emp where empno = 7788;
insert into emp_copy select * from emp where empno = 7788
ERROR at line 1:
ORA-02291: integrity constraint (JMARTINEZ.EMP_COPY_FK) violated - parent key not found
There I am violating my constraint, since SCOTT has a parent, which is JONES ( 7566 ). Same would happen if I try to insert all of the rows if I dont especify a criteria to follow -- since that is the only way Oracle will guarantee you an order.
However, if I want to insert all of the employees on EMP on emp_copy, I would do something like this.
SQL@8i> insert into emp_copy
2 select *
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr;
14 rows created.
Since I know the hierarchy, I can enforce it with a query, and then insert that query results into my emp_copy table. Another approach would be to use deferrable constraints ( if you are doing massive data load ).
SQL@8i> alter table emp_copy add constraint emp_copy_pk primary key ( empno );
SQL@8i> alter table emp_copy add constraint emp_copy_fk foreign key ( mgr ) references emp initially deferred;
SQL@8i> insert into emp_copy select * from emp order by empno;
14 rows created.
There I didnt have to know the hierarchy, I just loaded data up, and commited when I finished -- at the commit time, Oracle checks if any of the constraint will be violated, and if so, gives you an error.
I am sure that the inserts into z_abc will be done in the same order as the inserts into abc, because it will work like this:
1a) insert record 1 into abc
1b) trigger inserts record 1 into z_abc
2a) insert record 2 into abc
2b) trigger inserts record 2 into z_abc
3a) insert record 3 into abc
3b) trigger inserts record 3 into z_abc
(I assume you are using a FOR EACH ROW trigger?)
Perhaps your problem is in assuming that the z_abc rows will automatically be processed by the procedure in the order they were inserted. That can only be guaranteed if you have a column in the z_abc table that represents the order of insertion and you ORDER by that. Otherwise, the rows may well be processed in a different order.