Uso de disparadores en PostgreSQL

El uso de disparadores (triggers) permite que se ejecuten de manera automática acciones sobre las tablas de nuestra base de datos. De entre todos los lenguajes disponibles, el más sencillo es PL/pgSQL el cual está integrado dentro de PostgreSQL.

¿Qué es un disparador?

Un disparador (trigger en inglés) es un objeto de una base de datos que define una acción que se ejecutará de manera automática. Este disparador se relaciona con una de las tablas y se ejecuta al realizarse una modificación sobre la misma, normalmente con las sentencias INSERT, UPDATE o DELETE.

Esta función definida en el disparador puede estar programado en diversos lenguajes de programación, como PL/Python, Perl y PL, aunque el más sencillo para la creación de este tipo de objetos es PL/pgSQL. Éste es un lenguaje de programación incorporado en PostgreSQL que permite la escritura de funciones dentro de la base de datos. Posee una sintaxis mucho más rápida que cualquier otro lenguaje debido a que se elimina la sobrecarga de conexión a la base de datos. Debido a esto, las ventajas de usar PL/pgSQL son las siguientes:

• Es fácil de usar
• Puede usarse para crear funciones y disparadores
• Añade estructuras de control al lenguaje SQL
• Puede ejecutar cálculos complejos
• Hereda todos los tipos, funciones y operadores definidos por el usuario

¿Cómo se programa un disparador?

Para programar un disparador en PostgreSQL lo primero que debe hacerse es programar la función que se desea que ejecute el disparador. La estructura que tendrá la función será la siguiente:

CREATE FUNCTION funcion_programada()
	RETURNS trigger AS
$BODY$
BEGIN
	-- En esta línea se escribe la operación que se desea automatizar
	RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Una vez creada la función, el siguiente paso es definir el disparador en la tabla en la que se desea que se ejecute:

CREATE TRIGGER nombre_trigger
{BEFORE | AFTER | INSTEAD OF} {event [OR …]}
	ON tabla
[FOR [EACH] {ROW | STATEMENT}]
	EXECUTE PROCEDURE función_programada;

Ejemplo de disparador

Vamos a crear un disparador que actualice la fecha en la que se crearon o editaron los elementos de nuestra tabla espacial. Para eso definimos una tabla que tendrá un atributo del tipo fecha y hora llamado fecha:

CREATE TABLE icv.icv_cons
(
    gid SERIAL PRIMARY KEY,
    elemento VARCHAR (254),
    area integer,
	fecha_creacion TIMESTAMP WITH TIME ZONE,
	fecha_mod TIMESTAMP WITH TIME ZONE,
    geom GEOMETRY(MultiPolygon,25830)
);

Como lo que se desea es que se grabe la fecha al crear nuevos elementos y al modificarlos, escribiremos una función para cada una de las dos operaciones:

CREATE OR REPLACE FUNCTION ()
  RETURNS trigger AS
$BODY$
BEGIN
    NEW. fecha_creacion = now();
    RETURN NEW;	
END;
$BODY$
  LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION public.edited_fecha_column()
  RETURNS trigger AS
$BODY$
BEGIN
    NEW. fecha_mod = now();
    RETURN NEW;	
END;
$BODY$
  LANGUAGE plpgsql;

Una vez hemos creado las funciones, vamos a crear los disparadores en la tabla icv_cons:

CREATE TRIGGER fecha_alta
  BEFORE INSERT
  ON icv.icv_cons
  FOR EACH ROW
  EXECUTE PROCEDURE public.create_fecha_column();

CREATE TRIGGER fecha_mod
  BEFORE UPDATE
  ON icv.icv_cons
  FOR EACH ROW
  EXECUTE PROCEDURE public.edited_fecha_column();

Por último, insertamos los valores dentro de la tabla. Como puede observarse en la siguiente imagen, el atributo fecha_creacion se ha actualizado automáticamente:

Veamos qué sucede cuando, desde QGIS, dibujamos una nueva construcción o editamos la geometría de una ya existente. En la siguiente ortofoto vemos una construcción que no está en nuestra cartografía y que procederemos a digitalizar usando las herramientas que nos proporciona QGIS.

Una vez digitalizado podemos ver que el atributo con la fecha de creación (atributo fecha_creacion) se ha actualizado de manera automática:

Por otro lado, el polígono del siguiente edificio no se corresponde con lo que vemos en la ortofoto:

Con las herramientas de edición de QGIS procedemos a corregir la forma del edificio. Una vez editado vemos que el atributo con la fecha de modificación (fecha_mod) se ha actualizado:

De este modo es sencillo mantener un control sobre la antigüedad de nuestros datos así como la fecha de la última modificación. Con práctica se podrían crear funciones que permitiesen contar el número de ediciones que se ha realizado sobre cada uno de los elementos de nuestra tabla espacial así como conocer quien ha sido el último usuario en realizar la edición.

Enlaces sobre PL / pgSQL y Disparadores

• Documentación PL/pgSQL en la página de PostgreSQL: https://www.postgresql.org/docs/12/plpgsql.html
• Tutorial de PL/pgSQL en w3resource: https://w3resource.com/PostgreSQL/pl-pgsql-declarations.php
• Enlaces en PostgreSQL Tutorial sobre PL/pgSQL (https://www.postgresqltutorial.com/postgresql-stored-procedures/) y Triggers (https://www.postgresqltutorial.com/postgresql-triggers/)