如何在PostgreSQL 9.2.1中避免循环触发调用

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

进一步向下：

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.

大胆强调我的.因此没有无限循环,因为触发器内的更新不会调用另一个触发器.

测试用例

创建测试表(简化,没有不相关的行)：

CREATE TABLE soil_samples (
  pgid SERIAL PRIMARY KEY,yt_albers_geom double precision
);

第一个要求的虚拟触发器：

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

WHEN子句是可选的.在没有实际更改值时阻止触发器触发.

第二个要求的虚拟触发器：

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

沿着这些方向触发第三个要求……

测试

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

触发upbef_utm：空更新,没有任何反应：

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

更新实际更改：第二个触发器upbef_wgs84将不会在UPDATE OF utm_zone上触发！

UPDATE soil_samples SET utm_zone = 0 RETURNING *;

触发upbef_wgs84：

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle demo.