registros - trigger postgresql example

Cómo evitar las llamadas al Trigger de bucle en PostgreSQL 9.2.1

Question

Tengo una mesa:

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

La geometría PostGIS 2.0 en yt_albers_geom se crea usando un disparador que dispara en INSERTS contra esta tabla. Si el registro que se inserta satisface una de las siguientes condiciones, se genera la geometría:

1. Los campos wgs84_latitude y wgs84_longitude no son nulos
2. Cada uno de utm_zone , utm_easting y utm_northing no son nulos

Ahora, estoy confundido acerca de cómo hacer actualizaciones que logren lo siguiente:

1. Cuando se realiza una actualización en utm_zone , utm_easting o utm_northing , entonces wgs_84_latitude , wgs84_longitude y yt_albers_geom se actualizan mediante un disparador
2. Cuando se realiza una actualización en wgs84_latitude o wgs84_longitude , se actualizan todos los campos utm_ , así como yt_albers_geom .
3. Cuando se realiza una actualización a yt_albers_geom , todos los campos de coordenadas se actualizan.

Parece que cualquiera de estos factores desencadenantes causaría un ciclo infinito de activación del disparador, ¿correcto?

Answer 1

Puede hacer esto con activadores estándar BEFORE UPDATE OF ... ON ...
El manual sobre CREATE TRIGGER informa:

El desencadenador solo se activará si al menos una de las columnas enumeradas se menciona como un objetivo del comando ACTUALIZAR.

Y más abajo:

Se activará un desencadenador específico de columna (uno definido mediante la sintaxis UPDATE OF column_name) cuando cualquiera de sus columnas se enumere como destinos en la lista SET del comando UPDATE. Es posible que el valor de una columna cambie incluso cuando el desencadenador no se active, porque los cambios realizados en los contenidos de la fila ANTES de los activadores de ACTUALIZACIÓN no se tienen en cuenta.

Negrita énfasis mío. Entonces no hay bucles infinitos, porque las actualizaciones dentro del disparador no invocan otro disparador.

Caso de prueba

Crear tabla de prueba (simplificada, sin filas irrelevantes):

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

Activador simulado para su primer requisito:

Cuando se realiza una actualización en utm_zone , utm_easting o utm_northing , entonces wgs_84_latitude , wgs84_longitude y yt_albers_geom se actualizan mediante un desencadenador.

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

La cláusula WHEN es opcional. Impide que el disparador se dispare cuando ningún valor realmente ha cambiado.

Activador ficticio para su segundo requerimiento:

Cuando se realiza una actualización en wgs84_latitude o wgs84_longitude , se actualizan todos los campos u tm_ , así como 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();

Desencadenar el tercer requerimiento a lo largo de estas líneas ...

Prueba

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

Desencadenar upbef_utm : actualización vacía, no pasa nada:

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

Actualización con cambio real: el segundo activador upbef_wgs84 no se upbef_wgs84 UPDATE OF utm_zone !

UPDATE soil_samples SET utm_zone = 0 RETURNING *;

Desencadenar upbef_wgs84 :

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> demostración SQLfiddle.