Search code examples
postgresqldatabase-trigger

Trigger to update table column when new row is insert in postgresql?

I want to write trigger to update table when new row is insert. I am using updatea Query like

UPDATE table SET
  geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
Solution

  • You need to use a BEFORE trigger and then assign the new value:

    CREATE OR REPLACE FUNCTION function_update()
  RETURNS trigger AS
$BODY$
BEGIN
    new.geom := ST_SetSRID(ST_MakePoint(new.longitude, new.latitude), 4326);
    RETURN new;
END;
$BODY$
LANGUAGE plpgsql;

    This can only be done in a BEFORE trigger:

    CREATE TRIGGER triggerinsert
  BEFORE INSERT
  ON rdpr
  FOR EACH ROW
  EXECUTE PROCEDURE function_update();