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;

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

The "pragma autonomous_transaction" keeps this from happening. The
other problem is that while the user updates one record with a
particular keyfield (name), I need to calculate all records that have
the same value in the keyfield (name).

Mark D Powell wrote:
>
> 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 --

tacrawford@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

tacrawford@adelphia.net a écrit :

> The "pragma autonomous_transaction" keeps this from happening. The
> other problem is that while the user updates one record with a
> particular keyfield (name), I need to calculate all records that have
> the same value in the keyfield (name).
>
> Mark D Powell wrote:
> >
> > 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.
> >

Dear,

Note that the "mutating table " is actually protecting us from
nasty mistakes. You may be on a dangereous path if you are
trying to circumvent it with an autonomous transaction ...

Tom Kyte discusses the issue at length in his
(highly recommended) books. If you don't have them,
you may want to browse the articles at
http://asktom.oracle.com - search for "mutating table"

You will - notably - find there an article documenting a
pattern to achieve your goal.

Regards

--- Raoul

I have a table that contains project data (table 1). I also have
a table that contains the starting/ending dates of each project (table
2). When I modify the required_weeks column, I need to re-calculate
the starting/ending dates for all projects in that resource slot.
What had been done was an AFTER UPDATE trigger on the project
table (table 1) that called a stored proc to calculate the
starting/ending dates for all projects in the same resource slot of the
modified project.
These starting/ending dates were queried from the project table
(table 1), math was applied, then they were written to the dates table
(table 2).
The problem that exists is that apparently the data is not
committed when the trigger runs, causing the stored proc to query the
OLD data when it is trying to calculate the starting/ending dates.


hasta_l3@hotmail.com wrote:
> tacrawford@adelphia.net a écrit :
>
> > The "pragma autonomous_transaction" keeps this from happening. The
> > other problem is that while the user updates one record with a
> > particular keyfield (name), I need to calculate all records that have
> > the same value in the keyfield (name).
> >
> > Mark D Powell wrote:
> > >
> > > 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.
> > >
>
> Dear,
>
> Note that the "mutating table " is actually protecting us from
> nasty mistakes. You may be on a dangereous path if you are
> trying to circumvent it with an autonomous transaction ...
>
> Tom Kyte discusses the issue at length in his
> (highly recommended) books. If you don't have them,
> you may want to browse the articles at
> http://asktom.oracle.com - search for "mutating table"
>
> You will - notably - find there an article documenting a
> pattern to achieve your goal.
>
> Regards
>
> --- Raoul

tacrawford@adelphia.net wrote:
> I have a table that contains project data (table 1). I also have
> a table that contains the starting/ending dates of each project (table
> 2). When I modify the required_weeks column, I need to re-calculate
> the starting/ending dates for all projects in that resource slot.
> What had been done was an AFTER UPDATE trigger on the project
> table (table 1) that called a stored proc to calculate the
> starting/ending dates for all projects in the same resource slot of the
> modified project.
> These starting/ending dates were queried from the project table
> (table 1), math was applied, then they were written to the dates table
> (table 2).
> The problem that exists is that apparently the data is not
> committed when the trigger runs, causing the stored proc to query the
> OLD data when it is trying to calculate the starting/ending dates.

That is the expected behaviour. Can you use the :NEW environment
variables to get what you need?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

I need to get all of the projects in the same resource slot,
including the modified one. The non-modified projects are not in the
:NEW variables.

DA Morgan wrote:
> tacrawford@adelphia.net wrote:
> > I have a table that contains project data (table 1). I also have
> > a table that contains the starting/ending dates of each project (table
> > 2). When I modify the required_weeks column, I need to re-calculate
> > the starting/ending dates for all projects in that resource slot.
> > What had been done was an AFTER UPDATE trigger on the project
> > table (table 1) that called a stored proc to calculate the
> > starting/ending dates for all projects in the same resource slot of the
> > modified project.
> > These starting/ending dates were queried from the project table
> > (table 1), math was applied, then they were written to the dates table
> > (table 2).
> > The problem that exists is that apparently the data is not
> > committed when the trigger runs, causing the stored proc to query the
> > OLD data when it is trying to calculate the starting/ending dates.
>
> That is the expected behaviour. Can you use the :NEW environment
> variables to get what you need?
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

tacrawford@adelphia.net wrote:
> I need to get all of the projects in the same resource slot,
> including the modified one. The non-modified projects are not in the
> :NEW variables.
>
> DA Morgan wrote:
>> tacrawford@adelphia.net wrote:
>>> I have a table that contains project data (table 1). I also have
>>> a table that contains the starting/ending dates of each project (table
>>> 2). When I modify the required_weeks column, I need to re-calculate
>>> the starting/ending dates for all projects in that resource slot.
>>> What had been done was an AFTER UPDATE trigger on the project
>>> table (table 1) that called a stored proc to calculate the
>>> starting/ending dates for all projects in the same resource slot of the
>>> modified project.
>>> These starting/ending dates were queried from the project table
>>> (table 1), math was applied, then they were written to the dates table
>>> (table 2).
>>> The problem that exists is that apparently the data is not
>>> committed when the trigger runs, causing the stored proc to query the
>>> OLD data when it is trying to calculate the starting/ending dates.
>> That is the expected behaviour. Can you use the :NEW environment
>> variables to get what you need?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

Then you need to do two things.

1. Stop top posting
2. Redesign your application

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

tacrawford@adelphia.net a écrit :

> I have a table that contains project data (table 1). I also have
> a table that contains the starting/ending dates of each project (table
> 2). When I modify the required_weeks column, I need to re-calculate
> the starting/ending dates for all projects in that resource slot.
> What had been done was an AFTER UPDATE trigger on the project
> table (table 1) that called a stored proc to calculate the
> starting/ending dates for all projects in the same resource slot of the
> modified project.
> These starting/ending dates were queried from the project table
> (table 1), math was applied, then they were written to the dates table
> (table 2).
> The problem that exists is that apparently the data is not
> committed when the trigger runs, causing the stored proc to query the
> OLD data when it is trying to calculate the starting/ending dates.
>

Indeed... *IF* I get you correctly, then you should drop the
autonomous
transaction pragma, and apply the standard pattern :

1. Initialize some package data in a before table trigger
2. Store in the package data the affected (resource slot/project/task
?)
with an after row trigger
3. Recompute the starting/ending date in an after table trigger,
for the data stored in the package.

This will work in the face of multiple row updates, rollbacks, etc...

You can find the pattern described in excruciating details in
the reference I gave earlier...

Take care

--- Raoul


> >
> > > The "pragma autonomous_transaction" keeps this from happening. The
> > > other problem is that while the user updates one record with a
> > > particular keyfield (name), I need to calculate all records that have
> > > the same value in the keyfield (name).
> > >
> > > Mark D Powell wrote:
> > > >
> > > > 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.
> > > >
> >
> > Dear,
> >
> > Note that the "mutating table " is actually protecting us from
> > nasty mistakes. You may be on a dangereous path if you are
> > trying to circumvent it with an autonomous transaction ...
> >
> > Tom Kyte discusses the issue at length in his
> > (highly recommended) books. If you don't have them,
> > you may want to browse the articles at
> > http://asktom.oracle.com - search for "mutating table"
> >
> > You will - notably - find there an article documenting a
> > pattern to achieve your goal.
> >
> > Regards
> >
> > --- Raoul