Re: Trigger problem 8.1.7.4 - Solaris 8

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_at_wausauwindow.com> 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_at_freelists.org
> 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_at_dr280r','jhenslee_at_wausauwindow.com','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_at_dr280r','jhenslee_at_wausauwindow.com','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_at_dr280r','jhenslee_at_wausauwindow.com','trigger 3','Doing Insert'); end;
> END;
> commit;
> begin baan.sp_sendmail('waumail01','root_at_dr280r','jhenslee_at_wausauwindow.com','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_at_wausauwindow.com
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l