RE: Trigger problem 8.1.7.4 - Solaris 8

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

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

Other related posts: