Postgresql, triggers error when check the insertion

Question

Im trying to use a trigger to check when I insert a data on my table "historial", I need to check before to insert, if some row of the table "Historial" has the same IDU as the data that I'm insert. I have use this trigger and this function, but I can't make it work..

That its my trigger:

CREATE TRIGGER VerificarInsercion
BEFORE INSERT ON public."Historial"
FOR EACH ROW 
EXECUTE PROCEDURE VerificarHistorial();

(I've tried removing "FOR EACH ROW", since what I want is to run only once and not for each row)

And finally, my function verificarhistorial():

BEGIN
  IF (SELECT Count (*) FROM public."Historial" WHERE estado = 1 AND "IDU" = NEW."IDU") < 1 THEN 
      INSERT INTO public."Historial" (usuario, vehiculo, mercancia, "combustibleInicial", ruta, "horaSalida", estado, "IDU", fecha)
      VALUES (NEW.usuario, NEW.vehiculo, NEW.mercancia, NEW."combustibleInicial", NEW.ruta, NEW."horaSalida", NEW.estado, NEW."IDU", NEW.fecha);
  END IF;
  RETURN null;
END;

When I insert some data in "Historial":

INSERT INTO public."Historial" (usuario, vehiculo, mercancia, "combustibleInicial", ruta, "horaSalida", estado, "IDU", fecha) VALUES ('David', '3424SDA', 200, 50, 'Cáceres-Sevilla', 'dom, 3 sep, 05:20 PM', 1, 50, '2017-09-03T15:21:07.442Z')

I get this error:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "SELECT (SELECT Count (*) FROM public."Historial" WHERE estado = 1 AND "IDU" = NEW."IDU") < 1"
PL/pgSQL function verificarhistorial() line 2 at IF
SQL statement "INSERT INTO public."Historial" (usuario, vehiculo, mercancia, "combustibleInicial", ruta, "horaSalida", estado, "IDU", fecha)
      VALUES (NEW.usuario, NEW.vehiculo, NEW.mercancia, NEW."combustibleInicial", NEW.ruta, NEW."horaSalida", NEW.estado, NEW."IDU", NEW.fecha)"
PL/pgSQL function verificarhistorial() line 3 at SQL statement

I have checked other similar responses, without result.

Some idea to make a function that check if any row have the same IDU that the insert data?

You don't need a trigger to this, just create an UNIQUE INDEX on that field. You can't do a select on the table you are creating the trigger. — Jorge Campos, Sep 03 '17 at 16:36
yes but, IDU can be duplicated on the table, only one row can have "estado" == 1 and the same IDU, but others row can have the same IDU but "estado == 0" — ElíasMarNev, Sep 03 '17 at 16:40
After to insert? I tring to prevent insert 2 o more rows with the same IDU and "estado" == 1, should be Before, shouldn't it? — ElíasMarNev, Sep 03 '17 at 16:44

score 2 · Accepted Answer · answered Sep 03 '17 at 16:46

The trigger function is called each time you insert a new row into the table. If you are trying to insert a row inside the function, the function is called again, and again and so on. This way you achieve stack overflow.

Do not try to insert a row in a trigger function called on insert...

BEGIN
    IF EXISTS (SELECT 1 FROM "Historial" WHERE estado = 1 AND "IDU" = NEW."IDU") THEN 
        RETURN null;
    END IF;
    RETURN new;
END; $$;

In fact you do not need a trigger if you create a partial unique index:

create unique index on "Historial" ("IDU") where estado = 1

AhmadReza Payan · Answer 2 · 2019-05-13T17:52:12.037

1

You shouldn't insert a new row in your trigger which is caused infinite recursion. I think it could be done by creating an Unique Index like this:

set search_path = public;
create unique index on "Historial" ("IDU") where estado = 1;

The same question but for the Update Command already answered in the Stackoverflow, See the below link:

Update a table with a trigger after update

edited May 13 '19 at 17:52

answered Sep 03 '17 at 16:59

AhmadReza Payan

2,171
1
23
33

Postgresql, triggers error when check the insertion

2 Answers2