Re: Trigger problem 8.1.7.4 - Solaris 8

  • From: Tim Hall <timhall1@xxxxxxxxx>
  • To: Oracle-L@xxxxxxxxxxxxx
  • Date: Thu, 28 Jul 2005 15:47:06 -0700

While you're in there, you'll probably want to add a WHEN clause to
the trigger definition... something like this (I don't know the
datatype of T$PROC, so adjust this as appropriate):

WHEN (NVL(OLD.T$PROC,CHR(0)) != NVL(NEW.T$PROC,CHR(0)))

If you just use "ON UPDATE OF T$PROC" the trigger will fire anytime
T$PROC is included in the SET clause of the UPDATE statement, even
when the old and new values happen to be the same.  (I'm assuming you
wouldn't want all of this logic to fire in that case?).  This can
happen surprisingly often depending on how the application assembles
its UPDATE statements (it happens a lot in Oracle Forms, for
example)...

Hope this helps,
Tim


On 7/28/05, Henslee, Jeff <jhenslee@xxxxxxxxxxxxxxxx> wrote:
> FIXED!!!
> ======
> 
> select  count(*) into myrec from  baan.ttiitm898220 where
> baan.ttiitm898220.t$item = new_item ;
> If myrec>0 then
>        -- we have records so update
> Else
> -- we don't so insert
> End if;
> 
> I was confusing cursor processing into my trigger.
> 
> Special Thank You to Jim Kennedy for pointing that out to me!
> Kudo's and a "virtual" beverage all around!
> 
> 
> -----Original Message-----
> From: Henslee, Jeff
> Sent: Thursday, July 28, 2005 4:29 PM
> To: Oracle-L@xxxxxxxxxxxxx
> Subject: Trigger problem 8.1.7.4 - Solaris 8
> 
> 
> Apologies for the simple question - but I am not a PLSQL expert - nor have I 
> written to many of these in the past.  I have a trigger whereby when the a 
> records gets posted (flag is flipped), I want to update a second table with 
> the quantity from the record updated.
> I have built in some e-mails for debugging purposes - I get every one of them 
> EXCEPT the one inside of the exception clause where the insert/update occur.  
> I know you can't do commits within a procedure and creating an autonomous 
> transaction allows you to work through that.  My problem is the entire 
> trigger is fired and every thing is executed EXCEPT for the update or insert 
> of records into the second table - I'm totally brain dead and stumped.  Any 
> suggestions?  Please help!
> ======
> CREATE OR REPLACE TRIGGER "BAAN"."TRG_TTDILC401220" AFTER
> UPDATE OF "T$PROC" ON "BAAN"."TTDILC401220" FOR EACH ROW DECLARE
>  PRAGMA AUTONOMOUS_TRANSACTION;
>  new_item    varchar2(16);
>  new_qsts    number(12,4);
>  m_message   varchar2(500);
> BEGIN
> --if :new.t$cwar = '401' then
>        -- I get this e-mail
>        begin 
> baan.sp_sendmail('waumail01','root@dr280r','jhenslee@xxxxxxxxxxxxxxxx','trigger
>  1','Start'); end;
>        new_item  :=:new.t$item;
>        new_qsts  :=:new.t$qsts;
>        m_message := 'Item:' || new_item || ' Qty: ' || new_qsts;
>        -- I get this e-mail too
>        begin 
> baan.sp_sendmail('waumail01','root@dr280r','jhenslee@xxxxxxxxxxxxxxxx','trigger
>  2',m_message); end;
>        begin
>           update baan.ttiitm898220
>                        set baan.ttiitm898220.t$pqty = 
> (baan.ttiitm898220.t$pqty + new_qsts)
>           where
>                        baan.ttiitm898220.t$item = new_item;
>                        EXCEPTION WHEN NO_DATA_FOUND then
>                begin
>                            insert into baan.ttiitm898220
>                            values(  new_item,
>                                           new_qsts,
>                                           0,
>                                           0);
>                  end;
>           -- I do NOT get this one at all
>           begin 
> baan.sp_sendmail('waumail01','root@dr280r','jhenslee@xxxxxxxxxxxxxxxx','trigger
>  3','Doing Insert'); end;
>        END;
>        commit;
>        begin 
> baan.sp_sendmail('waumail01','root@dr280r','jhenslee@xxxxxxxxxxxxxxxx','trigger','End-o-trigger');
>  end;
>        --Lastly, I get this one,  what am I doing wrong with my SQL?
> --end if;
> 
> END;
> 
> Can anyone help me?  thanks a ton!
> 
> Jeffrey C. Henslee (Chico)
> Wausau Window and Wall Systems
> mailto:jhenslee@xxxxxxxxxxxxxxxx
> 
> --
> //www.freelists.org/webpage/oracle-l
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l

Other related posts: