我有一张桌子:
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:
现在,我对如何实现实现以下目的的更新感到困惑:
Now, I am confused about how to do updates which achieve the following:
这些触发器中的任何一个似乎都会引起触发器触发的无限循环,对吗?
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中循环触发调用
发布评论