Trigger problem 8.1.7.4 - Solaris 8
- From: "Henslee, Jeff" <jhenslee@xxxxxxxxxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 28 Jul 2005 16:29:01 -0500
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
Other related posts:
