Can we have a commit statement inside a trigger? if no why cant we?

  • Interview Candidate
  • 46
  • 56342

Showing Answers 1 - 46 of 46 Answers

sang

  • Oct 22nd, 2005
 

bcas trigger and porcedure can have the auto commit so that we can't use the commit statement inside the trigger

  Was this answer useful?  Yes

Abhijit Pritam Dutta

  • Dec 27th, 2005
 

I can not execute the commit statement inside a trigger but it was asked in many interview. I say no but that they say yes. Your site can help me in this metter. please help me.

  Was this answer useful?  Yes

kehri_anurag

  • Jan 13th, 2006
 

Yes ,You can Commit inside the trigger.

But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another. Shold be declare in DECLARE section of any subprogram.

Used to make Modular and Resuable Blocks. if you need the example then ask to me.

Ashish Dixit

  • Feb 23rd, 2006
 

Grateful if you could elaborate the reply with an example and the syntax. for the same.

  Was this answer useful?  Yes

santhosh

  • Mar 31st, 2006
 

Triggers should not contain transaction control statements like commit, rollback, savepoint r set transaction. Because it is firedas part of the execution of the triggering statement. When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

  Was this answer useful?  Yes

dbadwh

  • Jul 31st, 2006
 

Hello Anurag Kheri,

Can you give us example?

thanks,

Vijay

RE: can we have a commit statement inside a trigger ? ...

Yes ,You can Commit inside the trigger.

But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another. Shold be declare in DECLARE section of any subprogram.

Used to make Modular and Resuable Blocks. if you need the example then ask to me.

  Was this answer useful?  Yes

shashi kumar jha

  • Aug 8th, 2006
 

u are saying that we can not use transaction control statement in trigger

bacause trigger is fired as part of execution of triggering  statement which cause trigger is fired.when trigger statement is commited or rollback trigger transaction is commited or rollback as well.

       then how by the use of pragma autonomous_transaction we can use commit,rollback etc

        and here some one telling that by the use of pragma autonomous_transaction it is commited independent of parent transaction

so i want to know which statement is a parent transaction in the case of trigger?

is it is triggering statement?

Thanks & Regards

shashi kumar jha

                

  Was this answer useful?  Yes

ram prasad

  • Sep 4th, 2006
 

Yes ,You can Commit inside the trigger.

But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another. Shold be declare in DECLARE section of any subprogram.

Used to make Modular and Resuable Blocks. if you need the example then ask to me.

  Was this answer useful?  Yes

viji

  • Sep 7th, 2006
 

Pl give the example

regrads

viji

  Was this answer useful?  Yes

Doesnt matter

  • Jan 20th, 2007
 

CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
 i PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO i
  FROM t1;

  INSERT INTO t2
  VALUES
  (i);
  COMMIT;
END;
/

  Was this answer useful?  Yes

rohitash

  • Aug 14th, 2007
 

Yes we can use commit statement in the database triggers, to achieve this you can use autonomous transaction.

  Was this answer useful?  Yes

Hanumantha Rao

  • Aug 16th, 2007
 

Because trigger and porcedure can have auto commit so that we can't use the commit statement inside the trigger.

  Was this answer useful?  Yes

arul

  • Aug 20th, 2007
 

Trigger is a auto commit and procedure is when you execute the code then after you will give the commit

  Was this answer useful?  Yes

Rahul

  • Oct 18th, 2007
 

Answer of the question is NO....It is perfectly right,But you want to use transaction control statement inside trigger then Yes,You can use commit inside trigger by using Pragma Autonomous_Transaction.By Using this you make transaction autonomous.

  Was this answer useful?  Yes

RC

  • Oct 26th, 2007
 

By using Autonomous transaction we can.. we will declare at the declaration part...

  Was this answer useful?  Yes

ssankoju

  • Mar 25th, 2008
 

yes, here is an example

SQL> CREATE OR REPLACE TRIGGER tab1_trig
  2    AFTER insert ON tab1
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
  7    COMMIT; -- only allowed in autonomous triggers
  8  END;
  9  /

  Was this answer useful?  Yes

Arun Maity

  • Feb 17th, 2014
 

no we cannot have commit or rollback statement in trigger because its a part of a transaction.

And trigger is not auto commit.

  Was this answer useful?  Yes

Gangadhar

  • Apr 1st, 2014
 

Because of its violation.

  Was this answer useful?  Yes

Bibhuprasad Behera

  • Dec 4th, 2015
 

Can you please send me the example of the trigger?

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions