如何避免在PostgreSQL 9.2.1中循环触发调用 [英] How To Avoid Looping Trigger Calls In PostgreSQL 9.2.1

问题描述

我有一张桌子:

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

yt_albers_geom中的PostGIS 2.0几何是使用触发器创建的，该触发器针对此表在INSERTS上触发.如果插入的记录满足以下条件之一，则生成几何:

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. wgs84_latitude和wgs84_longitude字段都不为空
2. utm_zone，utm_easting和utm_northing的每个都不为空

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. 对utm_zone，utm_easting或utm_northing进行更新时，触发器将更新wgs_84_latitude，wgs84_longitude和yt_albers_geom
2. 完成对wgs84_latitude或wgs84_longitude的更新时，所有utm_字段以及yt_albers_geom都将更新.
3. 对yt_albers_geom进行更新后，所有坐标字段都将更新.

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?

推荐答案

您可以使用标准触发器 BEFORE UPDATE OF ... ON ...来完成此操作.
关于CREATE TRIGGER的手册告知:

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

仅当至少列出的列之一为 提到它是UPDATE命令的目标.

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

再往下走:

特定于列的触发器(使用UPDATE OF column_name定义的触发器 语法)将在其任何列被列为目标时触发 UPDATE命令的SET列表.列的值有可能 即使未触发触发器也要更改，因为对 不考虑UPDATE触发器之前的行内容.

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.

创建测试表(简化，没有无关的行):

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

虚拟触发器满足您的第一个要求:

完成对utm_zone，utm_easting或utm_northing的更新时， wgs_84_latitude，wgs84_longitude和yt_albers_geom由触发器更新.

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子句是可选的.防止在没有实际更改任何值时触发触发器.

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

完成对wgs84_latitude或wgs84_longitude的更新后，所有 u tm_字段以及yt_albers_geom都将更新.

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,1,1, 2,2, 3) RETURNING *;

触发upbef_utm:更新为空，没有任何反应:

Trigger upbef_utm: empty update, nothing happens:

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

使用实际更改进行更新:第二个触发器upbef_wgs84不会在UPDATE OF utm_zone上触发！

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 *;

触发upbef_wgs84:

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle演示.

这篇关于如何避免在PostgreSQL 9.2.1中循环触发调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！