From oracle-l-bounce@freelists.org Thu Jul 28 17:30:29 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j6SMUTRL026476 for ; Thu, 28 Jul 2005 17:30:29 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j6SMUPIP026463 for ; Thu, 28 Jul 2005 17:30:25 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4259A1D8E8A; Thu, 28 Jul 2005 17:30:19 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02021-01; Thu, 28 Jul 2005 17:30:19 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AE7DB1D8E4B; Thu, 28 Jul 2005 17:30:18 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Trigger problem 8.1.7.4 - Solaris 8 Date: Thu, 28 Jul 2005 17:28:24 -0500 Message-ID: <3F02DCE8969E484482022C07D8CD6D9BE7335A@waumail02.wausau.int> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Trigger problem 8.1.7.4 - Solaris 8 Thread-Index: AcWTu16mQc234T83RJ2ibXJkl3bTBwAB7Ekg From: "Henslee, Jeff" To: "Henslee, Jeff" Cc: X-OriginalArrivalTime: 28 Jul 2005 22:28:33.0486 (UTC) FILETIME=[B040A6E0:01C593C3] X-archive-position: 23024 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jhenslee@wausauwindow.com Precedence: normal Reply-To: jhenslee@wausauwindow.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 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@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@dr280r','jhenslee@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@dr280r','jhenslee@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@dr280r','jhenslee@wausauwindow.com','trigger 3','Doing Insert'); end; END; commit; begin baan.sp_sendmail('waumail01','root@dr280r','jhenslee@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@wausauwindow.com -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l