Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Insert trigger with if condition

user12064835Jan 9 2013 — edited Jan 10 2013
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2013
Added on Jan 9 2013
16 comments
17,204 views