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

What is the primary key of the `contracts` table? – Jul 31 '22 at 08:57 — , Jul 31 '22 at 08:57

score 1 · Accepted Answer · answered Jul 31 '22 at 09:01

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

answered Jul 31 '22 at 09:01

Thanks! Why assigning value with := ? – Dmitry Bubnenkov Jul 31 '22 at 09:09
Because that's how [assignment](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT) is done in PL/pgSQL – Jul 31 '22 at 09:17
The link also shows that `=` is correct. This has been the case since Postgres 9.4. – Adrian Klaver Jul 31 '22 at 14:50
Postgres attempts maintaining some comparability with Oracle. (Why ?) .The option to use `:=` or `=` for assignment is one. Assignment in Oracle is `:=`, – Belayer Jul 31 '22 at 18:06
@Belayer: `:=` is used for assignment in several languages (e.g. Pascal, Algol and Ada - from which PL/SQL and PL/pgSQL where inspired). In [older Postgres versions](https://www.postgresql.org/docs/9.3/plpgsql-statements.html) that was the only officially documented assignment operator. – Jul 31 '22 at 18:38

score 0 · Answer 2 · answered Jul 31 '22 at 08:34

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)

How to prevent recursion in trigger on UPDATE operation?

2 Answers2