| update statement [message #83150] |
Tue, 12 August 2003 17:21  |
Anju
Messages: 33
Registered: September 2000
|
Member |
|
|
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
values(:new.work_order,:new.PERSON_name,'T',sysdate,user);
else
if updating then
update re_assigned_workorder
set status_flag ='F'
where work_order = :old.work_order;
and person_name = :old.person_name;
insert into re_assigned_workorder
values(:new.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;
|
|
|
|
| Re: update statement [message #83151 is a reply to message #83150] |
Wed, 13 August 2003 05:48  |
Flu
Messages: 1
Registered: August 2003
|
Junior Member |
|
|
where work_order = :old.work_order;
and person_name = :old.person_name;
Don't put seicolon write like the following
where work_order = :old.work_order
and person_name = :old.person_name
Hope it will work
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
