Tenemos una tabla de archivos (llamada archivos), en la que uno
de los campos es un MD5 sobre el contenido del archivo (campo llamado
dmd5). En una tabla anexa (llamada aux), necesitamos
llevar la cuenta de cuántos registros tenemos con el mismo MD5, para
esto tenemos dos campos: dmd5 y cont. Así, cada vez que
insertamos un registro en archivos debemos de actualizar el
contador correspondiente en aux. De igual manera, cuando
borramos un registro de archivos, deberemos de decrementar el
contador correspondiente en aux y en caso de que sea el único,
eliminar el registro.
Obviamente esto se puede hacer desde el mismo programa de actualización, pero resulta, y es este caso en particular lo que motivó el empleo de triggers, que son varios programas los que actualizan esta tabla, así que mantener cada uno de ellos se vuelve un tanto cuanto engorroso. La mejor solución es emplear triggers. Cabe señalar que el ejemplo que se muestra a continuación funciona sólo de la versión de PostgreSQL 6.5.3 en adelante, dado que emplea el Procedure Language PL/pgSQL y algunas particularidades integradas a partir de esa versión.
Bueno, el código para actualizar los valores es el siguiente:
DROP FUNCTION inc_aux ();
CREATE FUNCTION inc_aux () RETURNS OPAQUE AS '
DECLARE
myrec record;
BEGIN
SELECT * INTO myrec FROM aux WHERE aux.dmd5 = NEW.dmd5;
IF NOT FOUND THEN
INSERT INTO aux VALUES (NEW.dmd5, 1);
ELSE
UPDATE aux SET cont=cont+1 WHERE dmd5 = NEW.dmd5;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
DROP TRIGGER ins_arc ON archivos;
CREATE TRIGGER ins_arc BEFORE INSERT ON archivos FOR EACH ROW
EXECUTE PROCEDURE inc_aux();
Los DROPs antes de crear la función y el trigger, son para
garantizar que las funciones no existen previamente y sobre todo,
porque tuve que hacer un demonial de pruebas antes de que la cosa
jalara.
Recordemos que los triggers no pueden recibir argumentos y siempre tienen que regresar un valor opaco.
El select se hace para averiguar si existe un registro con el
MD5 y en caso de no existir lo insertamos con el contador en uno y en
caso de que ya exista, incrementamos el contador en uno. Muy
simple. Sólo es de notar que tuvimos que declarar un registro para
hacer la consulta y saber si el registro existe en la tabla anexa para
actualizarlo (update) o si será necesario crear uno nuevo
(insert). El registro NEW se refiere al registro con que
el que fué disparado el trigger.
Eliminamos el trigger para, en caso de que ya exista uno con el
mismo nombre, garantizar que se ejecutará el que vamos a
declarar. Como podemos ver, los triggers están asociados a tablas y
por eso se debe de indicar de que tabla lo eliminamos al momento de
efectuar el drop.
Ahora veamos el caso en que se eliminan registros. Por supuesto, en
este caso estamos eliminando un registro que está en la base, así que
no necesitamos ver primero si existe. El único considerando a tomar en
cuenta es el caso en que el MD5 es único, en cuyo caso habremos
de eliminar de la tabla dmd5 el registro.
DROP FUNCTION dec_aux ();
CREATE FUNCTION dec_aux () RETURNS OPAQUE AS '
BEGIN
UPDATE aux SET cont=cont-1 WHERE dmd5 = OLD.dmd5;
DELETE FROM aux where cont < 1;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';
DROP TRIGGER del_arc ON archivos;
CREATE TRIGGER del_arc AFTER DELETE ON archivos
FOR EACH ROW EXECUTE PROCEDURE dec_aux();
Una manera de probarlo, es la siguiente:
mancha=> SELECT * FROM aux WHERE dmd5='12345678901234567890123456789012';
dmd5|cont
----+----
(0 rows)
mancha=> INSERT INTO archivos VALUES ('BORRAME','/',12345,'31-12-1999','23:59:07',
'12345678901234567890123456789012','12345678901234567890123456789012','Caserola');
INSERT 10981731 1
mancha=> SELECT * FROM aux WHERE dmd5='12345678901234567890123456789012';
dmd5|cont
--------------------------------+----
12345678901234567890123456789012| 1
(1 row)
mancha=> delete from archivos where arc='BORRAME';
DELETE 1
mancha=> SELECT * FROM aux WHERE dmd5='12345678901234567890123456789012';
dmd5|cont
----+----
(0 rows)
Ahora supongamos que tenemos un pequeño sistema de nómina y en el módulo de ABC11.1 queremos tener la garantía mínima de que no se insertará un registro sin nombre o con salario negativo. Además, queremos llevar nota de quién y cuando modificó los registros. Sea la tabla:
CREATE TABLE emp (
nombre text,
salario int4,
last_date datetime,
last_user name);
Definimos la función que ``estampe'' los cambios:
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
BEGIN
-- Verifica que el nombre y el salario se hallan dado
IF NEW.nombre ISNULL THEN
RAISE EXCEPTION ''nombre no puede ser NULL'';
END IF;
IF NEW.salario ISNULL THEN
RAISE EXCEPTION ''% no puede tener un salario NULL'', NEW.nombre;
END IF;
-- Que no tenga salario negativo (puede ser cero)
IF NEW.salario < 0 THEN
RAISE EXCEPTION ''% no puede tener un salario negativo'', NEW.nombre;
END IF;
-- Ahora estampamos quién y cuándo hizo los cambios
NEW.last_date := ''now'';
NEW.last_user := getpgusername();
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Creamos el trigger:
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Y vemos un ejemplo:
mancha=> insert into emp values ('Meiga Pintita', 2500);
INSERT 11635171 1
mancha=> insert into emp values ('Misha Negrita', 2500);
INSERT 11635172 1
mancha=> select * from emp;
nombre |salario|last_date |last_user
-------------+-------+----------------------------+---------
Bill Clinton | 1000|Mon 03 Jan 12:28:12 2000 CST|mancha
Boris Yeltsin| 2000|Mon 03 Jan 12:28:36 2000 CST|mancha
Meiga Pintita| 2500|Wed 05 Jan 13:07:12 2000 CST|mancha
Misha Negrita| 2500|Wed 05 Jan 13:08:37 2000 CST|mancha
(4 rows)
mancha=> insert into emp values ('Perico Cerillo', -1000000);
ERROR: Perico Cerillo no puede tener un salario negativo
mancha=> insert into emp values (NULL , 1000000);
ERROR: nombre no puede ser NULL
mancha=> insert into emp values ('Perico Cerillo', NULL);
ERROR: Perico Cerillo no puede tener un salario NULL
Sin embargo, se nos olvidó hacer una verificación básica:
mancha=> insert into emp values ('', 1000000);
INSERT 11635173 1
mancha=> select * from emp;
nombre |salario|last_date |last_user
-------------+-------+----------------------------+---------
Bill Clinton | 1000|Mon 03 Jan 12:28:12 2000 CST|mancha
Boris Yeltsin| 2000|Mon 03 Jan 12:28:36 2000 CST|mancha
Meiga Pintita| 2500|Wed 05 Jan 13:07:12 2000 CST|mancha
Misha Negrita| 2500|Wed 05 Jan 13:08:37 2000 CST|mancha
|1000000|Wed 05 Jan 13:09:50 2000 CST|mancha
(5 rows)
Queda como ejercicio verificar que no se inserten nombres en blanco.