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

An important difference here, is that if TBL_SETTINGS accidently get's more
records, your procedure will use the last TAX_PERCENTAGE it can find.

Now, TBL_SETTINGS sounds like a table that might get more rows? Or
do you create columns for each setting?

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