|
|
 |
 |
 |
 |
help with triggers and mutating tables
Hello, We're converting from Sybase to Oracle and I'm running into problems
converting the triggers. We have a lot of tables that update other
rows in the same table based on certain criteria. Although I can use
the basic mutating table trick to compile a trigger and get it to work
for a single column update, it doesn't work if I'm updating more than
one column. In this example, for instance, if I update only supe,
status or bid_days the trigger will correctly update the relevant row
but if I try to update more than one only the first one referenced in
the trigger updates. What am I missing?
Thanks,
Heidi
create or replace trigger seqpts_au_trigger
after update
on seqpoints
declare
sum_bid_days number;
begin
if ( seqpoints_pkg.fire )
then
seqpoints_pkg.fire := FALSE;
for seqPtCnt in 1 .. seqpoints_pkg.inserted.count
loop
if ( nvl(seqpoints_pkg.deleted( seqPtCnt ).supe,' ') <>
nvl(seqpoints_pkg.inserted( seqPtCnt ).supe,' ') and
seqpoints_pkg.inserted( seqPtCnt ).dept = 'effects' )
then
/* sim supe is same as lighting supe */
update seqpoints set supe =
seqpoints_pkg.inserted( seqPtCnt ).supe
where
seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
and seqpoints.dept = 'sim';
end if;
if ( seqpoints_pkg.deleted( seqPtCnt ).status <>
seqpoints_pkg.inserted( seqPtCnt ).status )
then
if (seqpoints_pkg.inserted( seqPtCnt ).dept='effects' and
seqpoints_pkg.inserted( seqPtCnt ).status='done')
then
update seqpoints set status='into'
where
seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
and seqpoints.dept='sim'
and seqpoints.status='pre-prod';
end if;
end if;
if ( seqpoints_pkg.deleted( seqPtCnt ).bid_days <>
seqpoints_pkg.inserted( seqPtCnt ).bid_days )
then
if (seqpoints_pkg.inserted( seqPtCnt ).dept='effects')
then
select sum(nvl(bid_days,0))
into sum_bid_days
from seqpoints
where
seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
and seqpoints.dept='effects'
and nvl(seqpoints.category,' ') != ' ';
update seqpoints set bid_days=sum_bid_days
where
seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
and seqpoints.dept='effects'
and nvl(seqpoints.category,' ')=' ';
end if;
end if;
end loop;
seqpoints_pkg.fire := TRUE;
end if;
end;
On May 11, 12:05 pm, heidi <heidistett @gmail.com> wrote:
> Hello, > We're converting from Sybase to Oracle and I'm running into problems
> converting the triggers. We have a lot of tables that update other
> rows in the same table based on certain criteria. Although I can use
> the basic mutating table trick to compile a trigger and get it to work
> for a single column update, it doesn't work if I'm updating more than
> one column. In this example, for instance, if I update only supe,
> status or bid_days the trigger will correctly update the relevant row
> but if I try to update more than one only the first one referenced in
> the trigger updates. What am I missing?
> Thanks,
> Heidi
> create or replace trigger seqpts_au_trigger
> after update
> on seqpoints
> declare
> sum_bid_days number;
> begin
> if ( seqpoints_pkg.fire )
> then
> seqpoints_pkg.fire := FALSE;
> for seqPtCnt in 1 .. seqpoints_pkg.inserted.count
> loop
> if ( nvl(seqpoints_pkg.deleted( seqPtCnt ).supe,' ') <>
> nvl(seqpoints_pkg.inserted( seqPtCnt ).supe,' ') and
> seqpoints_pkg.inserted( seqPtCnt ).dept = 'effects' )
> then
> /* sim supe is same as lighting supe */
> update seqpoints set supe =
> seqpoints_pkg.inserted( seqPtCnt ).supe
> where
> seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
> and seqpoints.dept = 'sim';
> end if;
> if ( seqpoints_pkg.deleted( seqPtCnt ).status <>
> seqpoints_pkg.inserted( seqPtCnt ).status )
> then
> if (seqpoints_pkg.inserted( seqPtCnt ).dept='effects' and
> seqpoints_pkg.inserted( seqPtCnt ).status='done')
> then
> update seqpoints set status='into'
> where
> seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
> and seqpoints.dept='sim'
> and seqpoints.status='pre-prod';
> end if;
> end if;
> if ( seqpoints_pkg.deleted( seqPtCnt ).bid_days <>
> seqpoints_pkg.inserted( seqPtCnt ).bid_days )
> then
> if (seqpoints_pkg.inserted( seqPtCnt ).dept='effects')
> then
> select sum(nvl(bid_days,0))
> into sum_bid_days
> from seqpoints
> where
> seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
> and seqpoints.dept='effects'
> and nvl(seqpoints.category,' ') != ' ';
> update seqpoints set bid_days=sum_bid_days
> where
> seqpoints.seq_id=seqpoints_pkg.inserted( seqPtCnt ).seq_id
> and seqpoints.dept='effects'
> and nvl(seqpoints.category,' ')=' ';
> end if;
> end if;
> end loop;
> seqpoints_pkg.fire := TRUE;
> end if;
> end;
This is a statement trigger and you seem to be looking for row level trigger. -----------------------------------------------Reply-----------------------------------------------
I use the row level trigger to save the information for each row, but this is the statement trigger that is run after the update so I can change the table without getting a mutating table error. The trigger is surrounded by a loop, one for each row updated. -----------------------------------------------Reply-----------------------------------------------
heidi wrote:
> I use the row level trigger to save the information for each row, but
> this is the statement trigger that is run after the update so I can
> change the table without getting a mutating table error. The trigger
> is surrounded by a loop, one for each row updated.
Nothing written above makes any sense. You can not "surround" a trigger with a loop. Please either post the code or write an explanation that is decipherable.
--
Daniel A. Morgan
University of Washington
damor@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
-----------------------------------------------Reply-----------------------------------------------
The code for the trigger is in my first post. I guess I should have said the relevant update code is surrounded by a loop which processes each row. Here is the loop part, as copied from the original post. create or replace trigger seqpts_au_trigger
after update
on seqpoints
declare
sum_bid_days number;
begin
if ( seqpoints_pkg.fire )
then
seqpoints_pkg.fire := FALSE;
for seqPtCnt in 1 .. seqpoints_pkg.inserted.count
loop
processing...
end loop;
seqpoints_pkg.fire := TRUE;
end if;
This is part of a package, which includes a before update trigger to
initialize the seqpoints_pkg.inserted and seqpoints_pkd.deleted arrays
and a before update on each row trigger which saves the :new and :old
information into the arrays.
-----------------------------------------------Reply-----------------------------------------------
You can create update triggers for each column separately. This will give you more flexibility on debugging the problem. I think the syntax is like create or replace trigger trigger_name after update of column_name on
table_name ....
Balaji
-----------------------------------------------Reply-----------------------------------------------
On May 15, 2:27 pm, "balaji.call @gmail.com" <balaji.call @gmail.com> wrote: > You can create update triggers for each column separately. This will > give you more flexibility on debugging the problem. I think the syntax > is like > create or replace trigger trigger_name after update of column_name on
> table_name ....
> Balaji
This is dangerous advice. Triggers are parsed always, and Oracle doesn't guarantee the execution order for different triggers on identical events. --
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
balaji.call @gmail.com wrote: > You can create update triggers for each column separately. This will > give you more flexibility on debugging the problem. I think the syntax > is like > create or replace trigger trigger_name after update of column_name on
> table_name ....
> Balaji
I would strongly recommend not taking this advice. If you want to debug something ... use proper debugging techniques. To use a kludge such as triggers on each column is begging for a nightmare. -- Daniel A. Morgan University of Washington damor @x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply-----------------------------------------------
The problem is not in any individual update in the trigger. I can update the relevant columns by themselves and the trigger fires fine. The problem is when I try updating both columns in one statement. Only the first update in the trigger will fire. -----------------------------------------------Reply-----------------------------------------------
On 15 May 2007 09:48:28 -0700, heidi <heidistett @gmail.com> wrote: >The problem is not in any individual update in the trigger. I can
>update the relevant columns by themselves and the trigger fires fine.
>The problem is when I try updating both columns in one statement.
>Only the first update in the trigger will fire.
That may be, but your unformatted code is completely unreadable. Do you assume anyone else is going to format it for you? --
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 heidi schreef:
> The problem is not in any individual update in the trigger. I can
> update the relevant columns by themselves and the trigger fires fine.
> The problem is when I try updating both columns in one statement.
> Only the first update in the trigger will fire.
Do you expect a *row* trigger to fire two times because you changed the values of two columns of the same *row*? Think again. It'll fire once - as designed.
It's up to you to check which columns are changed, using
the :old and :new column values, or create a trigger
for each and every column - which is not a good
idea, as indicated and explained by Sybrand and Daniel.
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGSfNeLw8L4IAs830RAqHqAKCJipmOUvPu7dciGEde9PDgH194vQCeN2Iw
64ri/OnpYJnamhUpwMM1QCQ=
=PLFX
-----END PGP SIGNATURE-----
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc