9 Replies Latest reply: Aug 4, 2013 5:04 PM by rp0428 RSS

    Commit in a trigger,Confusion

    979801

      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

          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

            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

              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

                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

                  Thank you.

                  • 6. Re: Commit in a trigger,Confusion
                    rp0428

                    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

                      Means is same as the isolated transaction

                      • 8. Re: Commit in a trigger,Confusion
                        Ishan

                        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

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