On Dec 19, 9:55 am, tacrawf...@adelphia.net wrote:
> I have a trigger that calls a stored proc on update for table1. When
> the stored proc queries table1 it can not see the new data. The user
> modifies the TIME column, but the proc still queries the old data. How
> can I fix that?
>
> Trigger:
>
> CREATE OR REPLACE TRIGGER trigger1
> after update on table1
> for each row
> declare
> pragma autonomous_transaction;
> begin
> proc1 (:new.name);
> commit;
> end;
>
> Stored Proc:
>
> create or replace procedure proc1 (v_name varchar2) as
> cursor c_tbl is
> select id, name, time
> from table1
> where name = v_name;
>
> v_total_time number;
>
> begin
> for crsr in c_tbl loop
> v_total_time := v_total_time + nvl ( crsr.time, 0 );
> end loop;
> end;


When you call a procedure from a database table trigger you normally
pass the procedure the row data that it is to work with. If you try to
work with the table that the trigger is defined on then you will likely
end up with a "mutating table" error.

HTH -- Mark D Powell --