How to prevent recursion in trigger on UPDATE operation?

Question

I have simple trigger:

CREATE OR REPLACE FUNCTION nmck_decrease_percent_calc() RETURNS TRIGGER AS
$BODY$

 DECLARE
        s_price integer;
BEGIN
    SELECT "lotMaxPrice" into s_price FROM lots WHERE "purchaseNumber" = new."purchaseNumber";

    UPDATE contracts SET nmck_decrease_percent = (100 - round(( (new.sum::numeric/s_price::numeric) * 100), 4 ))
      WHERE "purchaseNumber" = new."purchaseNumber" AND "lotNumber" = new."lotNumber";

    RETURN new;
END;
$BODY$
language plpgsql;


CREATE OR REPLACE TRIGGER trig_percent_calc
     AFTER INSERT ON contracts
     FOR EACH ROW
     EXECUTE PROCEDURE nmck_decrease_percent_calc();

it's working, but I am getting recursion if I am changing: AFTER INSERT to AFTER UPDATE OR INSERT.

I understand that update it triggering new update etc.

But is there any way to get it work? I need recalculate value if it was UPDATEed

Answer 1

If purchaseNumber and lotNumber are the primary key of the contracts table, you don't need an UPDATE at all. You can just assign the value in a BEFORE trigger:

CREATE OR REPLACE FUNCTION nmck_decrease_percent_calc() 
  RETURNS TRIGGER 
AS
$BODY$
DECLARE
  s_price numeric;
BEGIN
  SELECT "lotMaxPrice" 
    into s_price 
  FROM lots 
  WHERE "purchaseNumber" = new."purchaseNumber";

  new.nmck_decrease_percent := (100 - round(( (new.sum::numeric/s_price) * 100), 4 ));

  RETURN new;
END;
$BODY$
language plpgsql;

For that to work you need a BEFORE row level trigger:

CREATE OR REPLACE TRIGGER trig_percent_calc
     BEFORE INSERT ON contracts
     FOR EACH ROW
     EXECUTE PROCEDURE nmck_decrease_percent_calc();

Answer 2

You could change your UPDATE to:

UPDATE contracts 
SET nmck_decrease_percent = (100 - round(( (new.sum::numeric/s_price::numeric) * 100), 4 ))
WHERE "purchaseNumber" = new."purchaseNumber" 
  AND "lotNumber" = new."lotNumber"
  and nmck_decrease_percent <> (100 - round(( (new.sum::numeric/s_price::numeric) * 100), 4 ));

This will only do an update when the value should change (the first time), and not when there is not change (the second time).

But it uncertain if this is the correct way to solve your problem, because of missing input data (as in Minimal, Reproducible Example)