Oracle PLSQL Trigger

From: <bogaci_at_my-deja.com>
Date: 2000/05/30
Message-ID: <8h14e8$o9b$1@nnrp1.deja.com>#1/1

Hi,

I got lost with this and it drives me crazy ...

I have a after insert trigger which has to create a new table with the name from a field in the last inserted record.

I have written a stored procedure which looks like this:

procedure update_afterinsert_ereig_ref
IS
  CURSOR new_rows IS
    SELECT e.*, e.ROWID FROM ereig_ref e WHERE e.ergbeschid IS NULL;   maxid NUMBER;
  etname VARCHAR2(20);
  c INTEGER;
  n NUMBER;
BEGIN
  FOR curent_rec IN new_rows
  LOOP
    SELECT MAX(ergbeschid) INTO maxid FROM ereig_ref;     UPDATE ereig_ref SET ergbeschid = (maxid + 1) WHERE ereig_ref.ROWID=curent_rec.ROWID;

SELECT ergtabel INTO etname FROM ereig_ref where ereig_ref.ROWID=curent_rec.ROWID;

CREATE_TABLE_ERGTABEL(etname);
END LOOP;
END; and

procedure create_table_ergtabel (numetab IN VARCHAR2) IS
c INTEGER;
n NUMBER;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'CREATE TABLE ' || numetab || '( ERGID NUMBER

(10,0), FIRMAID NUMBER(8,0))', dbms_sql.native);
 n := dbms_sql.execute(c);
 dbms_sql.close_cursor(c);

exception when others then
  if dbms_sql.is_open(c) then
    dbms_sql.close_cursor(c);
  end if;
  raise;
END create_table_ergtabel;

and when inserting a new record in the table i get this error :

ORA-04092: Trigger can not do a COMMIT
ORA-06512: in "CREATE_TABLE_ERGTABEL", line 14
ORA-06512: in "UPDATE_AFTERINSERT_EREIG_REF", line 14
ORA-06512: in "AFINS_ERGBESCHID", line 2
ORA-04088: error when running Trigger "AFINS_ERBESCHID", line 2

Triger looks like:
TRIGGER AFINS_ERGBESCHID
BEGIN
UPDATE_AFTERINSERT_EREIG_REF;
END; I know the code is a bit to complicated, but is also the same with putting all the code in the trigger and using old and new values, not the update_afterinsert_ereig_ref stored procedure, but is working.

So the question is this posible or not and where's my mistake ?

TIA,
Seb.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 30 2000 - 00:00:00 CDT