Re: AFTER UPDATE Triggers....

An update is not totally complete even in the After Update trigger until a commit occurs. Oracle will not allow an Insert/Update/Delete trigger to query the table the query is based on. This can only be done in a statement level trigger.
What I have done is create a package into which the trigger passes the key info for the record and the package saves this. The package will then have a function that loops through the list of keys and calls your P_CHK_PCODE_GRP(:new.act_id,:new.empid) function. This package function is called by the Statement level trigger.

This is a big pain, but it is a documented big pain in some of the Oracle documentation.

In article <34C7BB1E.EC31369D_at_ii3.com>, nospam.ken_at_ii3.com says...
>
>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 Sun Jan 25 1998 - 00:00:00 CST