Hi Nick

Thanks for your response. Why do you say the statement is a recipe for
disaster in a multiuser environment?

--- In firebird-support@yahoogroups.com, "Nick Upson" <nick.upson@...>
wrote:

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