如何避免在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:
-
wgs84_latitude
和wgs84_longitude
字段都不为空 -
utm_zone
,utm_easting
和utm_northing
的每个都不为空
- Both
wgs84_latitude
andwgs84_longitude
fields are not null - Each of
utm_zone
,utm_easting
, andutm_northing
are not null
现在,我对如何实现实现以下目的的更新感到困惑:
Now, I am confused about how to do updates which achieve the following:
- 对
utm_zone
,utm_easting
或utm_northing
进行更新时,触发器将更新wgs_84_latitude
,wgs84_longitude
和yt_albers_geom
- 完成对
wgs84_latitude
或wgs84_longitude
的更新时,所有utm_
字段以及yt_albers_geom
都将更新. - 对
yt_albers_geom
进行更新后,所有坐标字段都将更新.
- When an update is done to
utm_zone
,utm_easting
, orutm_northing
, thenwgs_84_latitude
,wgs84_longitude
, andyt_albers_geom
are updated by a trigger - When an update is done to
wgs84_latitude
orwgs84_longitude
, then all theutm_
fields are updated, as well asyt_albers_geom
. - 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
, orutm_northing
, thenwgs_84_latitude
,wgs84_longitude
, andyt_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
的更新后,所有 utm_
字段以及yt_albers_geom
都将更新.
When an update is done to
wgs84_latitude
orwgs84_longitude
, then all the utm_
fields are updated, as well asyt_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 *;
这篇关于如何避免在PostgreSQL 9.2.1中循环触发调用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!