There is a trigger:
CREATE OR REPLACE FUNCTION LogTransfer_trigger_update_func() RETURNS trigger AS $TRIGGER$ DECLARE rec ComprositiontTransfer; sqlUpdate varchar; BEGIN if OLD."OldState_id" = 3 and OLD."NewState_id" = 5 then /* если этот код то еще и обновить лог транспортировки со статусом 3 надо */ /* UPDATE "LogTransfer" SET "Сomposition"=NEW."Сomposition" WHERE "Transfer_ID"=OLD."Transfer_ID" AND "OldState_id"=1 AND "NewState_id"=3; */ sqlUpdate = 'UPDATE "' || OLD."SkladPostav" || '" SET "Count" = ("Count" - $2), "Changed" = true WHERE ("Name" = $1)'; FOR rec.Name, rec.Count IN SELECT u.Name, u.Count FROM unnest(OLD."Сomposition") u GROUP BY u.Name, u.Count LOOP execute sqlUpdate using rec.Name, rec.Count; END LOOP; /* Добавляем по новой */ sqlUpdate = 'UPDATE "' || OLD."SkladPostav" || '" SET "Count" = ("Count" + $2), "Changed" = true WHERE ("Name" = $1)'; FOR rec.Name, rec.Count IN SELECT u.Name, u.Count FROM unnest(NEW."Сomposition") u GROUP BY u.Name, u.Count LOOP execute sqlUpdate using rec.Name, rec.Count; END LOOP; elsif OLD."OldState_id" = 1 and OLD."NewState_id" = 3 then /* если этот код то еще и обновить лог транспортировки со статусом 3 надо */ /*UPDATE "LogTransfer" SET "Сomposition"=NEW."Сomposition" WHERE "Transfer_ID"=OLD."Transfer_ID" AND "OldState_id"=3 AND "NewState_id"=5;*/ sqlUpdate = 'UPDATE "' || OLD."SkladPostav" || '" SET "Count" = ("Count" - $2), "Changed" = true WHERE ("Name" = $1)'; FOR rec.Name, rec.Count IN SELECT u.Name, u.Count FROM unnest(OLD."Сomposition") u GROUP BY u.Name, u.Count LOOP execute sqlUpdate using rec.Name, rec.Count; END LOOP; /* Добавляем по новой */ sqlUpdate = 'UPDATE "' || OLD."SkladPostav" || '" SET "Count" = ("Count" + $2), "Changed" = true WHERE ("Name" = $1)'; FOR rec.Name, rec.Count IN SELECT u.Name, u.Count FROM unnest(NEW."Сomposition") u GROUP BY u.Name, u.Count LOOP execute sqlUpdate using rec.Name, rec.Count; END LOOP; end if; return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER LogTransfer_Update_trigger AFTER UPDATE ON "LogTransfer" FOR EACH ROW EXECUTE PROCEDURE LogTransfer_trigger_update_func(); So I need to synchronously update two entries in the table. Those. if it is updated with the status 3-> 5, then it is also necessary to update 1-> 3 and vice versa. If I open the update string in the trigger. He writes that: the maximum depth stack is full. If I call the update twice in the application logic, then it does everything to me in double size, because it works twice. Question: Is it possible to call UPDATE bypassing a trigger in an application, or how can I correctly change this trigger?
update LogTransfer set A=1-A where "OldState_id"=3 AND "NewState_id"=5 and OLD.A=NEW.A- Mike