Firebird compute field value in trigger from query

Question

I'm trying to make this trigger work:

CREATE trigger trig_tbl_art for tbl_art
active before update position 0
AS
begin
  IF (NEW.e  OLD.e) THEN
    IF (NEW.f = OLD.f) THEN
   NEW.f = SELECT (NEW.e)*CAST(row_b AS NUMERIC(9,2)) FROM table_a  WHERE row_a = 'someval';
end

The idea is to compute the value of tbl_art.f only when tbl_art.e changes.
NEW.f should be NEW.e * [the value returned from the query]

Any help please?

Answer 1

In PSQL you cannot assign the result of a query directly to a variable... this is since queries can return multiple columns, but you can use the into clause in your select statement:

SELECT (NEW.e)*CAST(row_b AS NUMERIC(9,2)) FROM table_a  WHERE row_a = 'someval'
 into NEW.f;

You are responsible to ensure the query returns only one row.