Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    12

    Exclamation Unanswered: insert related query

    hi,
    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.

    thanks and regards,
    akshay

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    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 ).
    Code:
    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.
    Code:
    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.
    Code:
    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.
    
    SQL@8i>
    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 ).
    Code:
    SQL@8i> alter table emp_copy add constraint emp_copy_pk primary key ( empno );
    
    Table altered.
    
    SQL@8i>
    SQL@8i> alter table emp_copy add constraint emp_copy_fk foreign key ( mgr ) references emp initially deferred;
    
    Table altered.
    
    SQL@8i> insert into emp_copy select * from emp order by empno;
    
    14 rows created.
    
    SQL@8i> commit;
    
    Commit complete.
    
    SQL@8i>
    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.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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
    ... etc.
    (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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •