Insert trigger with if condition
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?