4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Giocor
  • 43
  • 1
  • 4
  • please give some details on HOW it crashes – Vao Tsun Mar 10 '17 at 11:08
  • `AFTER OR UPDATE`? Maybe you want `AFTER INSERT OR UPDATE` instead. -- You should use the [`NEW` record](https://www.postgresql.org/docs/current/static/plpgsql-trigger.html), instead of `UPDATE`ing the whole table. (in its current form your trigger calls an infinite loop and because of `RETURN null` it would revoke an actual insert when the table is empty -- and there is no infinite loop) – pozs Mar 10 '17 at 11:09
  • Why are you updating **all** rows in that table each time a single row is updated? –  Mar 10 '17 at 11:15
  • This question desperately needs some explanation what the trigger is supposed to do *exactly*. Also, your `create trigger` is syntactical nonsense that Postgres would not accept. `AFTER OR UPDATE` .. not possible. – Erwin Brandstetter Mar 10 '17 at 13:50
  • I think the crash you’re talking about is a result of an infinite loop. The way you’ve written it, the trigger updates the table, which triggers the trigger again. @a_horse_with_no_name’s solution avoids that. – Manngo Sep 05 '21 at 05:58

1 Answers1

13

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()
  • I have also commented on the original question regarding what OP describes as crashing. Out of curiosity, why is the trigger _before_ and not after? Intuitively, I should have thought that you’re making additional changes after the others have been made. – Manngo Sep 05 '21 at 06:00
  • @Manngo: because you can't change the "new" record in an `after` trigger –  Sep 06 '21 at 05:08