CODEWITHSUNDEEP
postgresqldatabase-trigger
Василий Пупкин
Василий Пупкин

Reputation: 13

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?

The code:

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();

Upvotes: 0

Views: 52

Answers (1)

user330315
user330315

Reputation:

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();

Upvotes: 2

Related Questions