Your procedure called by the trigger creates a table.
Now a "create table" statement makes an implicit commit
and this is not allowed in a trigger unless you put a
"pragma autonomous_transaction;" statement either in the
trigger or in the procedure.
Note that all what is then done in the trigger cannot be
rollbacked.
--
Have a nice day
Michel
<bogaci_at_my-deja.com> a écrit dans le message : 8h14dp$bg1$1_at_nnrp2.deja.com...
> 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 Wed May 31 2000 - 00:00:00 CDT
