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

Question

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)

score 8 · Accepted Answer · 2016-11-10 09:03:38Z

8

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

edited Nov 10, 2016 at 9:03

answered Nov 9, 2016 at 8:59

user330315

Sign up to request clarification or add additional context in comments.

Comments

indu · Accepted Answer · 2016-11-10 11:44:17Z

0

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

edited Nov 10, 2016 at 11:44

answered Nov 9, 2016 at 8:51

indu

1,2173 gold badges10 silver badges15 bronze badges

9 Comments

user330315 Over a year ago

This is the wrong way to do it. Use a before trigger and don't use update

user330315 Over a year ago

It is much more efficient to do that in an BEFORE trigger and assign a value instead of running a (costly) update statement for each and every row that is inserted. There is no need to do that in an AFTER trigger

indu Over a year ago

Before insert not working for me.and i want to know why we should not use use 'update' in function

user330315 Over a year ago

Why is before "not working"? Did you try the example in my answer? And you shouldn't use update because it is a lot slower then simply changing the row that is inserted.

indu Over a year ago

Thanks for help.I have to update other geom column when insert into rdpr lat,long

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related