--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 05:17 PM 11/06/2008, you wrote:
> >>
> >> CREATE TRIGGER ITEM_TOTAL_EXCL FOR TBL_JOB_CARD_INVENTORY
> >> ACTIVE BEFORE INSERT OR UPDATE POSITION 1
> >> AS
> >> BEGIN
> >>
> >> NEW.Total_Incl = (NEW.Selling_Price -
> >> NEW.Selling_Price*NEW.Item_Discount/100)*NEW.Quantity*(select
> >tax_percentage
> >> from tbl_settings);
> >>
> >> END;
> >
> >> Anderson Farias
> >>
> >
> >Thanks for your response. I get the following error when running your
> >code "Subselect illegal in this context".
>
> ..as indeed it is. For this to be a valid expression, the subselect

must be certain to return a scalar. This syntax allows the
possibility of breaking that rule.

>
> Assuming TBL_SETTINGS is a table with exactly one row (not zero

rows, not two or more rows) then fetch the value TAX_PERCENTAGE into a
variable, as follows:

>
> CREATE TRIGGER ITEM_TOTAL_EXCL FOR TBL_JOB_CARD_INVENTORY
> ACTIVE BEFORE INSERT OR UPDATE POSITION 1
> AS
> declare taxpc float=1;
> BEGIN
> select tax_percentage from tbl_settings into :taxpc;
> NEW.Total_Incl = NEW.Selling_Price - (
> NEW.Selling_Price*NEW.Item_Discount/100)*NEW.Quantity*taxpc);
> END
>
> Note, if TBL_SETTINGS is not a one-row table then you will get a

different error: "Multiple rows in singleton select"

>
> That means you need a key to link this row in TBL_JOB_CARD_INVENTORY

with a unique row in TBL_SETTINGS -- if this is the case, then a
*correlated* subquery will be legal.

>
> ./heLen
>

Thanks. Yes that's the same conclusion I came to. The table only has
one record. I have used the following code which also works

BEGIN
FOR SELECT TAX_PERCENTAGE
FROM TBL_SETTINGS
INTO :TAX
DO
BEGIN
NEW.Total_Incl_VAT = (NEW.Selling_Price -
NEW.Selling_Price*NEW.Item_Discount/100)*NEW.Quantity*TAX/100;
END
END

Many thanks for your help everyone