This discussion is archived
9 Replies Latest reply: Aug 4, 2013 3:04 PM by rp0428 RSS

Commit in a trigger,Confusion

979801 Newbie
Currently Being Moderated

Hello experts,

I am new in oracle using oracle sql developer in windows7.I am little bit confuse in that I am using commit in a trigger as given :

create or replace
trigger comt after insert on tbl_city
declare
pragma autonomous_transaction;
begin
commit;
dbms_output.put_line('Value is committed');
end;

Now when I perform an

insert into tbl_city values (1,'XYZ',1);

in tbl_city---->trigger fires properly and gives an output stream

.Value is committed

But If I perform

rollback 

now --->there are the data rollbacked in table.

why this is happen ?I think after commit(which is in trigger associated at insert to table)there should no any rollback in table.

Please give me solution.

Thank You

regards

aaditya.

  • 1. Re: Commit in a trigger,Confusion
    BCV Journeyer
    Currently Being Moderated

    Hi,

         pragma autonomous_transaction here works to commit the particular trnsaction,

    In Triggers " Do All transaction or else nothing" ... (i.e )Rollback  to previous stage, so commit some changes alone they will use pragma autonomous_transaction , to Commit the Transaction.

     

     

    Cheers...

  • 2. Re: Commit in a trigger,Confusion
    Solomon Yakobson Guru
    Currently Being Moderated

    Autonomous transaction opens a separate transaction which is no different from transactions, say, opened by other sessions. It is executed in a separate context and same as any other transaction doesn't see any (and therefore can neither commit nor rollback) uncommitted changes made by invoking transaction. All it can do is commit/rollback own changes.

     

    SY.

  • 3. Re: Commit in a trigger,Confusion
    BCV Journeyer
    Currently Being Moderated

    Hi,

     

        For your Better Understand of Autonomous transaction., follow My Code, feel the magic of Autonomous transaction.

     

    Step1: Execute this block without giving commit.

     

    Create Table emp1(sal number);    

            

    declare

    begin

    for i in 1..5 loop

          INSERT INTO emp1

                      (sal

                      )

               VALUES (i

                      );

                      end loop;

    end;

     

     

    Step 2: Execute this Block used Autonomous transaction.

     

    DECLARE

     

    PRAGMA AUTONOMOUS_TRANSACTION;

    BEGIN

       BEGIN

          for i in 1..10 loop

          INSERT INTO emp1

                      (sal

                      )

               VALUES (i

                      );

                      end loop;

                  

    commit;               

    END;

         

    Totally 15 rows u have inserted into emp table on SAL column right.

     

    Now Give ROLLBACK...

     

    And issue select statement now,

    Select * from emp1;

     

    you can get to know still the 10 rows remaing .. that's the use of PRAGMA AUTONOMOUS_TRANSACTION.

    for commiting the session wise transactions...

    Cheers... !

  • 4. Re: Commit in a trigger,Confusion
    S10390 Journeyer
    Currently Being Moderated

    Pragma autonomous_transaction means, Commit only this transaction ( where you have specified this PRAGMA), by suspending all the other transactions.

  • 5. Re: Commit in a trigger,Confusion
    979801 Newbie
    Currently Being Moderated

    Thank you.

  • 6. Re: Commit in a trigger,Confusion
    rp0428 Guru
    Currently Being Moderated

    S10390 wrote:

     

    Pragma autonomous_transaction means, Commit only this transaction ( where you have specified this PRAGMA), by suspending all the other transactions.

    NO - NO - NO!

     

    It doesn't mean 'COMMIT' anything at all. And it doesn't SUSPEND 'all the other transactions'.

     

    The pragma only causes the suspension of the CURRENT transaction and the creation of a new one.

     

    It is up to that new transaction to perform EITHER a COMMIT or a ROLLBACK before completing.

     

    Autonomous transactions can be rolled back just like any other transaction.

  • 7. Re: Commit in a trigger,Confusion
    979801 Newbie
    Currently Being Moderated

    Means is same as the isolated transaction

  • 8. Re: Commit in a trigger,Confusion
    Ishan Journeyer
    Currently Being Moderated

    Lets say we have transaction T1.

     

    Somewhere in T1 we are calling Pragma autonomous_transaction, lets call it T2.

     

    While executing T1, as and when the execution reaches to T2, T1 gets suspended while T2 is getting executed. After T2, is completed, the control returns back to T1 again.

     

    Thanks,

    Ishan

  • 9. Re: Commit in a trigger,Confusion
    rp0428 Guru
    Currently Being Moderated

    And saying that T2 'is completed' means that T2 issued a COMMIT (explicitly or implicityly) or issued a ROLLBACK.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points