Re: procedure called by trigger can't see new data

On Dec 19, 9:55 am, tacrawf..._at_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 -- Received on Tue Dec 19 2006 - 09:49:41 CST