At 01:08 AM 4/11/2003 +0000, you wrote:

> > Do you understand that, if the operation on Table B fails and you
>roll back
> > the transaction, then anything done by triggers also gets rolled back?
> >
>
>Yes, Iam aware of that. Well I guess I just have to take your word
>that triggers will always fire. I assume that you find no problem at
>all with my coding on my trigger. Is this ok ... set request = request
>+ new.requestqty ...
>
>Because some suggest me to get the original value first then put in on
>the variable, then make use of the variable ... but I think we can't
>declare and use variable in trigger ...

Yes, of course you can use a local variable in a trigger. You just can't
have input and output variables (arguments).

create trigger ai_tableb for table b
active after insert position 0
as
declare variable old_qty double precision; /* data type as appropriate */
begin
if (exists(select itemid from tableA
where itemid = new.itemid) then
begin
select requestqty from tableA
where itemid = new.itemid
into :old_qty;
if (old_qty is null) then
old_qty = 0;
update table A
set requestqty = :old_qty + new.request
where itemid = new.itemid;
end
end


heLen