Re: Oracle PLSQL Trigger

> 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

It is very correct that you get this error. Creating a table is a DDL-command. Oracle will execute COMMIT BEFORE AND AFTER a DDL-command.
But you are not allowd to commit or rollback from within a trigger....

> 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 ?

If you REALLY need to create tables depending on the input of a table may be the following work-around can help you:

Let the trigger fill a table with the names of tables to be created. Create a DB-job (read the manual for the package DBMS_JOBS) and let this job create the tables instead of the trigger periodically (let say every 2 minutes).

BUT: This solution will have a delay between inserting in the table with the trigger
and creating the new table by the db_job.

Let me know if this is a solution for your problem.

Regards, Stephan

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-0           | Landsberger Allee 392
fax: +49 30 549932-21          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------
       PGP-Key verfügbar       |      PGP-Key available
---------------------------------------------------------------