Implementar trigger en PostgreSQL usando PL/pgSQL

En esta ocasión, voy a explicar como podemos crear un Trigger en PostgreSQL usando PL/pgSQL.

Lo primero que se debe de hacer, es crear una función la cual se encargará de manejar los procedimientos de PL. Esto deberá hacerse con la cuenta de usuario postgres en la base de datos donde vamos a crear el trigger. Por tal motivo abriremos una sesión de postgres con el usuario postgres en nuestra base de datos, la cual para fines de este ejemplo usaré el nombre de javoaxian.

javoaxian@darthmaul:~$ psql -U postgres -d javoaxian

Ya que estamos en el prompt de postgres, crearemos la función plpgsql_call_handler y le debemos de indicar donde debe encontrar el archivo plpgsql.so, el cual se encuentra situado en el directorio lib donde fue instalado postgresql, que en mi caso esta en /opt/pgsql/lib.

javoaxian=# CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '/opt/pgsql/lib/plpgsql.so' LANGUAGE C;

Ahora dejaremos de ser el usuario postgres y nos convertiremos en el dueño de la base de datos, que en mi caso será javoaxian.

javoaxian=# \c javoaxian javoaxian

Esto nos pedirá el password del usuario javoaxian y una vez que ingresemos éste, nos indicará que estamos conectados a la base de datos con el usuario que indicamos.

Password for user javoaxian:
You are now connected to database "javoaxian" as user "javoaxian".
javoaxian=>

Ahora que ya ingresamos con nuestro usuario, podemos crear el lenguaje plpgsql en nuestra base de datos y le indicamos la función plpgsql_call_handler que creamos con el usuario postgres.

javoaxian=> CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

Lo anterior nos mostrará algo similar a esto:

NOTICE: using pg_pltemplate information instead of CREATE LANGUAGE parameters
CREATE LANGUAGE

Creado el lenguaje, procederemos a crear el procedimiento almacenado que llamará el trigger que crearemos. Este procedimiento se encargará de verificar que no se inserten más de 100 registros en una tabla llamada usuario, por tal motivo, deberemos tener creada una tabla llamada usuario y para fines de este post puede estar estructurada de la siguiente manera:

javoaxian=> CREATE TABLE usuario(
  id INTEGER NOT NULL PRIMARY KEY,
  nombre VARCHAR(80) NOT NULL);

Ahora crearemos el procedimiento:

javoaxian=> CREATE FUNCTION sp_max_100_registros() RETURNS trigger AS $trigger_max_100_registros$
DECLARE
  registro RECORD;
BEGIN
  SELECT INTO registro COUNT(*) AS numRegistros
  FROM usuario;

  IF registro.numRegistros < 100 THEN
    RETURN NEW;
  ELSE
    RAISE EXCEPTION 'No pueden existir más de 100 registros en la tabla usuario';
    RETURN NULL;
  END IF;
END;
$trigger_max_100_registros$ LANGUAGE plpgsql;

Como se puede observar en el procedimiento, se le deberá poner un nombre, que en este caso es sp_max_100_registros(), seguido de esto le indicamos que vamos a devolver un tipo de dato trigger y que tendrá el nombre de trigger_max_100_registros entre signos de "$".
Posteriormente, declaramos una variable llamada registro en la sección de variables DECLARE y le indicamos que es de tipo RECORD ya que en esta variable se almacenará el resultado de nuestra consulta.
Seguido de esta sección, colocamos la sección BEGIN, la cual nos permitirá poner el comportamiente de nuestro procedimiento hasta encontrar una instrucción END que lo finalize.
Entre las instrucciones BEGIN y END ejecutaremos una sentencia SELECT que cuenta el número de registros en la tabla usuario. Dicha sentencia cuenta con la opción INTO, ya que es la que permite asignar el resultado a nuestra variable registro.
Luego encontraremos un IF con su respectivos THEN, ELSE y END IF, donde preguntamos por medio de la variable registro si obtuvo menos de 100 registros, si la condición es afirmativa, entonces devuelve una variable llamada NEW, la cual indica que puede ser insertado el registro en la tabla usuario, pero en caso contrario, entonces nos devuelve una excepción indicando que no pueden existir más de 100 registros en la tabla usuario.
Por último indicamos el nombre del trigger entre signos de "$" que en el ejemplo es: trigger_max_100_registros y el lenguaje del procedimiento, que en este caso es plpgsql.

Ya que contamos con el procedimiento almacenado, crearemos nuestro trigger, y para hacer esto, deberemos hacer lo siguiente:

javoaxian=> CREATE TRIGGER trigger_max_100_registros
  BEFORE INSERT ON usuario
  FOR EACH ROW EXECUTE PROCEDURE sp_max_100_registros();

La declaración del anterior nos indica que vamos a crear un TRIGGER llamado trigger_max_100_registros, el cual se deberá lanzar antes de realizar un INSERT sobre la tabla usuario para cada registro (FOR EACH ROW), y le indicamos que deberá ejecutar el procedimiento almacenado que creamos, el cual es: sp_max_100_registros().

Ahora ya tenemos creado nuestro trigger y cada vez insertemos un registro, verificará si puede insertar dicho registro.

Para borrar nuestro trigger, lo primero que hay que hacer, será ejecutar la siguiente línea:

javoaxian=> DROP TRIGGER trigger_max_100_registros ON usuario;

En la instrucción anterior indicamos el nombre del trigger que queremos borrar y sobre que tabla está referenciada.

Una vez que borramos el trigger, también deberemos borrar su procedimiento almacenado, y esto lo haremos de esta forma:

javoaxian=> DROP FUNCTION sp_max_100_registros();

Se puede observar, que en el comando anterior deberémos indicar el nombre del procedimiento y que este debe llevar los paréntesis que abren y cierra.

Ahora bien, pondré otro trigger, el cual se encarga de verificar que después de insertar en nuestra tabla usuario un registro cuyo nombre cuente con la palabra luis, ya no permita meter más registros.

El procedimiento sería de esta manera:

CREATE FUNCTION sp_sin_luis() RETURNS trigger AS $trigger_sin_luis$
BEGIN
  PERFORM * FROM usuario WHERE UPPER(nombre) LIKE UPPER('%luis%');

  IF NOT FOUND THEN
    RAISE NOTICE 'Se insertará el registro';
    RETURN NEW;
  ELSE
    RAISE EXCEPTION 'No se insertará el registro';
    RETURN NULL;
  END IF;
END;
$trigger_sin_luis$ LANGUAGE plpgsql;

Y el trigger:

CREATE TRIGGER trigger_sin_luis
  BEFORE INSERT OR UPDATE ON usuario
  FOR EACH ROW EXECUTE PROCEDURE sp_sin_luis();

Entre las cosas diferentes del procedimiento de este nuevo trigger, es que no definimos la sección DECLARE, aparte en lugar de poner la sentencia SELECT ponemos la palabra PERFORM. La diferencia radica en que PERFORM se usa para cuando hacemos consultas en las cuales no vamos a usar el resultado de ésta.
También otra diferencia es en la instrucción IF, ya que usamos la palabra NOT FOUND, la cual sirve para preguntar si no se encontraron registro en la consulta hecha.
La última diferencia del procedimiento está dentro del IF, podemos observar que antes de devolver el nuevo registro (RETURN NEW), usamos la instrucción: RAISE NOTICE, y ésta nos permite mandar un mensaje a la salida estandar de notificación.

La diferencia en la creación del trigger, es que también indicamos que se ejecute cuando se hagan UPDATE's.

Por último, pondré un ejemplo, el cual se encarga de verificar que no se pueda insertar un registro en la tabla usuario que contenga la cadena luis.

Procedimiento:

CREATE FUNCTION sp_no_luis() RETURNS trigger AS $trigger_no_luis$
BEGIN
  IF UPPER(NEW.nombre) LIKE '%LUIS%' THEN
    RAISE EXCEPTION 'El registro contiene el nombre de luis';
    RETURN NULL;
  ELSE
    RAISE NOTICE 'Se agregará el registro';
    RETURN NEW;
  END IF;
END;
$trigger_no_luis$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trigger_no_luis
  BEFORE INSERT OR UPDATE ON usuario
  FOR EACH ROW EXECUTE PROCEDURE sp_no_luis();

La diferencia en este último, es que usamos la instrucción NEW para hacer la comparación. Esta instrucción contiene el nuevo registro y por consiguiente los datos de los campos ha insertar, los cuales podemos hacer referencia por medio del nombre del campo como se puede observar. Algo IMPORTANTE de mencionar, es que para poder usar la instrucción NEW u otra llamada OLD, es que cuando creemos el trigger, deberemos poner la instrucción FOR EACH ROW, ya que sin esta no se crearán estos elementos.

Si deseean ver un poco más de las opciones con que cuenta un trigger, puede ver este archivo, el cual encontré en esta página.