SQL - Can you use a commit statement within a database trigger?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 52 of 171

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

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

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

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

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

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

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

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

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

User Name		Remember Me?
Password

Question Bank