2 Replies Latest reply: Jan 9, 2013 3:09 AM by user12064835 RSS

    Insert trigger with if condition

    user12064835
      Hi.

      I am new sql programming and I am trying to write a insert trigger.
      I have created an insert-trigger that inserts into another table(p_person) whenever the trigger table (p_personmds) gets insterted:

      CREATE OR REPLACE TRIGGER INSERT_P_PERSON_TG
      AFTER INSERT ON P_PERSONMDS
      FOR EACH ROW
      BEGIN
      insert into P_PERSON (person,person_id,person_name,email_address,disabled)
      values (seq_p_person.nextval,:new.person_id,:new.person_name,:new.email_address,:new.disabled);

      END INSERT_P_PERSON_TG;
      /

      After testing the trigger I discovered that I need to add some checking to the trigger:
      If person_id already exists in the p_person table I need to just update the 'DISABLED' column and set it to 'null' value.

      So I tried modifying the trigger:

      CREATE OR REPLACE TRIGGER TESTMDS.INSERT_P_PERSON_TG
      AFTER INSERT ON TESTMDS.P_PERSONMDS
      FOR EACH ROW
      BEGIN
      IF :new.disabled is not null
      THEN
      update p_person set disabled=NULL where person_id=:old.person_id;
      ELSE
      insert into P_PERSON (person,person_id,person_name,email_address,disabled)
      values (seq_p_person.nextval,:new.person_id,:new.person_name,:new.email_address,:new.disabled);
      END IF;
      END INSERT_P_PERSON_TG;
      /

      Hovewer the triggers seems to ignore the first if update condition and only inserts another row with mulitiple values.

      Anyone know what I am doeing wrong here?