Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> AFTER UPDATE Triggers....

AFTER UPDATE Triggers....

From: Kenneth Brown <nospam.ken_at_ii3.com>
Date: 1998/01/22
Message-ID: <34C7BB1E.EC31369D@ii3.com>#1/1

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 = aid
 INTERSECT
  select jobid,grp_id
  from enrgrps
  where enrgrps.empid = empl;

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;

    end LOOP;
    close GRPS;
end;

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;

--

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To reply remove the 'nospan.' from my posted email address
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Received on Thu Jan 22 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US