ora-4094. can you explain it?

From: <tashepkov_at_my-deja.com>
Date: Thu, 08 Jul 1999 22:09:13 GMT
Message-ID: <7m37i0$ong$1@nnrp1.deja.com>

Hello,

I have two tables A and B.
Table B contains numbers, table A contains only unique numbers from B
B is having a primary key, A is having a foreign key, which references to B
B is also having a "before insert for each row" trigger to insert new unique values into A

Problem:
I can't insert several rows in B at once like this INSERT INTO ... SELECT ... FROM ...,
but I can do that row by row (in PL/SQL for example).

Can anybody explain it?

I attached a SQL*Plus output below.

Thank you,
Tim Ashepkov

cut here ----------------------

SQL> CREATE TABLE a (
2 col NUMBER NOT NULL PRIMARY KEY 3 );

Table created.

SQL>
SQL> CREATE TABLE b (
2 col NUMBER NOT NULL REFERENCES a(col) 3 );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER b_bi BEFORE INSERT ON b FOR EACH ROW   2 DECLARE
  3 num_rec PLS_INTEGER;
  4
  5 BEGIN
  6 SELECT count(*) INTO num_rec FROM a WHERE col = :new.col;   7

  8      IF num_rec = 0 THEN
  9          INSERT INTO a(col) VALUES (:new.col);
 10      END IF;

11 END;
12 /

Trigger created.

SQL>
SQL> CREATE TABLE bulk (
2 col NUMBER NOT NULL
3 );

Table created.

SQL>
SQL> INSERT INTO bulk(col) VALUES (1);

1 row created.

SQL> INSERT INTO bulk(col) VALUES (1);

1 row created.

SQL> INSERT INTO bulk(col) VALUES (2);

1 row created.

SQL> INSERT INTO bulk(col) VALUES (3);

1 row created.

SQL> INSERT INTO bulk(col) VALUES (4);

1 row created.

SQL> COMMIT; Commit complete.

SQL>
SQL> -- that is not going to work
SQL> INSERT INTO b(col) SELECT col FROM bulk;
INSERT INTO b(col) SELECT col FROM bulk
            *

ERROR at line 1:

ORA-04094: table TASHEPKOV.A is constraining, trigger may not modify it
ORA-06512: at "TASHEPKOV.B_BI", line 8
ORA-04088: error during execution of trigger 'TASHEPKOV.B_BI'

SQL> ROLLBACK; Rollback complete.

SQL>
SQL> -- although that will work!
SQL> BEGIN
  2      FOR i IN (SELECT col FROM bulk)
  3      LOOP
  4          INSERT INTO b(col) VALUES (i.col);
  5      END LOOP;

6 END;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM a;

COL

SQL>
SQL> SELECT * FROM b;

COL

SQL> Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 17:09:13 CDT