On May 11, 12:05 pm, heidi <heidistett..._at_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.
Received on Fri May 11 2007 - 13:30:21 CDT
