OraFAQ Forum: SQL & PL/SQL » ORA-04092: cannot COMMIT in a trigger

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04092: cannot COMMIT in a trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-04092: cannot COMMIT in a trigger [message #152373]

Thu, 22 December 2005 14:09

jmejia2275
Messages: 3
Registered: December 2005

Junior Member

Hi,

I have the following:

In a triger (after insert) I call a stored procedure, in this stored procedure, I tried the DML to create an external table, using the statement "execute immediate".

When I insert in the table who owns the trigger, oracle gives me the error : ORA-04092: cannot COMMIT in a trigger...

How can I do this ?

Thanks in advance,

Jose Enrique

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #152376 is a reply to message #152373]

Thu, 22 December 2005 14:23

wpaillan
Messages: 35
Registered: March 2005
Location: Santiago - Chile

Member

hi

When a procedure is called by trigger, this procedure can not take explicit commit

When finalizing the trigger's execution, you are like a play-acted commit

williams

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #152581 is a reply to message #152376]

Sat, 24 December 2005 04:36

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Why on earth are you creating a table in a trigger?
Use a global temporary table, a PL/SQL table, a REF cursor but don't create and drop tables on the fly. You'll compromise your data model. I know that it is common practice in the MS SQL Server world but in Oracle it's a big "nono".
You could start by explaining what you want to do and why you need to create a new table for each insert in your table.

MHE

[Updated on: Sat, 24 December 2005 04:36]

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #152610 is a reply to message #152373]

Sun, 25 December 2005 05:03

alkeshyeole
Messages: 2
Registered: November 2005
Location: Pune

Junior Member

Hi
To have a DML statement in your Trigger u should use the driective to compilers i.e. pragmas. The pragma used in this case is
pragma autonomus_transaction
After adding this in the trigger declerative part u can use any DML in your trigger.

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #152611 is a reply to message #152610]

Sun, 25 December 2005 05:13

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I still would like to know why the OP is trying to create tables on the fly. I deliberately didn't mention autonomous transactions because you have to make sure there's absolutely no other way to solve the original problem. I tend to avoid autonomous transactions as much as I can: they make things complicated (you have to remember they cannot read data from temp tables, uncommitted data, ...) and certainly in triggers. They cannot read the data you are about to insert/update. You have to pass it to them.

But again: what are you trying to do? Perhaps there's an easier way than just creating tables all the time.

MHE

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #152696 is a reply to message #152611]

Mon, 26 December 2005 09:33

jmejia2275
Messages: 3
Registered: December 2005

Junior Member

I need to create temporary external tables from a trigger, these temporary external tables I need to use in a mapping from warehouse builder. I need it in order to change the directory and the name of the flat file sources of the external table. That's not going to compromise my data model.

Thanks for your help.

Report message to a moderator

Re: ORA-04092: cannot COMMIT in a trigger [message #153004 is a reply to message #152696]

Wed, 28 December 2005 06:01

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Yuk! That's damn ugly! But alkeshyeole was right: you are looking for an autonomous transaction. Use the PRAGMA he suggested.

And can't you just replace the old file with the new one? That way you won't have to mess with dynamic DDL in triggers.

I'm glad I'm not responsible for your db Very Happy

.

MHE

BTW: Temporary tables do exist in Oracle, and they refer to tables with temporary content, not to tables with exist only for a short amount of time.

Report message to a moderator

Previous Topic:	deleting primary key constraint from table
Next Topic:	converting number to word

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 19:40:35 CDT 2025