Conoce SQL – Lenguaje procedimental 4 – Triggers

También llamados disparadores, lo que hace un trigger es estar alerta hasta que sucede algo y saltan. Por ejemplo un foreign key se podría interpretar como un trigger que comprueba que existe un valor en otra tabla cada vez que insertarmos o actualizamos un dato de una tabla. O un UNIQUE podría ser un trigger que antes de insertar comprueba que no está ya insertado uno igual.

Básicamente un trigger está a la escucha de una tabla esperando que se haga un insert, update o delete sobre ella y actuar de una determinada manera. Usos que se le puede dar por ejemplo es para crear logs. Cada vez que un usuario inserte un comentario se añade a otra tabla “El usuario tal a insertado la foto cual” o cada vez que se modifique un perfil por ejemplo. También para validación avanzada de datos. Cuando se ejecuta un insert podemos tirarlo si no nos gusta (al igual que un update o un delete). En el capítulo de los checks mencióne esto no sé si te acuerdas. También podemos hacer una tabla de solo inserción (cancelando todos los deletes)… infinidad de cosas.

También podemos elegir si el trigger actua antes o despues de realizarse la consulta. Depende de la finalidad nos interesa una cosa u otra. Por ejemplo si lo que queremos es validar datos el trigger se tiene que ejecutar antes para así evitar que se lleguen a insertar los datos. Pero si lo que queremos es por ejemplo crear un contador de usuarios, el trigger tiene que saltar después de realizarse la consulta para que solo se ejecute cuando la consulta se ha realizado correctamente y no cuando se ha intentado insertar un usuario y no se ha podido por el motivo que sea, con lo que el contador mostraría información incorrecta.

Como en el capítulo anterior,  usaré ejemplos de Cutrenti (qué bueno que sea software libre!)

Comenzaremos con un trigger que copia datos borrados a otra tabla. Este trigger lo hice cuando implementé el borrado de comentarios en respuesta a posibles vulnerabilidades. El poder borrar es algo con lo que hay que tener mucho cuidado ya que hay que evitar que alguien pueda borrar algo que no es suyo. Pero como siempre (por muy seguro que esté) dejo la posibildad del fallo decidí hacer una especie de “backup” de los comentarios así si alguien encontraba un bug (cosa que no ha sucedido jeje) y se dedicaba a borrar todos los comentarios no tenía mas que restaurarlos de la tabla. El trigger en cuestión es este

CREATE TRIGGER almacenar_comentarios
AFTER DELETE ON tablones
FOR EACH ROW
BEGIN
INSERT INTO tablones_log (id_usuario,n_mensaje,id_autor,contenido,n_mensaje_respuesta,fecha_tablon,id_multimedia) values (old.id_usuario,old.n_mensaje,old.id_autor,old.contenido,old.n_mensaje_respuesta,old.fecha_tablon,old.id_multimedia);
END

La primera línea define el trigger con su nombre. Después indicamos cuando queremos que el trigger salte si antes de la ejecución de la consulta o después. En este caso quería que se ejecutase después ya que solo me interesaba copiar los comentarios que borraban realmente. Como queremos controla la acción de borrado ponemos DELETE y la tabla a la que escucha el trigger es tablones (ON tablones). En inglés tiene bastante sentido que traducido seria “DESPUES de BORRAR EN tablones”. Es lo bueno de estos lenguajes de cuarta generación.

Y después se pone FOR EACH ROW y se abre código y ale a escribir lo que hace el trigger. Las posibilidades del trigger nos permite AFTER o BEFORE (antes o después) y DELETE y/o UPDATE y/o DELETE. Es conveniente solo escuchar aquello que nos interesa, por ejemplo en este trigger sería malo poner que escuchara al insertar y actualizar ya que no haría nada pero mysql ha tenido que lanzar un proceso para ejecutar el trigger y siempre que podamos evitar molestar a mysql la base de datos irá más rápida. Sí, eso tarda 0,0001 milisegundos pero si multiplicas eso por cada fila y por cada consulta haz cuentas…

Ahora un ejemplo de como tirar una consulta. Cuando un usuario se registra en Cutrenti, se inserta en una tabla temporal hasta que es validado. El problema viene si quiero controlar que no se repitan emails. Tengo un UNIQUE en la tabla usuarios pero como primero se insertan en la tabla usuarios_temporales daría el error cuando se mueve el usuario de la tabla temporal a la real. Eso sería realmente molesto para el usuario ya que de repente en el paso final le dice que su correo ya está registrado. En ese momento saltaría el trigger del usuario AFTER TOCAR HUEVOS que consiste en describir la página con argumentos un tanto soeces del tipo INSERT INTO descripciones_pagina (descripcion) VALUES (‘mierda pagina’) , (‘a este le dieron el titulo en la tombola);.

Para evitar esa situación uso un trigger. Como has adivinado este trigger se debería ejecutar antes de insertar el usuario temporal. El código es este:

