RE: Executing DDL from trigger

You can now isolate a transaction (commit/rollback) within the trigger itself. The commit/rollback is for this local (autonomous [new buzzword]) transaction only and not part of the 'parent' transaction.

This is a la 8i.

Jay Ballinger
Technical Team Lead
Internet Development
DST Innovis

-----Original Message-----
From: martyn.r.baker_at_uk.pwcglobal.com
[mailto:martyn.r.baker_at_uk.pwcglobal.com] Sent: Wednesday, May 24, 2000 10:13
To: Multiple recipients of list ORACLE-L Subject: Re: Executing DDL from trigger

Jim,

A couple of things that you CANNOT do with a trigger: o execute DDL statements
o issue COMMIT, ROLLBACK and SAVEPOINT statements.

Martyn
(Correct me if I'm wrong...... again :o)

                                                                  
 (Embedded                                                        
 image moved   Jim Conboy <Jim.Conboy_at_trw.com>                    
 to file:      24/05/2000 16:04                                   
 pic08319.pcx)                                                    
                                                                  

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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).


----------------------------------------------------------------
The information transmitted is intended only for the person or entity to
which
it is addressed and may contain confidential and/or privileged material.
Any
review, retransmission, dissemination or other use of, or taking of any
action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited.   If you received this in error, please