IF ( ... )


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> 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
>
>
>