4 Replies Latest reply: Feb 15, 2013 6:05 PM by JustinCave RSS

    AFTER_INSERT Trigger

    user457349
      This is on Database 11g.

      When does the AFTER INSERT Trigger fired on a table.
      does it fire after insert and before committing the row
      
      or 
      
      does it fire after inserting and committing the row in the database.
      please advise.

      D

      Edited by: 952951 on Feb 15, 2013 3:10 PM
        • 1. Re: AFTER_INSERT Trigger
          JustinCave
          First, are you talking about a row-level trigger? Or a statement-level trigger?

          A row-level trigger fires after each row is inserted (before the next row is inserted). A statement-level trigger fires after the statement has finished. Control won't be returned to the caller that executed the statement until after all triggers are fired.

          Triggers have nothing whatsoever to do with transaction control. Commits come from the application.

          Justin
          • 2. Re: AFTER_INSERT Trigger
            user457349
            Thank you for quick reply. My problem is following. I have two triggers on two tables in a cascading manner as you can see below. They are row level triggers.


            Trigger1
            This trigger inserts into other table XX_SORT_PR_LINE_STATUS after inserting the table PO_REQUISITION_LINES_ALL
            CREATE OR REPLACE TRIGGER APPS.XX_PR_LINENUM_SORT
               AFTER INSERT
               ON PO_REQUISITION_LINES_ALL
               FOR EACH ROW
            WHEN (
            new.attribute7 IS NOT NULL AND new.attribute7 <> new.line_num
                  )
            
            DECLARE
               concreqid    INTEGER;
               returncode   BOOLEAN;
               noreq        EXCEPTION;
               erx          VARCHAR2 (2000);
               v_name       VARCHAR2 (240);
               v_email      VARCHAR2 (240);
               PRAGMA AUTONOMOUS_TRANSACTION;
            BEGIN
            
               SELECT user_name, email_address
                 INTO v_name, v_email
                 FROM fnd_user
                WHERE (user_id =  :new.created_by);
            
               INSERT INTO PO.XX_SORT_PR_LINE_STATUS (req_header_id,
                                                     req_num,
                                                     preparer_id,
                                                     preparer_name,
                                                     preparer_email,
                                                     sort_line_req_id,
                                                     req_import_req_id,
                                                     req_sort_created_by,
                                                     creation_date,
                                                     interface_source)
                    VALUES (:new.requisition_header_id,
                            null,
                            null,
                            v_name,
                            v_email,
                            concreqid,
                            :new.request_id,
                            :new.created_by,
                            :new.creation_date,
                           'ReqWizard');
            COMMIT;
              
            EXCEPTION
               WHEN noreq
               THEN
                  erx := 'XXSORTPRNUM: Can not Generate Request';
                  _DEBUG_PROC (erx);
               WHEN OTHERS
               THEN
                  erx := 'XXSORTPRNUM-' || SQLERRM;
            
                  _DEBUG_PROC (erx);
            END;
            Trigger2
            The another after insert trigger on XX_SORT_PR_LINE_STATUS should update lines on PO_REQUISITION_LINES_ALL and that is not happening. I do not see updates even though all :NEW bind variables have values..
            Why it is not updating PO_REQUISITION_LINES_ALL.. ? it is the same table that has AFTER INSERT in Trigger1 above
            CREATE OR REPLACE TRIGGER PO.XX_SORT_PR_LINE_STATUS_T
               AFTER INSERT
               ON PO.XX_SORT_PR_LINE_STATUS    FOR EACH ROW
            WHEN (
            new.interface_source = 'ReqWizard'
                  )
            DECLARE
               concreqid    INTEGER;
               returncode   BOOLEAN;
               noreq        EXCEPTION;
               erx          VARCHAR2 (2000);
               v_name       VARCHAR2 (240);
               v_email      VARCHAR2 (240);
               anum number;
               PRAGMA AUTONOMOUS_TRANSACTION;
            
               CURSOR headers
               IS
                  SELECT DISTINCT req_header_id
                    FROM po.XX_SORT_PR_LINE_STATUS
                   WHERE interface_source = 'ReqWizard'
            --             AND req_header_id = :new.req_header_id
                         AND status_code is null;
            
               --          and segment1 = '105200249' ;
            
               CURSOR lines (
                  p_header_id NUMBER)
               IS
                    SELECT requisition_header_id,
                           requisition_line_id,
                           line_num,
                           TO_NUMBER (attribute7) attribute7
                      FROM po_requisition_lines_all
                     WHERE requisition_header_id = p_header_id
                           AND line_num <> TO_NUMBER (attribute7)
                           AND attribute7 is not null
                  ORDER BY attribute7;
            BEGIN
            
            apps._DEBUG_PROC ('Step1');
            
               FOR i IN headers
               LOOP
               apps._DEBUG_PROC ('Step2-'||to_char(i.req_header_id));
                  apps._DEBUG_PROC ('Step2-'||to_char(:new.req_header_id));
                     
                  FOR k IN lines (i.req_header_id)
                  LOOP
                  apps._DEBUG_PROC ('Step3-'||to_char(i.req_header_id));
                      apps._DEBUG_PROC ('Step3-'||to_char(:new.req_header_id));
                     IF k.line_num <> k.attribute7
                     THEN
                     apps._DEBUG_PROC ('Step4-'||to_char(i.req_header_id));
                               apps._DEBUG_PROC ('Step4-'||to_char(:new.req_header_id));
                        UPDATE po_requisition_lines_all
                           SET line_num = attribute7
                         WHERE requisition_header_id = k.requisition_header_id;
            
                   
                        COMMIT;
                     END IF;
                  END LOOP;
                  apps._DEBUG_PROC ('Step5');
                  --UPDATE  po.XX_SORT_PR_LINE_STATUS set status_code = 'U' where req_header_id = i.req_header_id; COMMIT;
               END LOOP;
               apps._DEBUG_PROC ('StepXx-'||to_char(:new.req_header_id));
              --  FOR j IN lines (:new.req_header_id)
                --  LOOP
                  apps._DEBUG_PROC ('StepX-'||to_char(:new.req_header_id));
                      apps._DEBUG_PROC ('StepX-'||:new.req_header_id);
            --         IF j.line_num <> j.attribute7
            --         THEN
                     apps._DEBUG_PROC ('StepX-'||to_char(:new.req_header_id));
                               apps._DEBUG_PROC ('StepX-'||:new.req_header_id);
                               
                               anum := :new.req_header_id;
                               
                        UPDATE po_requisition_lines_all
                           SET line_num = to_number(attribute7)
                         WHERE requisition_header_id =:new.req_header_id;
            
                   
                        COMMIT;
            --         END IF;
                  --END LOOP;
            EXCEPTION
               WHEN OTHERS
               THEN
                  erx := 'XX_PR_LINENUM_CHANGE_LINE-' || SQLERRM;
                 apps._DEBUG_PROC (erx);
            END;
            /
            Please advise.

            Thanks,
            D
            • 3. Re: AFTER_INSERT Trigger
              rp0428
              >
              This trigger inserts into other table XX_SORT_PR_LINE_STATUS after inserting the table PO_REQUISITION_LINES_ALL
              >
              Yes - and it does that even if the insert into PO_REQUISITION_LINES_ALL fails or gets rolled back.

              Why do you want to insert on the status table even if the insert into the lines table never happens?

              And if you want to know why things are failing why are you throwing away the information that might tell you?
              EXCEPTION
                 WHEN noreq
                 THEN
                    erx := 'XXSORTPRNUM: Can not Generate Request';
                    _DEBUG_PROC (erx);
                 WHEN OTHERS
                 THEN
                    erx := 'XXSORTPRNUM-' || SQLERRM;
               
                    _DEBUG_PROC (erx);
              END;
              Get rid of the exception handlers altogether until you get the code working properly so Oracle can tell you when things go wrong.

              And NEVER use WHEN OTHERS or a handler that doesn't either handle the exception or RAISE it again after logging it.
              • 4. Re: AFTER_INSERT Trigger
                JustinCave
                Why are your triggers declared to use autonomous transactions?

                I'm assuming that you are trying to work around a mutating table exception. By doing so, however, your autonomous transactions cannot see the uncommitted work of the parent transaction. So your XX_SORT_PR_LINE_STATUS_T trigger can't see the row that was inserted into the PO_REQUISITION_LINES_ALL table that caused the XX_PR_LINENUM_SORT trigger to fire.

                Do not use autonomous transactions to work around mutating table exceptions.

                Justin