7 Replies Latest reply: Feb 21, 2013 8:52 AM by Paul Horth RSS

    Trigger Issue while updating same table

    user546710
      Hi all,

      I am creating one after insert trigger on table tests, which will check, if application id is null, application will be fetched from another table and update the table
      tests table. But this trigger is not updating the application id of the table tests


      CREATE OR REPLACE
      TRIGGER TB_REC_APPL_TESTS1
      AFTER INSERT ON tests FOR EACH ROW

      DECLARE

      v_application_id NUMBER;
      v_rec_appl_tests_id NUMBER;
      v_a_recruit_id NUMBER;
      PRAGMA AUTONOMOUS_TRANSACTION;
      BEGIN
      v_rec_appl_tests_id := :NEW.rec_appl_tests_id;
      v_a_recruit_id := :NEW.a_recruit_id;

      IF :NEW.a_applic_id IS NULL THEN
      SELECT a_applic_id INTO v_application_id FROM recruit WHERE a_recrut_id = v_a_recruit_id;

      dbms_output.PUT_LINE(v_application_id||'-'||v_rec_appl_tests_id);
      UPDATE tests SET a_applic_id = v_application_id
      WHERE rec_appl_tests_id = v_rec_appl_tests_id;--:NEW.rec_appl_tests_id;
      END IF;
      commit;
      END;
      /

      Thanks in advance,
      Pal
        • 1. Re: Trigger Issue while updating same table
          Chanchal Wankhade
          user546710 wrote:
          Hi all,

          I am creating one after insert trigger on table tests, which will check, if application id is null, application will be fetched from another table and update the table
          tests table. But this trigger is not updating the application id of the table tests


          CREATE OR REPLACE
          TRIGGER TB_REC_APPL_TESTS1
          AFTER INSERT ON tests FOR EACH ROW

          DECLARE

          v_application_id NUMBER;
          v_rec_appl_tests_id NUMBER;
          v_a_recruit_id NUMBER;
          PRAGMA AUTONOMOUS_TRANSACTION;
          BEGIN
          v_rec_appl_tests_id := :NEW.rec_appl_tests_id;
          v_a_recruit_id := :NEW.a_recruit_id;

          IF :NEW.a_applic_id IS NULL THEN
          SELECT a_applic_id INTO v_application_id FROM recruit WHERE a_recrut_id = v_a_recruit_id;

          dbms_output.PUT_LINE(v_application_id||'-'||v_rec_appl_tests_id);
          UPDATE tests SET a_applic_id = v_application_id
          WHERE rec_appl_tests_id = v_rec_appl_tests_id;--:NEW.rec_appl_tests_id;
          END IF;
          commit;
          END;
          /

          Thanks in advance,
          Pal
          you are creating triger on the table and updating it. It will not allow to update since you are firing trigger on same table.
          Best practice is to use other table to update or any other operation.
          • 2. Re: Trigger Issue while updating same table
            odie_63
            CREATE OR REPLACE TRIGGER TB_REC_APPL_TESTS1
            AFTER INSERT ON tests 
            FOR EACH ROW
            DECLARE
            
              v_application_id NUMBER;
            
            BEGIN
            
              IF :NEW.a_applic_id IS NULL THEN
               
                SELECT a_applic_id INTO v_application_id FROM recruit WHERE a_recrut_id = :NEW.a_recruit_id;
            
                :NEW.a_applic_id := v_application_id;
                
              END IF;
            
            END;
            /
            • 3. Re: Trigger Issue while updating same table
              Karthick_Arp
              <pre>
              create or replace trigger tb_rec_appl_tests1 after insert on tests for each row
              declare
                   v_application_id number;
                   v_rec_appl_tests_id number;
                   v_a_recruit_id number;

                   <font size = 3 color = "red">pragma autonomous_transaction; -- EVIL EVIL EVIL</font>
              begin
                   v_rec_appl_tests_id := :new.rec_appl_tests_id;
                   v_a_recruit_id := :new.a_recruit_id;

                   if :new.a_applic_id is null then
                        select a_applic_id
                        into v_application_id
                        from recruit
                        where a_recrut_id = v_a_recruit_id;

                        dbms_output.put_line(v_application_id||'-'||v_rec_appl_tests_id);
                   
                        update tests
                        set a_applic_id = v_application_id
                   where rec_appl_tests_id = v_rec_appl_tests_id;
                   end if;

                   <font size = 3 color="red">commit; -- EVIL EVIL EVIL</font>
              end;
              /
              </pre>

              Your code is EVIL. Never, I say again NEVER commit in a trigger. Just don't do it NEVER.

              Now tell us your business requirement. We could come up with a better solution than the one above.
              • 4. Re: Trigger Issue while updating same table
                user546710
                Hi,


                Thank you very much for your reply. AFAIK, for AFTER trigger, we cannot assign values to :NEW. Am i correct ?


                Thanks,
                Venu
                • 5. Re: Trigger Issue while updating same table
                  odie_63
                  Thank you very much for your reply. AFAIK, for AFTER trigger, we cannot assign values to :NEW. Am i correct ?
                  Yes, missed that.

                  Is BEFORE not suitable?
                  • 6. Re: Trigger Issue while updating same table
                    user546710
                    Hi,

                    Yes, by using, BEFORE trigger, we can do it. But any idea why, it is not updating while using AFTER trigger. Is it Oracle behavior ?

                    Thanks,
                    Venu
                    • 7. Re: Trigger Issue while updating same table
                      Paul  Horth
                      user546710 wrote:
                      Hi,

                      Yes, by using, BEFORE trigger, we can do it. But any idea why, it is not updating while using AFTER trigger. Is it Oracle behavior ?

                      Thanks,
                      Venu
                      So, AFTER you do the insert, you expect it to work?

                      How could it?, you've already inserted.

                      I think you're confused. Your original idea was, after the insert, do an update on the row you've just inserted.
                      You can't do that in a trigger.
                      What you can do, in a before trigger, is intercept the row before it gets inserted and change the data using :new.col = <something>

                      Does that make sense?