So far, we've implemented an insert trigger. What if the value changes for a particular row? In that case, we will require a separate update trigger. We'll change our original function to test the UPDATE case, and we'll use WHEN in our trigger to constrain updates to the column being changed.

Also, note that the following function is written with the assumption that the user wants to always update the changing geometries based on the changing values:

CREATE OR REPLACE FUNCTION chp02.before_insertXYZ() 
  RETURNS trigger AS 
$$ 
BEGIN 
if (TG_OP='INSERT') then 
  if (NEW.geom is null) then 
    NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x,NEW.y), 3734); 
  end if; 
ELSEIF (TG_OP='UPDATE') then 
  NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x,NEW.y), 3734);
end if;

RETURN NEW; 
END; 
 
$$ 
LANGUAGE 'plpgsql'; 
 
CREATE TRIGGER popgeom_insert 
  BEFORE INSERT ON chp02.xwhyzed1 
  FOR EACH ROW EXECUTE PROCEDURE chp02.before_insertXYZ(); 
 
CREATE trigger popgeom_update 
  BEFORE UPDATE ON chp02.xwhyzed1 
  FOR EACH ROW  
  WHEN (OLD.X IS DISTINCT FROM NEW.X OR OLD.Y IS DISTINCT FROM  
    NEW.Y) 
  EXECUTE PROCEDURE chp02.before_insertXYZ();