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

tacrawford_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;

Pragma autonomous_transaction acts as though it is a separate session. As you trigger's actions are pre-commit ... what is it you expect will be visible to the procedure?

My recommendation would be to either code what you want done into the trigger using the :OLD and :NEW environment variables, perhaps passing them to a procedure, or look into the possibility updating a view that has an INSTEAD OF trigger. I'm not wildly crazy about the second option.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org