postgresql trigger with FOR EACH ROW affect nonaffected rows
<p>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!</p>

<pre><code>CREATE TABLE public.teams
(
  name text,
  coverage_wkt text,
  coverage_geom geometry(Polygon,4326)
)
</code></pre>

<hr>

<pre><code>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();
</code></pre>

<hr>

<pre><code>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;
</code></pre>

<hr>

<pre><code>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))');
</code></pre>

<p>Running the commands above leads to:</p>

<pre><code> 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...
</code></pre>

<p>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: </p>

<blockquote>
  <p>A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies</p>
</blockquote>

<p>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.</p>