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