GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Oracle  >  SQL
Go To First  |  Previous Question  |  Next Question 
 SQL  |  Question 52 of 171    Print  
Can you use a commit statement within a database trigger?
No.



  
Total Answers and Comments: 8 Last Update: August 11, 2008   
  
 Sponsored Links

 
 Best Rated Answer

No best answer available. Please pick the good answer available or submit your answer.
June 13, 2005 08:51:47   #1  
subrahmanaym        

RE: Can you use a commit statement within a database trigger?
NO but it's possible through autonomous transaction.
Autonoumous transaction is transaction which started in main program but is is independent of main transaction.

 
Is this answer useful? Yes | No
July 26, 2005 01:53:25   #2  
GT        

RE: Can you use a commit statement within a database trigger?
Yes we can use commit inside a trigger by using pragma_autonomous_transactions
 
Is this answer useful? Yes | No
November 09, 2005 13:32:35   #3  
Indrajit Adhya        

RE: Can you use a commit statement within a database t...
YES. It's possible by doing SET Autonomas_Transaction TRUE
 
Is this answer useful? Yes | No
April 01, 2006 00:19:43   #4  
kin        

RE: Can you use a commit statement within a database t...
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
 
Is this answer useful? Yes | No
April 05, 2006 02:11:53   #5  
Rana        

RE: Can you use a commit statement within a database t...

Commit cannot be used in trigger.

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


 
Is this answer useful? Yes | No
April 18, 2006 08:56:32   #6  
Rakesh        

RE: Can you use a commit statement within a database t...
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.

 
Is this answer useful? Yes | No
May 25, 2006 04:25:48   #7  
Mahesh Konatham        

RE: Can you use a commit statement within a database t...

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


 
Is this answer useful? Yes | No
August 11, 2008 15:17:05   #8  
rmadhu_apps Member Since: July 2008   Contribution: 2    

RE: Can you use a commit statement within a database trigger?
No directly we cannot use 'commit' inside the trigger.
We have to use pragma Autonomous Transaction to issue commit inside a trigger.

 
Is this answer useful? Yes | No


 
Go To Top


 Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape