Update statement in database trigger

[anju123]

Hi Friends,
I need help in one update statement.
There is a table called wo_assigned_person(columns: work_order, person_name). Where there can be single workorder number with 1 or more person name. Basically that means a single workorder can be assigned to more than 1 people.
What I want to do is, when user updates or inserts in the wo_assigned_person table the inserted and updated record should go to re_assigned_workorder table with ‘T’ and ‘F’ flag (T is currently working and F is not working). The update written below work perfectly when there is only one person for a workorder, if I assign a single workorder for 2 people and then try to update it fails (basically it sets flag F for both people instead of setting flag ‘F’ for only that person who got updated). Did anyone get my point??
This is the trigger: just look at update statement


CREATE OR REPLACE TRIGGER B_IU_WAP
BEFORE INSERT OR UPDATE of person_name
ON wo_assigned_person
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
declare
err_num NUMBER;
err_msg VARCHAR2(100);
begin
if inserting then
insert into re_assigned_workorder
valuesnew.work_order,:new.PERSON_name,'T',sysdate,user);
else
if updating then
update re_assigned_workorder
set status_flag ='F'
where work_order = ld.work_order;
and person_name = ld.person_name;
insert into re_assigned_workorder
valuesnew.work_order,
:new.person_name,
'T',sysdate,user);
end if;
end if;
exception
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(err_msg);
end;

 

[Ygor]

Extraneous semicolon;

where work_order = ld.work_order;
and person_name = ld.person_name;

 

[anju123]

Sorry, that was typo.In Actual trigger its not there.

 

[anju123]

Thanks friend, i got the solution.