Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> AFTER UPDATE Triggers....
Anyone.....
Could someone explain why after update triggers (or how i can make) runnig using functions that validated data set in the table fail because of mutable errors.... ?
Don't after update triggers run AFTER the update has been preformed?
If I execute the procedure it works fine... when it is associated with a trigger it dies!!!
If it is impossible to associate this procedure with a trigger how can i automate the addition of act_id's in my ENRACTS??? (i need to know what act_id's were added to know what ENRGRPS to update....
If you can help or need to see a img of the physical tables please reply
TO mail me you have to remove the 'nospan.' from my address
Thank you
Ken B.
TRIGGER ---
create or replace trigger tr_ENRACTS_PCODE_a_u
after update of progresscd on ENRACTS
for each row
begin
P_CHK_PCODE_GRP(:new.act_id,:new.empid)
end;
/
PROCEDURE ---
create or replace procedure P_CHK_PCODE_GRP( act IN varchar2, emp IN
varchar2 )
AS
cursor GRPS (empl varchar2, aid varchar2) is
select grp2job.jobid,grp2job.grp_id
from grp2acts,grp2job
where grp2job.grp_id=grp2acts.grp_id
and grp2acts.act_id = aidINTERSECT
grp varchar2(9);
job varchar2(9);
begin
open GRPS (emp, act);
LOOP
FETCH GRPS INTO job,grp; exit when GRPS%notfound; if F_STAT_GRP(emp,grp,job,'NST')!= 0 and F_STAT_GRP(emp,grp,job,'COM') != 0 then update enrgrps set progresscd='INP' where (empid=emp and grp_id=grp and jobid=job); elsif F_STAT_GRP(emp,grp,job,'NST') = 0 and F_STAT_GRP(emp,grp,job,'COM') !=0 then update enrgrps set progresscd='COM' where (empid=emp and grp_id=grp and jobid=job); elsif F_STAT_GRP(emp,grp,job,'NST') !=0 and F_STAT_GRP(emp,grp,job,'COM') = 0 then update enrgrps set progresscd='NST' where (empid=emp and grp_id=grp and jobid=job); end if;
FUNCTION ---
create or replace function F_STAT_GRP
(emp IN varchar2, grp IN VARCHAR2, jid IN VARCHAR2, pcd IN VARCHAR2)
return number
IS
stat number;
BEGIN
select count(*) into stat
from enracts
where empid=emp
and progresscd=pcd
and act_id
in (select act_id from grp2acts where grp_id = grp
intersect select act_id from acts2job where jobid = jid);return(stat);
END;
--Received on Thu Jan 22 1998 - 00:00:00 CST
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To reply remove the 'nospan.' from my posted email address
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~