CODEWITHSUNDEEP
postgresqldatabase-trigger
indu
indu

Reputation: 1187

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)

Upvotes: 4

Views: 9024

Answers (2)

indu
indu

Reputation: 1187

I Solved by writing trigger function like 

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 VOLATILE
  COST 100;
ALTER FUNCTION function_update()
  OWNER TO postgres;

and i wrote trigger to update

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

Upvotes: 0

user330315
user330315

Reputation:

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();

Upvotes: 8

Related Questions