开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