GeekInterview.com
Series: Subject: Topic:
Question: 139 of 190

Can you use a commit statement within a database trigger?

No.
Asked by: Interview Candidate | Asked on: Jul 27th, 2005
Showing Answers 1 - 11 of 11 Answers
subrahmanaym

Answered On : 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
GT

Answered On : Jul 26th, 2005

Yes we can use commit inside a trigger by using pragma_autonomous_transactions

  
Login to rate this answer.
Indrajit Adhya

Answered On : Nov 9th, 2005

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

Yes  1 User has rated as useful.
  
Login to rate this answer.
kin

Answered On : 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

  
Login to rate this answer.
Rana

Answered On : Apr 5th, 2006

Commit cannot be used in trigger.

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

  
Login to rate this answer.
Rakesh

Answered On : 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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
Mahesh Konatham

Answered On : 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

  
Login to rate this answer.

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

  
Login to rate this answer.
selvaspak

Answered On : Dec 24th, 2010

View all answers by selvaspak

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

Regards,
SSG

Yes  1 User has rated as useful.
  
Login to rate this answer.
Jagan

Answered On : Aug 21st, 2011

Yes, by using autonomous transaction

  
Login to rate this answer.
Shantanu

Answered On : 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.

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.