Thanks Martin.

But I wonder can we declare and use a variable in trigger? I think
decalring and using a variable is only available in SP.

Anybody have any idea on this one? could this be a bug?


--- In firebird-support@yahoogroups.com, "Martin Dew"
<martin.dew@a...> wrote:

> Firstly you would need to get the original quantity from table a)....
>
> CREATE TRIGGER TABLEB_1 FOR TABLE_B BEFORE UPDATE POSITION 0 AS
> declare variable old_amount integer;
> BEGIN
> Select requestqty from table_a where itemid = new.itemid into
> :old_amount;
>
> Update table_a Set requestqty = old_amount + new.request where itemid =
> new.item_id;
>
> END
>
> Martin Dew
>
>
>
> -----Original Message-----
> From: james_027 [mailto:james_027@y...]
> Sent: 03 November 2003 17:24
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Trigger is not 100%
>
> My setup is this I have table A and table B. I have a afterinsert
> trigger on table B that could update a record on table A. For example
>
> table A fields
> --------------
> itemid
> itemname
> requestqty
>
> table B fields
> --------------
> itemid
> requestdate
> request
>
> my trigger on afterinsert on table B is this ...
>
> update table A
> set requestqty = requestqty + new.request where itemid = new.itemid
>
> I have encountered that there are times that after a record was inserted
> on the table B... the record on the table A wasn't not updated.
>
> For me this is very annoying it causes my data to be not synchronize.
> Is there a bug? Does any one experience this one where sometimes it
> seems that the trigger wasn't triggered after all.
>
> Please advise.
>
> Thanks.
>
>
>
> james
>
>
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/