Good day. In the database created a table:

CREATE TABLE public."Tabel" ( "UserHash" bytea NOT NULL, "Date" timestamp without time zone NOT NULL, day numeric[], "TotalTime" numeric )

I register the trigger:

 CREATE OR REPLACE FUNCTION tabel_trigger_func() RETURNS trigger AS $TRIGGER$ BEGIN NEW."TotalTime" = (SELECT sum(f) from "Tabel",unnest("Tabel".day) f WHERE ("UserHash" = NEW."UserHash" ) and ("Date" = NEW."Date")); return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER tabel_trigger AFTER UPDATE ON "Tabel" FOR EACH ROW EXECUTE PROCEDURE tabel_trigger_func();

When updating the table field, or rather, the "day" array, the "TotalTime" field should be automatically updated (the sum of the "day" array should be counted). But when updating, nothing happens.

Insert code:

 INSERT INTO public."Tabel"( "UserHash", "Date", day, "TotalTime") VALUES ('4', '2016.11.01 00:00:00', '{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}', '0');

Update code:

 UPDATE "Tabel" SET day[1] = '20' WHERE "UserHash"= '4';

    1 answer 1

    The change of NEW in the AFTER UPDATE trigger, of course, will not be saved. The same trigger after the update is called. Offer after the trigger to save the string again? And once again it is necessary to cause the trigger? You probably wanted to do BEFORE UPDATE .

    If UserHash + Date unique index, then it is not necessary to read the label, you can simplify the trigger:

     CREATE OR REPLACE FUNCTION tabel_trigger_func() RETURNS trigger AS $TRIGGER$ BEGIN NEW."TotalTime" = (SELECT sum(f) from unnest(NEW.day) f); return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER tabel_trigger BEFORE UPDATE ON "Tabel" FOR EACH ROW EXECUTE PROCEDURE tabel_trigger_func();