Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Executing DDL from trigger

Re: Executing DDL from trigger

From: Jim Conboy <Jim.Conboy_at_trw.com>
Date: Wed, 24 May 2000 14:31:29 -0700
Message-Id: <10507.106592@fatcity.com>


Hi folks-

I should have specified that I'm using a 7.3.4 database, but now I'm glad = I didn't because what I've learned about autonomous transactions give me = yet more incentive to push our databases up the upgrade path. Other = people came up with imaginative solutions mostly involving a separate = process initiated from dbms_job or the OS. Well, I've got some ideas to = play with. Thanks to all for your help.

Jim

>>> vprasad_at_olf.com 05/24/00 05:37PM >>>
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. Received on Wed May 24 2000 - 16:31:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US