CODEWITHSUNDEEP
postgresqltriggers
ajthinking
ajthinking

Reputation: 4608

postgresql trigger with FOR EACH ROW affect nonaffected rows

Im making a INSERT/UPDATE trigger to populate a geometry column from a text column containing a WKT string. But now its overwriting all rows!

CREATE TABLE public.teams
(
  name text,
  coverage_wkt text,
  coverage_geom geometry(Polygon,4326)
)

CREATE TRIGGER trigger_insert_or_update_on_teams
AFTER INSERT OR UPDATE ON teams
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE set_geom_columns();

CREATE FUNCTION set_geom_columns() RETURNS trigger AS $set_geom_columns$
BEGIN
    IF NEW.coverage_wkt IS NOT NULL THEN        
        NEW.name := 'im changed forever';       
        NEW.coverage_geom := ST_GeomFromText(NEW.coverage_wkt,4326);            
    END IF;        
    RETURN NEW;
END; $set_geom_columns$ LANGUAGE plpgsql;

INSERT INTO teams (coverage_wkt) VALUES ('POLYGON((17 59, 19 59, 19 60, 18 60, 17 59))');
INSERT INTO teams (coverage_wkt) VALUES ('POLYGON((18 59, 19 59, 19 60, 18 60, 18 59))');

Running the commands above leads to:

 name |                 coverage_wkt                 |   coverage_geom
      | POLYGON((17 59, 19 59, 19 60, 18 60, 17 59)) | 010300002...
      | POLYGON((18 59, 19 59, 19 60, 18 60, 18 59)) | 010300002...

The populated geometry column holds identical geometries: the last one inserted! That's leading me to believe its affecting all rows each time it is triggered. But from the docs I read:

A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies

Furthermore, while the coverage_geom is actually persistent after "RETURN NEW" I cannot set NEW.name the same way. I don't know if it is related.

Upvotes: 0

Views: 2043

Answers (1)

ajthinking
ajthinking

Reputation: 4608

"You need a BEFORE trigger if you want to change the values of the new record –" As pointed out by a_horse_with_no_name.

Upvotes: 1

Related Questions