Re: Executing DDL from trigger

From: Veera Prasad <vprasad_at_olf.com>
Date: Wed, 24 May 2000 16:20:12 -0400
Message-Id: <10507.106585@fatcity.com>

--------------BB01CBF3CE51A0F2C975831C
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Using autonomous_Transaction in trigger works for DDL's too. check it out

VPRASAD: PRECY>create or replace trigger test_table   2 after insert on emp
  3 declare
  4 pragma autonomous_transaction;
  5 begin
  6 execute immediate 'create table test1 (no number)';   7 end;
  8 /

Trigger created.

VPRASAD: PRECY>insert into emp (empno) values(9999);

1 row created.

VPRASAD: PRECY>desc test1

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NO                                                             NUMBER

I take back my statement made in my earlier mail like " autonomous transaction doesn't seems like working for DDL's " which is not really true as it proved above.

Jim Conboy, I think u got the solution for your problem.

Regards
Veera Prasad

Jim Conboy wrote:

> Hi folks-
>
> I'm trying to simplify the process of creating a user account for a COTS package where I can't touch the code. There's some user configuration within the application, and of course there's creating the actual Oracle account. I created an insert trigger for the main user configuration table and used DBMS_SQL to execute a CREATE USER statement. The code works fine as a stored procedure, but as a trigger I get an error 4092 that commit not allowed in a trigger, presumably because of the CREATE USER DDL. I tried having the trigger execute the procedure but got the same error - actually I'd be disturbed if it didn't! I'm not looking for anything too involved here because a script will do the trick fine, just wondering if anybody has a simple suggestion to make this work. Thanks.
>
> Jim
>
> --
> Author: Jim Conboy
> INET: Jim.Conboy_at_trw.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--------------BB01CBF3CE51A0F2C975831C
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html>
Using autonomous_Transaction in trigger works for DDL's too.

<br>check it out
<p>VPRASAD: PRECY>create or replace trigger test_table
<br>&nbsp; 2&nbsp; after insert on emp
<br>&nbsp; 3&nbsp; declare
<br>&nbsp; 4&nbsp; pragma autonomous_transaction;
<br>&nbsp; 5&nbsp; begin
<br>&nbsp; 6&nbsp; execute immediate 'create table <b>test1</b> (no number)';
<br>&nbsp; 7&nbsp; end;
<br>&nbsp; 8&nbsp; /
<p>Trigger created.
<p>VPRASAD: PRECY>insert into emp (empno) values(9999);
<p>1 row created.
<p>VPRASAD: PRECY>desc <b>test1</b>
<br>&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Null?    Type

<br>&nbsp;----------------------------------------------------- --------
------------------------------------

NO                                                             NUMBER
  
I take back my statement made in my earlier mail like " autonomous transaction doesn't
 seems like working for DDL's " which is not really true as it proved above.

<p>Jim Conboy, I think u got the solution for your problem.
<p>Regards
<br>Veera Prasad
<br>&nbsp;
<p>Jim Conboy wrote:
<blockquote TYPE=CITE>Hi folks-
<p>I'm trying to simplify the process of creating a user account for a

COTS package where I can't touch the code.  There's some user configuration within the application, and of course there's creating the actual Oracle account.  I created an insert trigger for the main user configuration table and used DBMS_SQL to execute a CREATE USER statement.  The code works fine as a stored procedure, but as a trigger I get an error 4092 that commit not allowed in a trigger, presumably because of the CREATE USER DDL.  I tried having the trigger execute the procedure but got the same error - actually I'd be disturbed if it didn't!  I'm not looking for anything too involved here because a script will do the trick fine, just wondering if anybody has a simple suggestion to make this work.  Thanks.

<p>Jim
<p>--
<br>Author: Jim Conboy
<br>&nbsp; INET: Jim.Conboy_at_trw.com
<p>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp;

FAX: (858) 538-5051
 San Diego, California        -- Public Internet access / Mailing Lists

<br>--------------------------------------------------------------------
<br>To REMOVE yourself from this mailing list, send an E-Mail message
<br>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).</blockquote> Received on Wed May 24 2000 - 15:20:12 CDT