postgresqlpostgresql-triggers

How to prevent recursion in trigger on UPDATE operation?


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


Solution

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