DDL From within a trigger 1

[MikeJones]

I'm trying to execute DDL from within a trigger but get a ora 4092 cannot commit within a trigger error.

Basically the table that the trigger resides on holds details of Oracle profiles as the table is inserted / updated / deleted the trigger captures the change and then builds the appropriate dynamic SQL to Create / change / delete the actual Oracle profile.

Does anyone know of away around this problem? Is there a DBMS Package that I can use???

Thanks,

Mike.

 

[carp]

I haven't tried this myself, but in Oracle8i, you can spawn off autonomous transactions. Perhaps this would be the way to get the job done. The problem is that DDL carries an implicit commit. But if you are in the middle of a transaction, you probably don't want to commit - and indeed you CAN'T commit if this is a BEFORE trigger!
However, if your trigger could spawn off an autonomous transaction (perhaps via a procedural call), you MIGHT be able to get it to run DDL, which would COMMIT without affecting your original transaction.

 

[MikeJones]

Thanks carp,

I was thinking of using autonamous transactions on the way home last night, I think this is the way I'll go, however a quick question before I lay this to rest,

I'm using an AFTER insert trigger, should I therefore be allowed to do DDL (With its implicit commit)?

Thanks again,

Mike.

 

[MikeJones]

Thanks Carp,

I've just checked the syntax of Autonomous transaction and I can put it against the trigger and thus avoid the problems of the commit.

Thanks for your help!!

Mike.

 

[carp]

Mike -
Thanks for following up on this. Now I know that the autonomous transaction approach works in this situation!