Firebird compute field value in trigger from query
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?
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.
精彩评论