Re: Executing DDL from trigger

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