Trigger to update a column on update

Question

I have written this function and this trigger:

CREATE OR REPLACE FUNCTION test4() RETURNS TRIGGER AS $BODY$
BEGIN
     UPDATE "cf"
     SET vol=(area*alt);
RETURN null;
END;
$BODY$
LANGUAGE plpgsql

trigger

CREATE TRIGGER trig_upd
AFTER OR UPDATE ON "cf"
FOR EACH ROW
EXECUTE PROCEDURE test4();

I have tested my function and it's ok. So I created the trigger, but I can't insert any value in the table "cf" because the system crashes.

Answer 1

If you want to update the value of vol for each modified row, don't use update, just assign the value. For this to work you also need to define the trigger as a before trigger:

CREATE OR REPLACE FUNCTION test4() RETURNS TRIGGER 
AS 
$BODY$
BEGIN
   new.vol := new.area * new.alt;
   RETURN new;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER trig_upd
BEFORE UPDATE ON "cf" --<< note the BEFORE!
FOR EACH ROW
EXECUTE PROCEDURE test4()