CREATE TRIGGER comprobar_email_temporal
BEFORE INSERT ON usuarios_temporales
FOR EACH ROW
BEGIN
DECLARE existe int;
SET existe = (select 1 from usuarios WHERE email=new.email);

IF existe IS NOT null THEN
SET new.hash=null;
END IF;
END

Es bien sencillote. Cada vez que se hace insert ejecutamos una consulta donde buscamos el email en la tabla de usuarios reales y si ya existe hacemos una chapucilla para que falle la consulta. Esta chapucilla es poner el hash de validación (el código ese largote) a nulo y como ese campo tiene como restricción NOT NULL la consulta fallará. No hay otra manera en mysql (es la opción que recomiendan en la página de mysql) pero aquí lo ideal sería usar una excepción y ala que alguien se encarge de capturarla y si no se tira la consulta. He de decir que oracle en este aspecto esta muy muy avanzado y es una pena que mysql esté tan verde en ese tema porque el manejo de excepciones es muy interesante.

Entonces la solución para tirar una consulta es coger un campo (lo más fácil es coger la primary key) y ponerle un valor inválido,  por ejemplo nulo en caso de primary key (que lleva implícito el NOT NULL).

Pero, ¿Como hago para cambiar los valores de la consulta?

Has visto que en he hecho uso de unas tablas llamadas old y new. Esas tablas no las he creado yo, son unas temporales que se crean en los triggers para permitirnos hacer algo útil con los mismos.

La tabla new contiene los datos nuevos y la old los viejos (vaya, que listo soy). Me explico.
En un insert no hay datos viejos asi que todos los campos de la tabla old son nulos. Pero en la tabla new tenemos todos los datos que se quieren insertar.

En un delete no hay datos nuevos (porque se borran) asi que new es nulo pero si hay datos viejos (los que hay y si quieren borrar), entonces en la tabla old están los datos actuales de la tabl.

En un update estan en la tabla new los datos nuevos que vamos a meter y en old los datos que van a ser sobreescritos.

Espero haberme explicado bien.

Con los trigger es muy común que las tablas muten. Por ejemplo, si es un trigger de tipo BEFORE INSERT no podemos hacer ni select, ni update ni delete a esa misma tabla porque está bloqueada. He visto casos en los que sí deja hacer select pero eso será por cuestiones internas de mysql que igual hace el select antes de bloquear la tabla o alguna movida interna. Pero vamos, que es imprevisble y no podemos contar con eso porque también he visto que en un principio el trigger no fallaba pero metiendo más datos daba problema de mutación de tabla. Repito, es imprevisible cuando una tabla puede mutar si leemos de ella mientras escribimos (escribir también es borrar) así que mejor no jugar con eso. Si intuyes que puede haber mutación, busca otra manera aunque en principio no te de fallo. Con el tiempo verás con antelación si una tabla puede mutar o no pero hay veces (en trigger complejos) en los que no te das cuenta hasta que peta.

Sabiendo esto ya puedes hacer triggers majos pero no te líes la manta a la cabeza y empieces a hacer triggers como un loco para validar cualquier chorrada, recuerda que consume ciclos de CPU y que por cada fila (FOR EACH ROW) se ejecuta el trigger. Y vamos, ni se te ocurra hacer (ahora que ya lo conoces) validaciones de trigger en el script PHP no solo en eficiencia sino sobretodo por la integridad de los datos ya que siempre que tenemos que hacer algo mano se nos puede escapar algo.

Y como prometí, la emulación de la restricción CHECK mediante trigger. Creo recordar que teníamos una tabla tal que así (me permito añadir el not null para que nos salga el truquillo)

CREATE TABLE paquetes2 (
descripcion varchar(255) NOT NULL,
entregado char(2),
CONSTRAINT paquetes_entregado_ck CHECK (entregado=’si’ or entregado=’no’)
)

con la restricción que solo nos deja poner “si” o “no” en la columna entregado. El trigger podría ser este.

CREATE TRIGGER paquetes_entregado_si_no_insert
BEFORE INSERT ON paquetes2
FOR EACH ROW
BEGIN
IF new.entregado<> ‘si’ AND new.entregado<> ‘no’
THEN
SET new.codigo= NULL ;
END IF;
END

y también sería recomendable insertar uno igual pero con BEFORE UPDATE para evitar cualquier posibilidad de datos inválidos. Recuerdo que el poner <> es lo mismo que != solo que <> es SQL estándar y lo otro no, aunque funciona igual.

Y ya está, ahora practicar y practicar!

You may also like...

