Categories
Database Development

CASE WITH NEW. AND OLD [duplicate]

New problem. from : this task

NEED to do 4 state CASE with using OLD. and NEW. value using funcion as trigger
There is problem with update only with state WHEN OLD=NEW
This is main part of code

WHEN 'UPDATE' THEN
    CASE
        WHEN OLD.nr_w <> NEW.nr_w  AND NEW.nr_w <> '0' THEN 
            UPDATE TABLE SET "status" = 'ON<>N<>' WHERE TABLE."id" = NEW.id ;
        WHEN OLD.nr_w <> NEW.nr_w  AND NEW.nr_w = '0' THEN 
            UPDATE TABLE SET "status" = 'ON<>N=' WHERE TABLE."id" = NEW.id ;
        WHEN OLD.nr_w = NEW.nr_w  AND NEW.nr_w <> '0' THEN 
            UPDATE TABLE SET "status" = 'ON=N<>' WHERE TABLE."id" = NEW.id ;
        WHEN OLD.nr_w = NEW.nr_w  AND NEW.nr_w = '0' THEN 
            UPDATE TABLE SET "status" = 'ON=N=' WHERE TABLE."id" = NEW.id ;
        ELSE 
    END CASE;

Here is code of part funcion.

CREATE OR REPLACE FUNCTION public.zzz()
RETURNS trigger AS
$BODY$
DECLARE

BEGIN


CASE TG_OP
WHEN 'INSERT' THEN
     UPDATE Trpge SET ls = NEW.ls, ls_w = NEW.ls_w, ls_s = NEW.ls_s, dom = NEW.dom , doa = NEW.doa, sa = NEW.sa,  sm = NEW.sm, s_m_w = NEW.s_m_w, s_a_w = NEW.s_a_w, ss = NEW.ss 
     WHERE Trpge."id" = NEW.id ;


WHEN 'UPDATE' THEN

        CASE
            WHEN OLD.nr_w <> NEW.nr_w  AND NEW.nr_w <> '0' THEN 
                UPDATE rpge SET "status" = 'ON<>N<>' WHERE rpge."id" = NEW.id ;
            WHEN OLD.nr_w <> NEW.nr_w  AND NEW.nr_w = '0' THEN 
                UPDATE rpge SET "status" = 'ON<>N=' WHERE rpge."id" = NEW.id ;
            WHEN OLD.nr_w = NEW.nr_w  AND NEW.nr_w <> '0' THEN 
                UPDATE rpge SET "status" = 'ON=N<>' WHERE rpge."id" = NEW.id ;
            WHEN OLD.nr_w = NEW.nr_w  AND NEW.nr_w = '0' THEN 
                UPDATE rpge SET "status" = 'ON=N=' WHERE rpge."id" = NEW.id ;
            ELSE 
        END CASE;


WHEN 'DELETE' THEN

      UPDATE Trpge SET "s_a_w" = OLD.s_a_w - OLD.doa WHERE Trpge."n_w_xxx"=OLD.n_w_xxx;



END CASE;

RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql
enter code here

And the trigger

CREATE TRIGGER zzz_trigger
  BEFORE INSERT OR UPDATE
  ON public."rpge"
  FOR EACH ROW
  EXECUTE PROCEDURE public.zzz();

And error is that.

Nie można zatwierdzić zmian dla warstwy zzz
Błędy: ERROR: 1 attribute value change(s) not applied.
Błędy dostawcy:
      Błąd PostGIS podczas zmiany atrybutów: 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 "UPDATE public."zzz" SET "nr_w" = 'problem2' WHERE public."zzz"."id" = NEW.id"
    PL/pgSQL function raport_pge() line 127 at SQL statement
    SQL statement "UPDATE public."zzz" SET "nr_w" = 'problem2' WHERE public."zzz"."id" = NEW.id"
....

Blockquote

Leave a Reply

Your email address will not be published. Required fields are marked *