This discussion is archived
7 Replies Latest reply: Feb 21, 2013 6:52 AM by Paul Horth RSS

Trigger Issue while updating same table

user546710 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    <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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?

Legend

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