I'ev made 3 correction to the syntax, use into, bracket test in if and use
variable in both parts of if test

you are hopefully aware that this is a recipe for disaster in a multiuser
system

CREATE TRIGGER TBL_MOVEMENT_BU FOR TBL_MOVEMENT
ACTIVE BEFORE UPDATE
POSITION 0
AS
DECLARE VARIABLE CURRENT_QOH DECIMAL(12, 2);
BEGIN
SELECT TBL_LOC_INV.QUANTITY_ON_HAND
FROM
TBL_LOC_INV
WHERE
TBL_LOC_INV.LOCATION_ID = OLD.FROM_LOC AND
TBL_LOC_INV.INVENTORY_ID = OLD.INVENTORY_ID
into :CURRENT_QOH;
IF ( CURRENT_QOH < OLD.QUANTITY OR CURRENT_QOH IS NULL ) THEN
BEGIN
EXCEPTION INSUFFICIENT_INV;
EXIT;
ELSE
UPDATE TBL_LOC_INV
SET
QUANTITY_ON_HAND = QUANTITY_ON_HAND - OLD.QUANTITY
WHERE
TBL_LOC_INV.LOCATION_ID = OLD.FROM_LOC AND
TBL_LOC_INV.INVENTORY_ID = OLD.INVENTORY_ID;
END
;

2008/8/26 dinol.softedge <dinol@...>

> Hi
>
> If have the following statement to check whether enough stock exists
> in tbl_loc_inv and if so deduct the current amount of stock from it:
>
> CREATE TRIGGER TBL_MOVEMENT_BU FOR TBL_MOVEMENT
> ACTIVE BEFORE UPDATE
> POSITION 0
> AS
> DECLARE VARIABLE CURRENT_QOH DECIMAL(12, 2);
> BEGIN
> CURRENT_QOH =(SELECT TBL_LOC_INV.QUANTITY_ON_HAND
> FROM
> TBL_LOC_INV
> WHERE
> TBL_LOC_INV.LOCATION_ID = OLD.FROM_LOC AND
> TBL_LOC_INV.INVENTORY_ID = OLD.INVENTORY_ID);
> IF CURRENT_QOH < OLD.QUANTITY OR IS NULL THEN
> BEGIN
> EXCEPTION INSUFFICIENT_INV;
> EXIT;
> ELSE
> UPDATE TBL_LOC_INV
> SET
> QUANTITY_ON_HAND = QUANTITY_ON_HAND - OLD.QUANTITY
> WHERE
> TBL_LOC_INV.LOCATION_ID = OLD.FROM_LOC AND
> TBL_LOC_INV.INVENTORY_ID = OLD.INVENTORY_ID;
> END
> ;
>
> Problem is I get the following error:
>
> Dynamic SQL Error.
> SQL error code = -104.
> Token unknown - line 13, char 6.
> CURRENT_QOH.
>
> I'm not sure why it doesn't recognise it as it is declared as a
> variable. Also, is this the most efficient way to do this trigger.
> Many thanks in advance
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

[Non-text portions of this message have been removed]