INTERVIEW QUESTIONS DATABASE STORED PROCEDURES DETAILS

Question: Can we use commit inside the trigger? If not then how can we save the transxaction made by the trigger?

Answer: Using pragma concept.

Using pragma concept. Source: CoolInterview.com

Cannot perform commit or rollback because they are part of triggering sql statements hence don't need to save the transaction by trigger. Source: CoolInterview.com

We cann't use TCL statement(commit,rollback, savepoint) in a trigger. But We can use it on stored pl/sql block means procedure. So we can call procedure from a trigger with "CALL" method. Source: CoolInterview.com

Using pragma autonomous_transaction, We can use TCL (Commit) in Triggers. Example as follows.<br><br>create or replace trigger at_trg<br>after insert<br>on emp<br>for each row<br>Declare<br>pragma autonomous_transaction;<br>begin<br>insert into bonus(ename,job,sal) <br> values(:new.ename,:new.job,:new.sal);<br>commit;<br>end;<br><br>SQL> insert into emp(empno,ename,job,sal,comm) <br>values(1000,'krishna','faculty',5000,200);<br><br>Result:-1 row created.<br> Source: CoolInterview.com

Answer already given.PRAGMA is the pre compiler directives.it gives instruction to the compiler how to use commit in a autonomous transaction. 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