RE: Trigger problem 8.1.7.4 - Solaris 8

From: Henslee, Jeff <jhenslee_at_wausauwindow.com>
Date: Thu, 28 Jul 2005 17:28:24 -0500
Message-ID: <3F02DCE8969E484482022C07D8CD6D9BE7335A@waumail02.wausau.int>

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 Received on Thu Jul 28 2005 - 17:30:29 CDT