如何避免执行更新的更新触发器中的递归? [英] How to avoid recursion in an update trigger that does an update?

问题描述

我需要使用触发器更新记录中商品的增值税总和.但是触发器将触发以更新记录，并陷入递归状态.如何避免这种情况?

I need to update the vat_total_sum of goods in a record using a trigger. But the trigger fires to update the record and falls into recursion. How can I avoid this?

代码:

CREATE OR REPLACE FUNCTION vat_total_sum() RETURNS TRIGGER AS $$
BEGIN

   UPDATE goods SET vat_total_sum = NEW.vat_sum / 100 * NEW.vat_percent 
      WHERE goods_id = new.goods_id;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER vat_total_sum AFTER INSERT OR UPDATE ON goods
    FOR EACH ROW EXECUTE PROCEDURE vat_total_sum();

推荐答案

不要使用UPDATE，请使用 BEFORE 触发器并分配新值:

Don't use UPDATE, use a BEFORE triger and assign the new value:

CREATE OR REPLACE FUNCTION vat_total_sum() RETURNS TRIGGER AS $$
BEGIN
  new.vat_total_sum := NEW.vat_sum / 100 * NEW.vat_percent; 
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER vat_total_sum 
   BEFORE INSERT OR UPDATE ON goods
   FOR EACH ROW EXECUTE PROCEDURE vat_total_sum();

这篇关于如何避免执行更新的更新触发器中的递归?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！