ketan

• Jul 28th, 2005

 

Yes, it is possibale if your triggers is Autonomous Triggers than

Naidu

• Aug 22nd, 2005

 

Generally In triggers you can't use TCL commands.  
But you can use TCL commands in Autonomous Triggers.  
You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger. 
At a same time you have to end your trigger with commit/rollback. 
You can use these type of triggers to maintain log details of a table. 

Taral Desai

• Sep 2nd, 2005

 

it's possible through pragma autonomous_transaction

mahendra

• Sep 29th, 2005

 

If the trigger is declare as autonomous transaction, then it can have commit or rollback.

m

• Nov 12th, 2005

 

is it possible to use Transaction control command at block...

sureshchowdary

• May 3rd, 2007

 

Give me one example

Karthik

• May 4th, 2007

 

Autonomous Transaction is a feature of oracle 8i which maintains the state of
its transactions and save it , to affect with the commit or rollback of the
surrounding transactions.

Here is the simple example to understand this :-

ora816 SamSQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 BEGIN
5 INSERT into Test_Table_B(x) values (1);
6 Commit;
7 END ;
8 BEGIN
9 INSERT INTO Test_Table_A(x) values (123);
10 InsertInTest_Table_B;
11 Rollback;
12 END;
13 / PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A; X---------- 123
ora816 SamSQL :> Select * from Test_Table_B; X---------- 1

Notice in above pl/sql COMMIT at line no 6 , commits the transaction at
line-no 5 and line-no 9. The Rollback at line-no 11 actually did nothing.
Commit/ROLLBACK at nested transactions will commit/rollback all other DML
transaction before that. PRAGMA AUTONOMOUS_TRANSACTION override this behavior.
Let us the see the following example with PRAGMA AUTONOMOUS_TRANSACTION.

ora816 SamSQL :> declare
2 Procedure InsertInTest_Table_B
3 is
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT into Test_Table_B(x) values (1);
7 Commit;
8 END ;
9 BEGIN
10 INSERT INTO Test_Table_A(x) values (123);
11 InsertInTest_Table_B;
12 Rollback;
13 END;
14 /PL/SQL procedure successfully completed.
ora816 SamSQL :> Select * from Test_Table_A;no rows selected
ora816 SamSQL :> Select * from Test_Table_B; X---------- 1

With PRAGMA AUTONOMOUS_TRANSACTION, the transaction state maintained
independently. Commit/Rollback of nested transaction will no effect the other
transaction. It is advisable to increase the value of TRANSACTIONS parameter in
the INIT parameter file to allow for the extra concurrent transaction.

bambino_chakma

 Profile Answers by bambino_chakma 

• Jul 15th, 2007

 

Yes ...it is possible ,provide the trigger is autonomus or else table mutation occur

Autonomus trigger can be defined using the trigger beggining with

Pragma autonomus _trancation  and the trigger must have  a commit/rollback statment at the end of the trigger

jockey

• Aug 2nd, 2007

 

Using autonomous_transaction it is possible

g_sidhu

 Profile 

• Jan 31st, 2008

 

COMMIT, ROLLBACK, and SAVEPOINT statements are not allowed within the trigger body. It is possible to commit or rollback indirectly by calling a procedure, but it is not recommended because of side effects to transactions.