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?

  • Well, vskidku - just add another field to the table, which the client application will not touch at all, and the trigger, making the "second" update will change it (say, doing +1 or a = 1-a) and the trigger that would check - if the field is changing - it means the update comes from the trigger and does not follow the update, so that it would not go in cycles. - Mike
  • By the way, why do you make requests to logtransfer in the form of strings and then do it, and also in a loop. One request and no execute would have looked prettier. - Mike
  • Can you give an example? I also found features such as: DISABLE TRIGGER; but when using them in the application, the database hangs - Oma
  • Example: make a field with the name A default 1 (so that null would not turn out). and write in the trigger literally update LogTransfer set A=1-A where "OldState_id"=3 AND "NewState_id"=5 and OLD.A=NEW.A - Mike
  • I'm talking about the cycle =) - Oma

0