1 2 Previous Next 17 Replies Latest reply: May 10, 2012 9:32 AM by 936241 RSS

    How to rollback a trigger when transaction rolls back?

    936241
      Hello

      I need to create an audit trigger, to log all the deletes from a base table CLNT_BOOK. The trigger will copy the deleted row plus some additional info like username and date into the log table BOOK_log. The trigger has to roll back if the delete process rolls back. If I am not mistaken, a trigger should automatically roll back when the transaction rolls back. So I don't really have to write any code to accomplish that, right?
      However, it doesn't look so.
      Here is my trigger:

      CREATE OR REPLACE TRIGGER BOOK_DEL
      before DELETE
      ON CLNT_BOOK
      FOR EACH ROW

      DECLARE
      --PRAGMA AUTONOMOUS_TRANSACTION;
      v_DELETE CHAR(1) := 'D';
      v_username varchar2(10);
      v_sys_error NUMBER := 0;

      BEGIN
      SELECT user INTO v_username FROM dual;

      INSERT INTO BOOK_log
      VALUES ( :OLD.ACCOUNT_NUMBER , :OLD.AMOUNT , :OLD.QTY , :OLD.PROCESS_DATE , :OLD.STATUS , :OLD.LAST_UPDATED_DATE, :OLD.UPDATED_BY , v_DELETE, v_username , SYSDATE , SYS_GUID() , to_char(sysdate, 'HH:MM:SS'));
      -- COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      v_sys_error := SQLCODE;
      END;
      /


      I delete rows from base table, then roll back.

      delete CLNT_BOOK where ENT_ID ='0001A'
      /
      rollback work
      /

      However the deleted rows stay deleted, and when I select from log table, select * from BOOK_log, the deleted rows are in the log table.
      What am I missing here??? Please help!

      Thanks in advance
        • 1. Re: How to rollback a trigger when transaction rolls back?
          sb92075
          933238 wrote:
          EXCEPTION
          WHEN OTHERS THEN
          v_sys_error := SQLCODE;
          END;
          /
          above is bug waiting to bite you.
          The whole, complete & entire EXCEPTION handler should be removed & deleted.
          • 2. Re: How to rollback a trigger when transaction rolls back?
            JustinCave
            As a general matter, not one that I expect is affecting you here, a WHEN OTHERS exception block that doesn't re-raise the error is almost certainly a bug waiting to happen. In this case, if your INSERT statement fails, your exception block is swallowing the exception so the caller has no idea there was a problem. That is a bug waiting to happen-- the exception block should be removed entirely in this case.

            What tool are you using to execute the DELETE? Whatever tool you are using, it sounds like you've asked it to run in autocommit mode so the DELETE is committed as soon as it returns successfully. You shouldn't run any tool in autocommit mode so you'll probably need to find out where that setting is made in your tool and undo it.

            Justin
            • 3. Re: How to rollback a trigger when transaction rolls back?
              936241
              I see... I'm runing from aqua data studio. I will try sqlplus now.

              Edited by: 933238 on May 9, 2012 2:28 PM
              • 4. Re: How to rollback a trigger when transaction rolls back?
                sb92075
                933238 wrote:
                Ok. ... what's wrong with THAT part?
                For reason why check these links.

                http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

                http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

                http://tkyte.blogspot.com/2007/03/challenge.html
                • 5. Re: How to rollback a trigger when transaction rolls back?
                  SamFisher
                  Use Raise in exception block.
                  • 6. Re: How to rollback a trigger when transaction rolls back?
                    936241
                    You were correct. I ran my delete from sqlplus, it returned an error:

                    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                    Probably, like you said, it was "swallowed" by my exception. Now that I dropped the exception, I got the error.
                    • 7. Re: How to rollback a trigger when transaction rolls back?
                      Peter Gjelstrup
                      SamFisher wrote:
                      Use Raise in exception block.
                      No. Read second link in post above yours. And then do as others already said, remove it all together.


                      All that this does
                      exception
                         when others then
                            raise;
                      Is to lose the error stack, and hide what actually happened where.

                      If you cannot handle it, or add value to it => Do not catch it.
                      • 8. Re: How to rollback a trigger when transaction rolls back?
                        936241
                        It worked !! :)

                        I used sqlplus, not data studio (probably it does autocommits). I ran this sequence of commands:
                        select count(*) from CLNT_BOOK where ENT_ID ='0000RAJ2H000002A'
                        6
                        select count(*) from BOOK_log
                        0

                        delete CLNT_BOOK where ENT_ID ='0000RAJ2H000002A'
                        6 rows deleted.

                        select count(*) from CLNT_BOOK where ENT_ID ='0000RAJ2H000002A'
                        0
                        select count(*) from BOOK_log
                        6

                        rollback work

                        select count(*) from CLNT_BOOK where ENT_ID ='0000RAJ2H000002A'
                        6
                        select count(*) from BOOK_log
                        0

                        So, since my trigger rolled back the deletes... it means that the trigger doesn't require any additional code to handle rollback, correct? Here is my current trigger code:


                        CREATE OR REPLACE TRIGGER BOOK_DEL
                        after DELETE
                        ON CLNT_BOOK
                        FOR EACH ROW
                        DECLARE
                        v_DELETE VARCHAR2(1) := 'D';
                        v_username varchar2(30);
                        v_sys_error NUMBER := 0;

                        BEGIN
                        SELECT user INTO v_username FROM dual;

                        INSERT INTO BOOK_log
                        [insert statement here]

                        END;
                        /
                        • 9. Re: How to rollback a trigger when transaction rolls back?
                          JustinCave
                          933238 wrote:
                          So, since my trigger rolled back the deletes... it means that the trigger doesn't require any additional code to handle rollback, correct? Here is my current trigger code:
                          Correct. The changes the trigger makes are part of the transaction-- they will be committed or rolled back as part of the caller's transaction.
                          CREATE OR REPLACE TRIGGER BOOK_DEL
                          after DELETE
                          ON CLNT_BOOK
                          FOR EACH ROW
                          DECLARE
                          v_DELETE VARCHAR2(1) := 'D';
                          v_username varchar2(30);
                          v_sys_error NUMBER := 0;

                          BEGIN
                          SELECT user INTO v_username FROM dual;

                          INSERT INTO BOOK_log
                          [insert statement here]

                          END;
                          /
                          - You don't need the v_sys_error variable any more
                          - It's fine to populate v_username like this. But you could also eliminate v_username and just use the USER function in your INSERT statement just like you do with the other functions SYSDATE and SYS_GUID
                          VALUES ( :OLD.ACCOUNT_NUMBER , :OLD.AMOUNT , :OLD.QTY , :OLD.PROCESS_DATE , 
                                   :OLD.STATUS , :OLD.LAST_UPDATED_DATE, :OLD.UPDATED_BY , 
                                   v_DELETE, USER, SYSDATE , SYS_GUID() , to_char(sysdate, 'HH:MM:SS'));
                          Justin
                          • 10. Re: How to rollback a trigger when transaction rolls back?
                            936241
                            Guys, you are great! Thank you very much!



                            One more thing... Since I dropped my exception, what if any error occurs, how do I catch it than?

                            Edited by: 933238 on May 9, 2012 3:16 PM
                            • 11. Re: How to rollback a trigger when transaction rolls back?
                              sb92075
                              933238 wrote:
                              Guys, you are great! Thank you very much!



                              One more thing... Since I dropped my exception, what if any error occurs, how do I catch it than?
                              Why do you feel compelled to do something when error occurs?
                              • 12. Re: How to rollback a trigger when transaction rolls back?
                                JustinCave
                                933238 wrote:
                                One mor thing... Since I droped my exception, what if any error occurs, how do I catch it than?
                                You don't.

                                If you're not expecting an exception and you can't do something useful if an exception is thrown, you ought to let the exception propagate up the stack. You generally should only be catching particular exceptions that you expect and can do something about. For example, if I have a function to get an employee's salary, if someone passes in an invalid EMPNO value, I can catch that specific error (no_data_found) and raise a more useful exception.
                                CREATE FUNCTION get_sal( p_empno IN emp.empno%type )
                                  RETURN number
                                IS
                                  l_sal emp.sal%type;
                                BEGIN
                                  BEGIN
                                    SELECT sal
                                      INTO l_sal
                                      FROM emp
                                     WHERE empno = p_empno;
                                  EXCEPTION
                                    WHEN no_data_found
                                    THEN
                                      RAISE_APPLICATION_ERROR( -20001, 'There is no employee with the empno ' || p_empno, true );
                                  END;
                                
                                  RETURN l_sal;
                                END;
                                If some other error happens-- if, for example, I get an error saying that a block in an index is corrupt, I can't do anything useful to recover from that or to provide more information about the error to the caller so I should let that exception get thrown.

                                Occasionally, it would make sense to have a WHEN OTHERS exception handler that logs the error to a table and then calls RAISE to re-raise the error. You have to be careful there, however, because it's easy to lose information about the specific line that actually generated the error.

                                Justin
                                • 13. Re: How to rollback a trigger when transaction rolls back?
                                  936241
                                  Isn't that a common practice to handle an error? you know, if error occurs do this and that... Say, just for example, if error occurs withing the trigger itself, I may want to roll back the whole delete transaction, or create some "try again" procedure.
                                  Ok, in this case : If they issue delete statement with "where" clause that doesn't have any match in the table, say there is no such ENT_ID, it would not be correct to log it, as no actual delete will happen. Not sure if that generates an error though.

                                  Edited by: 933238 on May 9, 2012 3:26 PM
                                  • 14. Re: How to rollback a trigger when transaction rolls back?
                                    SamFisher
                                    Thank You.
                                    Here's the demo that will be helpful for others.
                                    Error starting at line 23 in command:
                                    DECLARE
                                      V VARCHAR2(10);
                                      v_sys_error varchar2(200);
                                    BEGIN
                                      SELECT DUMMY INTO V FROM DUAL WHERE DUMMY = '2';
                                    END;
                                    Error report:
                                    ORA-01403: no data found
                                    ORA-06512: at line 5
                                    01403. 00000 -  "no data found"
                                    
                                    
                                    Error starting at line 23 in command:
                                    DECLARE
                                      V VARCHAR2(10);
                                      v_sys_error varchar2(200);
                                    BEGIN
                                      SELECT DUMMY INTO V FROM DUAL WHERE DUMMY = '2';
                                    EXCEPTION
                                      WHEN OTHERS THEN 
                                      RAISE;
                                    END;
                                    Error report:
                                    ORA-01403: no data found
                                    ORA-06512: at line 8
                                    01403. 00000 -  "no data found"
                                    We do not get the exact line number where exactly the problem is.
                                    1 2 Previous Next