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