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.
