How To Avoid Looping Trigger Calls In PostgreSQL 9

问题:

I have a table:

CREATE TABLE field_data.soil_samples (
 pgid SERIAL NOT NULL,
 sample_id text,
 project_id text,
 utm_zone integer,
 utm_easting integer,
 utm_northing integer,
 wgs84_longitude double precision,
 wgs84_latitude double precision,
 yt_albers_geom geometry(Point,3578),
 CONSTRAINT soil_samples_pk PRIMARY KEY (pgid)
)

The PostGIS 2.0 geometry in yt_albers_geom is created using a trigger which fires on INSERTS against this table. If the record being inserted satisfies one of the following conditions, the geometry is generated:

1. Both wgs84_latitude and wgs84_longitude fields are not null
2. Each of utm_zone, utm_easting, and utm_northing are not null

Now, I am confused about how to do updates which achieve the following:

1. When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger
2. When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.
3. When an update is done to yt_albers_geom, all of the coordinate fields are updated.

It seems that any of these triggers would cause an infinite loop of trigger firing, correct?

回答1:

You can do this with standard triggers BEFORE UPDATE OF ... ON ....
The manual on CREATE TRIGGER informs:

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

And further down:

A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered.

Bold emphasis mine. So no infinite loops, because the the updates inside the trigger do not invoke another trigger.

Test case

Create test table (simplified, without irrelevant rows):

CREATE TABLE soil_samples (
  pgid SERIAL PRIMARY KEY

 ,utm_zone integer
 ,utm_easting integer
 ,utm_northing integer

 ,wgs84_longitude double precision
 ,wgs84_latitude double precision

 ,yt_albers_geom double precision
);

Dummy trigger for your first requirement:

When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger.

CREATE OR REPLACE FUNCTION trg_upbef_utm()  RETURNS trigger AS
$func$
BEGIN
   NEW.wgs84_latitude  := NEW.wgs84_latitude + 10;
   NEW.wgs84_longitude := NEW.wgs84_longitude + 10;
   NEW.yt_albers_geom  := NEW.yt_albers_geom + 10;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_utm
BEFORE UPDATE OF utm_zone, utm_easting, utm_northing ON soil_samples
FOR EACH ROW
WHEN (NEW.utm_zone     IS DISTINCT FROM OLD.utm_zone    OR
      NEW.utm_easting  IS DISTINCT FROM OLD.utm_easting OR
      NEW.utm_northing IS DISTINCT FROM OLD.utm_northing)  -- optional
EXECUTE PROCEDURE trg_upbef_utm();

The WHEN clause is optional. Prevents the trigger from firing when no value has actually changed.

Dummy trigger for your second requirement:

When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.

CREATE OR REPLACE FUNCTION trg_upbef_wgs84()  RETURNS trigger AS
$func$
BEGIN
   NEW.utm_zone       := NEW.utm_zone + 100;
   NEW.utm_easting    := NEW.utm_easting + 100;
   NEW.utm_northing   := NEW.utm_northing + 100;
   NEW.yt_albers_geom := NEW.yt_albers_geom + 100;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_wgs84
 BEFORE UPDATE OF wgs84_latitude, wgs84_longitude ON soil_samples
 FOR EACH ROW
 WHEN (NEW.wgs84_latitude  IS DISTINCT FROM OLD.wgs84_latitude OR
       NEW.wgs84_longitude IS DISTINCT FROM OLD.wgs84_longitude)  -- optional
 EXECUTE PROCEDURE trg_upbef_wgs84();

Trigger for third requirement along these lines ...

Test

INSERT INTO soil_samples VALUES (1, 1,1,1, 2,2, 3) RETURNING *;

Trigger upbef_utm: empty update, nothing happens:

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

Update with actual change: The second trigger upbef_wgs84 will not fire on UPDATE OF utm_zone!

UPDATE soil_samples SET utm_zone = 0 RETURNING *;

Trigger upbef_wgs84:

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle demo.