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