INTERVIEW QUESTIONS ORACLE PL-SQL DETAILS

Question: Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

Answer: It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing. Source: CoolInterview.com

we can use TCL commands in trigger by using autonomous transactions feature of oracle. Source: CoolInterview.com

It is possible. We can create a trigger without error by using 'PRAGMA AUTONOMOUS-TRANSACTION' in declare section. Source: CoolInterview.com

No, we can not use Commit or Rollback, within a trigger. To understand, take one simple example...
Suppose two persons are using one common table and they both have written trigger on that table. If in the first trigger we are using commit/rollback, then the second use would get some unwanted result.
That is the reason, why we don't use Commit/Rollback in Triggers.
Yeah. If some type of failure/ breakdown occurs, in that case Oracle automatically Rollback the data. Source: CoolInterview.com

We can use COMMIT or ROLLBACK inside Trigger, with the help of PRAGMA AUTONOMUS.. Source: CoolInterview.com

Yes above one is correct and avoid mutating transaction by using pragma autonomous_transaction; -- at begin of trigger definition and use TCL in body.. Source: CoolInterview.com

No, its not possible. bcoz we can't do the persistance storage by using triggers. Source: CoolInterview.com

In trigger you cant use the Comint or rollback option. It will be commited or rolled back automatically once the driving startement is commited or rolled back. But if you want to forsibly commit the transaction in trigger then user Progma_autonomous which will forcibly commit Source: CoolInterview.com

we cannot use commit and rollback in triggers without using PRAGMA AUTONOMOUS_TRANSACTION.
triggers are fired when any DML is performed on a table like insert update or delete, and these all have tendency to lock the rows for their operation. whereas Commit or Rollback when used releases the locks if any acquired by transaction. thus in this state trigger gets confused either to lock the rows or to release the lock and goes to mutating stage.
with use of autonomous_transaction parent transaction is seprated from child transaction and child transaction can be committed or rolled back without affecting parent transaction and that is how we can commit or rollback in triggers.
:) Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question