CODEWITHSUNDEEP
postgresqltriggers
ElíasMarNev
ElíasMarNev

Reputation: 124

Postgresql, triggers error when check the insertion

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..

My table "Historial"

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?

Upvotes: 1

Views: 122

Answers (2)

AhmadReza Payan
AhmadReza Payan

Reputation: 2271

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

Upvotes: 1

klin
klin

Reputation: 121604

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

Upvotes: 2

Related Questions