16 Responses

  1. Juan Salvador dice:

    Bueno Max, he modificado el triggers que realizaste, solo falta capturar la excepción, pero con esto es suficiente, porque borra la tupla para que no la cargue en la tabla con null.

    mysql> create trigger entregas_insert
    -> before insert on paquete2
    -> for each row
    -> begin
    -> if new.entregado’si’ and new.entregado’no’
    -> then
    -> set new.codigo=NULL;
    -> delete from paquete2 where new.codigo is NULL;
    -> end if;
    -> end
    -> //

  2. Maxpowel dice:

    pero no es necesario borrar la fila porque no se llega a insertar.

    Te agradezco el comentario pero creo que el método que he usado es más eficiente ya que realizas sólo una transacción además de que la fila nunca llega a estar en la tabla evitando así cualquier posible conflico con unique.

    He puesto el método de poner clave primaria como null precisamente para que salte una excepción y cancele la consulta, suponiendo que toda tabla de una base de datos tiene que tener una clave primaria (si no tiene clave primaria ya no es relacional)

  3. Juan Salvador dice:

    Bueno es que he probado tu ejemplo e inserta la tupla y en el campo codigo le asigna null por eso efectuo el delete para borrarla….

  4. Juan Salvador dice:

    ahh ok.. he leido el post y… ya note que cuando hice la tabla no aplique primary key al campo codigo..
    Perdon.. por no haber notado ese detalle jeje

  5. Maxpowel dice:

    si te pasa eso es porque la columna codigo no es clave primaria (primary key)

  6. yodara dice:

    hola aaaa todos… saludos desde guatemala.. ps, les cuento que estoy haciendo una base de datos en oracle y es mi primero proyecto, y no se como hacer un trigger que impida que se ingrese un cliente con sueldo menor a 2,000 o mayor a 10,000, ruego me ayuden por que no me quiero ir a la tumba sin saber, hacer esto jeje…. muxas gracias con anticipacion….y porfa xfa ayudenme …..please..mi correo por si me quieren ayudar en linea es [email protected] pero solo para eso eeeeeeeeeh! jajaja….bueno espero su ayuda adios y feliz dia

  7. Maxpowel dice:

    Hola, para hacer eso no te hace falta un trigger. Usa un check.

    En la definición de la tabla define un check donde “sueldo > 2000 and sueldo < 10000"

    En el caso de que estes practicando con triggers, la clave esta en lanzar una excepcion (con raise_application_error por ejemplo) si new.salario 10000

    No te pongo el codigo exacto para no equivocarme porque hace mucho que no programo para oracle y seguramente mezclaria sintaxis.

    Hecha un ojo este otro articulo http://www.congdegnu.es/2009/04/15/conoce-sql-restricciones-check/

  8. henry dice:

    hola tengo un inconveniente deseo agregar un campo en una tabla que me contenga un dato asi 12345/6 pongo en la restriccion algo asi
    CONSTRAINT REGLASERIE CHECK
    (serie LIKE ‘[0-9][0-9][0-9][0-9][0-9][/][a]’),
    para que obligatoriamente me acepte 5 numero y luego un slah y un numero mas pero me genera error el campo tiene una longitud de 7 he intentado e muchas manera pero siempre me genera el error. si alguien me puede ayudar x favor enviarme la respuesta o algun codigo a mi correo.

    gracias de antemano.

  9. SANTIAGO dice:

    tengo dos bases de datos en el mismo servidor y quiero actualizar e insertar los datos de la tabla1 de la BD1 al momento de insertar datos en la tabla2 de la BD2
    No puedo crear el triggers
    ¡¡¡¡Ayuda por favor????

  10. josias dice:

    disculpa como le ago para validar por ejemplo quireo vender articulos pero hay q validar si en una venta los articulos q se kieren comprar son mayores a los q hay no se pueda hacer la venta

  11. Maxpowel dice:

    Santiago entonces desde mysql simplemente no se puede. Otra opción es modificar añadir la consulta en tu aplicación para que lo inserte ahí también.

    josias soy incapaz de entender tu pregunta, a qué te refieres con artículos “mayores”?

  12. I have got the product or service nowadays, I’ve about fifteen lbs to get minimized, hope these Reduce Fat Fruta Planta might help me.

  13. Sebasmc dice:

    Hola Max, tus explcaciones estan muy bien, enhorabuena.
    Pero tenog una conulta, tengo una tabla relacionada con prestamos de libros, la idea es que el usuario no pueda coger prestado mas de dos libros al dia.
    La atabla es algo asi:
    CREATE TABLE prestamos (
    fechaprestamo date
    socio char(40)
    llibro varchar(250)
    fecharetorno date
    codigolibro serial);

    Habia pensado en un CHECK pero no se como hacerlo para que la restirccion sea por dia.

    Soy bastante novato en esto, a ver si me puedes ayudar.

    Saludos

  14. Maxpowel dice:

    Buenas, la solución es un trigger que ANTES de insertar haga algo tipo:
    SELECT count(*) FROM prestamos WHERE socio = %socio%

    Si count es mayor que 2, cancelas la consulta

  15. miguel dice:

    sabes q tengo un problema quisiera saber como se hace un trigers para q controle la insercion de datos osea si estoy insertando un dato y ya existe q me mande un aviso diciendome q dato existente de antemano os agradezco su colaboracio programo en oracle

  16. hola necesito hacer un trigger que me evite borrar una base de datos ..como ago ayudenme

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *