binurajnair

 Profile Answers by binurajnair 

• Feb 21st, 2008

 

Commit and roll back statements are not possible from triggers. Although we can create triggers with COMMIT and ROLLBACK, it will result in an error when the trigger has fired.

Ideally this is desirable since the original transaction may be rolled back and so the effects of trigger also should be rolled back.

However we can use commit if it is declared as an autonomous transaction. Generally that will be used for writing to log tables.

maheshveeragoni

 Profile Answers by maheshveeragoni Questions by maheshveeragoni

• Feb 27th, 2008

 

what is automonous tranction

binurajnair

 Profile Answers by binurajnair 

• Feb 28th, 2008

 

      An autonomous transaction is an independent transaction which can be committed independent of other transactions. An autonomous transaction will be committed with out committing the other non-autonomous transactions are committed.

The following exercise will help you to understand the difference between autonomous and normal transactions.

Create a table for the exercise.

create table test_table (a varchar2(50));

Create a non-autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE non_autonomous_proc
IS
BEGIN
   INSERT INTO test_table
   VALUES      ('Non Autnomous Insert');

   COMMIT;
END;

Now execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Non Autnomous Insert');

   non_autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Non Autnomous Insert');

   ROLLBACK;
END;

Now query the table

select * from test_table;

As expected 2 rows will be retrieved

A
--------------------------------------------------
Before Non Autnomous Insert
Non Autnomous Insert

Now we will see the case of autonomous transaction.

Clear the test_table

delete from test_table;
commit;

Create an autonomous procedure which will insert one row to this table.

CREATE OR REPLACE PROCEDURE autonomous_proc
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO test_table
   VALUES      ('Autnomous Insert');

   COMMIT;
END;

Now Execute the following code

DECLARE
BEGIN
   INSERT INTO test_table
   VALUES      ('Before Autnomous Insert');

   autonomous_proc;

   INSERT INTO test_table
   VALUES      ('After Autnomous Insert');

   ROLLBACK;
END;

And query the table

select * from test_table;

This will show only one row

A
--------------------------------------------------
Autnomous Insert

Conclusion

1) The commit statement in the autonomous procedure will commit the DML operations in the autonomous procedure without commiting the transactions before that.

2) The rollback statement caused both the inserts before and after the autonomous transaction to be rolled back, but not the autonomous transaction

arbind1982

 Profile Answers by arbind1982 

• Mar 12th, 2008

 

we can use commit or rolback in trigger without error by using "PRAGMA AUTONOMOUS_TRANSACTION;" on declaration section




CREATE TRIGGER anniversary_trigger
BEFORE INSERT ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
-- Only commits the preceding INSERT, not the INSERT that fired
-- the trigger.
COMMIT;
EXCEPTION
-- If someone else was hired on the same day, we get an exception
-- because of duplicate values. That's OK, no action needed.
WHEN OTHERS THEN NULL;
END;
/

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 31st, 2008

 

We can use commit and rollback in trigers using autonomous transactions.

- An atonomous transaction is an independent transaction initiated by another transaction.
- When an atonomous transaction is called the parent transaction is temporarly suspended.
- An atonomous transaction allows us to sub divide a transaction into multiple rollback and commit transactions.
- We can  commit or rollback an autonomous transaction  independent of parent transaction or without effecting parent transaction.
- It should be committed or rollback before return control to the calling transaction.

You can make a transaction as autonomous transaction by simply adding PRAGMA AUTONOMOUS_TRANSACTION in declarative part of the statement.