help with triggers and mutating tables

From: heidi <heidistettner_at_gmail.com>
Date: 11 May 2007 09:05:29 -0700
Message-ID: <1178899529.493107.124980@e51g2000hsg.googlegroups.com>

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; Received on Fri May 11 2007 - 11:05:29 CDT