subrahmanaym

• Jun 13th, 2005

 

NO,but it's possible through autonomous transaction. 
Autonoumous transaction is transaction,which started in main program but is is independent of main transaction.

GT

• Jul 26th, 2005

 

Yes we can use commit inside a trigger by using pragma_autonomous_transactions

Indrajit Adhya

• Nov 9th, 2005

 

YES. It's possible by doing SET Autonomas_Transaction = TRUE 

kin

• Apr 1st, 2006

 

Come to think of it, I have never seen a commit on triggers. Triggers as you know are a last line defense based on an action that was carried out by an insert,update or delete. Hence, since the trigger exists on the server, and is only fired if one of the above conditions are executed then it seems pointless to use a commit in the trigger. Also commit goes along nicely with transactions where you begin the transaction and if all is well then you issue a commit, else if the transaction fails, then you issue a rollback. Hope this helps

Rana

• Apr 5th, 2006

 

Commit cannot be used in trigger.

It can be achived by calling procedure or function which can perform commit.

Rakesh

• Apr 18th, 2006

 

As per Trigger's restrictions, we can not use any transaction control statement(like commit,rollback,savepoint etc.) into the body of a trigger.

But oracle provides a facility called Autonomous Transactions through which you can achive this.You add the that piece of the code, which contains commit, in some block and make that block as Autonomous Transaction and call or use that block into your trigger.

Mahesh Konatham

• May 25th, 2006

 

One cannot use commit directly in side the body of a trigger. A little tweak would help one achieve it.

Way One:   Have an autonomos block of code in the trigger body and use the commit statement inside it.

Way Two:  Call a stored procedure from the trigger body and let the stored procedure have the commit statement in it.

Hope this gives you a way out.

With thanks,
Mahesh Konatham

rmadhu_apps

 Profile Answers by rmadhu_apps Questions by rmadhu_apps

• Aug 11th, 2008

 

No, directly we cannot use 'commit' inside the trigger.
We have to use pragma Autonomous Transaction to issue commit inside a trigger.

selvaspak

 Profile Answers by selvaspak 

• Dec 24th, 2010

 

Yes, you can commit inside the trigger.
But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma.
Pragma AUTONOMOUS_TRANSACTION allow you to build the Independent (child) Transaction

Regards,
SSG

Jagan

• Aug 21st, 2011

 

Yes, by using autonomous transaction

Shantanu

• Aug 17th, 2012

 

You cant directly use commit in triggers. You have to use PRAGMA Autonomous_Transaction or you can call other procedure or function, but other procedure or function should also have PRAGMA Autonomous_Transaction in it.