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

Mramesh · #1 (**permalink**) 09-11-2007, 02:48 AM

Hi all,
Could you explain commit statement inside a trigger? if no why cant we?

Sundaram · #2 (**permalink**) 09-11-2007, 02:50 AM

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

a.deeban · #3 (**permalink**) 09-11-2007, 02:55 AM

Hi ramesh,

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.

Sathish Kumar · #4 (**permalink**) 09-11-2007, 02:58 AM

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.

Mramesh · #5 (**permalink**) 09-11-2007, 03:01 AM

Quote:

Originally Posted by a.deeban

Hi ramesh,

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.

Hello Deeban,

Can you give us example?

thanks,

Ramesh Kumar M

SaravananJ · #6 (**permalink**) 09-11-2007, 03:07 AM

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?

a.deeban · #7 (**permalink**) 09-11-2007, 03:08 AM

hi
try this one

Code:

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;
/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Run an exe from an oracle trigger	H2o	Database Support	5	08-09-2007 11:55 AM
Storing the output from trigger in a text file	sureshbabu	Database Support	3	07-27-2007 06:44 AM
How to enforce Transaction concept (ie: commit,rollback ) from .net web application	kingmaker	C# Programming	0	07-16-2007 11:15 PM
how to create trigger in MYSQL	Jeyaseelansarc	Database Support	5	07-10-2007 11:53 PM
how to retrieve the data after commit statement	vadivelanvaidyanathan	Database Support	3	03-21-2007 05:49 AM