Explicamos cómo crear triggers o disparadores en MySQL Server 5.1. Mostramos qué es un trigger y cómo usarlo en MySQL. Como ejemplo creamos un trigger para simular un deshacer de una tabla, un trigger que guarda de forma automática los valores de los campos anteriores y los nuevos valores en caso de modificación de un registro. Cómo crear, eliminar y mostrar triggers en MySQL.

Trigger (disparador)

Un trigger o disparador en una Base de datos , es un procedimiento que se ejecuta cuando se cumple una condición establecida al realizar una operación. Dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos.

Los triggers son usados para mejorar la administración de la Base de datos, sin necesidad de contar con que el usuario ejecute sentencias de SQL determinadas para tal efecto. Además, pueden generar valores de columnas, pueden prevenir errores de datos, sincronizar tablas, modificar valores de una vista, auditorías de seguridad, etc.

La estructura básica de un trigger es:

  • Llamada de activación: es la sentencia que permite «disparar» el código a ejecutar.
  • Restricción: es la condición necesaria para realizar el código. Esta restricción puede ser de tipo condicional o de tipo nulidad.
  • Acción a ejecutar: es la secuencia de instrucciones a ejecutar una vez que se han cumplido las condiciones iniciales.

Existen dos tipos de disparadores que se clasifican según la cantidad de ejecuciones a realizar:

  • Row Triggers (o disparadores de fila): son aquellos que se ejecutaran n-veces si se llaman n-veces desde la tabla asociada al trigger.
  • Statement Triggers (o disparadores de secuencia): son aquéllos que sin importar la cantidad de veces que se cumpla con la condición, su ejecución es única.

Cómo crear un trigger en MySQL Server 5.1

Para crear un trigger o disparador en MySQL Server deberemos usar alguna aplicación que permita ejecutar sentencias SQL, por ejemplo MySQL Administrator con MySQL Query Browser:

Cómo crear un trigger en MySQL Server 5.1

O desde una terminal (MS-DOS de Windows o Linux) ejecutando el comando «mysql»:

mysql –user=nombre_usuario –pasword=contraseña_usuario base_datos

Cómo crear un trigger en MySQL Server 5.1

O bien usando alguna otra aplicación de terceros, como nuestra aplicación gratuita y 100% open source AjpdSoft Administración Bases de Datos:

Cómo crear un trigger en MySQL Server 5.1

En cuanto dispongamos de la aplicación para ejecutar sentencias SQL y un usuario de MySQL Server con permisos suficientes para crear triggers o disparadores en la base de datos donde queramos, a continuación deberemos analizar para qué vamos a usar el trigger, dependiendo de la tarea a realizar necesitaremos, por ejemplo, una tabla auxiliar. En el ejemplo que vamos a usar queremos que mediante un disparador o trigger de MySQL Server se añada un registro a una tabla auxiliar cada vez que un usuario realice una inserción en una de las tablas de MySQL Server. Para ello crearemos la tabla destino del trigger con la sentencia SQL:

Cómo crear un trigger en MySQL Server 5.1

En la tabla auxiliar de auditoría anterior queremos almacenar el nombre del usuario de MySQL Server y la fecha y hora en la que se haya realizado una inserción en una tabla de una base de datos de nuestro servidor MySQL Server. En el ejemplo auditaremos la inserción de registros en la tabla «factura».

A continuación crearemos el trigger o disparador correspondiente con la sentencia SQL:

Introduciremos la sentencia SQL anterior en MySQL Query Browser o en AjpdSoft Administración Bases de Datos (sin «delimiter $$» ni «delimiter ;») pulsaremos Control + Intro para ejecutar la consulta SQL y, si todo es correcto (tenemos permisos, existe la tabla «factura», existe la tabla «log_accesos» y existe el catálogo o base de datos «ajsoluciones») el trigger quedará almacenado y funcionando:

Cómo crear un trigger en MySQL Server 5.1

En el ejemplo anterior, el trigger se creará en la base de datos «ajsoluciones», con el nombre «tg_auditoria_accesos» para la tabla «factura» y, a partir de ahora, cuando un usuario cree un registro en la tabla «factura» se creará otro de forma automática en la tabla auxiliar «log_accesos» con el nombre del usuario de MySQL Server que ha creado el registro y con la fecha y hora en que realizó la inserción.

Para obtener el usuario actual de MySQL Server hemos usado la función CURRENT_USER() y para obtener la fecha y la hora actuales hemos usado la función NOW().

La sintaxis para crear un trigger en MySQL Server:

Otras acciones o tareas a realizar con los triggers o disparadores de MySQL

Para consultar los triggers o disparadores creados en una base de datos ejecutaremos el comando SQL:

show create triggers;

Nos mostrará un registro por cada trigger creado con los campos: trigger, event, table, statement, timing, created, sql_mode, definer, character_set_client, collation_connection, database_collation. Las importantes son:

  • Trigger: almacena el nombre del disparador.
  • Event: indica el tipo de trigger (insert, update, delete).
  • Table: tabla de la base de datos a la que se asocia el trigger.
  • Statement: código SQL del trigger.
  • Timing: tiempo en que se ejecutará el trigger: before (antes), after (después).
Otras acciones o tareas a realizar con los triggers o disparadores de MySQL

Para eliminar un trigger o disparador existente ejecutaremos la siguiente consulta:

drop trigger nombre_trigger;

Para mostrar la consulta SQL completa de creación de un trigger ejecutaremos el comando:

show create trigger nombre_trigger;

Almacenamiento de los trigger en MySQL: los triggers o disparadores se almacenan en la tabla
TRIGGERS del catálogo del sistema information_schema, para verlos:

select * from information_schema.triggers;

Otras acciones o tareas a realizar con los triggers o disparadores de MySQL

Crear trigger para auditoría de modificaciones en una tabla de una base de datos MySQL

Crear disparador en tabla MySQL para logear todos los cambios de una tabla

Vamos a explicar cómo crear un trigger en MySQL Server que permita guardar en una tabla auxiliar todas las modificaciones que se vayan realizando en todos los campos de una tabla de la base de datos del servidor MySQL Server. Con este trigger podremos saber qué modificaciones se han realizado, con qué usuario de MySQL Server, en qué fecha y hora y qué valores había antes de la modificación y después de la modificación.

Con este ejemplo podremos implementar de forma automática el control de cambios de una tabla, por si, en algún momento necesitamos implementar un deshacer o bien por si queremos recuperar la tabla a un punto anterior en el tiempo.

Realizaremos este trigger para una tabla de ejemplo llamada «factura» con la siguiente estructura:

Crearemos la tabla auxiliar «factura» con los mismos campos para guardar el valor anterior (añadiremos el sufijo «_old») y duplicaremos los campos para guardar el nuevo valor (añadiremos el sufijo «_new»):

A continuación crearemos el trigger o disparador con el código SQL:

Crear trigger para auditoría de modificaciones en una tabla de una base de datos MySQL

El código del disparador o trigger completo:

Vamos a explicar un poco el trigger anterior:

  1. Por un lado usamos AFTER UPDATE para indicar que el trigger se ejecute
    cada vez que un usuario realice alguna modificación en la tabla factura del catálogo o base de datos ajsoluciones.
  2. El trigger insertará un registro en la tabla auxiliar ajsoluciones.auditoria_factura, en dicho registro, el trigger guardará para cada campo el valor anterior y el nuevo valor (si ha habido modificación). Para ello se usan las cláusulas especiales «OLD.nombre_campo» (el trigger obtendrá el valor anterior al cambio del campo) y «NEW.nombre_campo» (el trigger obtendrá el nuevo valor del campo).
  3. La función de MySQL CURRENT_USER() obtendrá y almacenará en la tabla el usuario actual de MySQL.
  4. La función NOW() obtendrá la fecha y hora en que el usuario realiza el cambio en la tabla.

Ejemplo del funcionamiento del trigger en MySQL desde una aplicación Delphi

Si uno de los usuarios de la aplicación abre la ventana de facturas:

Crear trigger para auditoría de modificaciones en una tabla de una base de datos MySQL

Cuando el usuario realice un cambio en cualquier campo de la tabla facturas, por ejemplo, si añade a «Observación» el texto «Prueba trigger AjpdSoft» y guarda los cambios:

Crear trigger para auditoría de modificaciones en una tabla de una base de datos MySQL

El trigger o disparador establecido para esta tabla habrá creado un registro en la tabla auditoria_factura con el valor anterior en el campo «observacion_old» y el valor nuevo en el campo «observacion_new». Para ver esta tabla ejecutaremos la consulta SQL:

select fecha, usuario, observacion_old, observacion_new
from ajsoluciones.auditoria_factura

Crear trigger para auditoría de modificaciones en una tabla de una base de datos MySQL

El trigger almacenará la fecha, la hora, el usuario de MySQL, el valor de los campos anteriores y el valor de los campos modificado.

Con este ejemplo hemos implementado una opción muy interesante para logear o auditar todos los cambios que se produzcan en una tabla MySQL. Por supuesto no se debe abusar de este tipo de trigger pues podría generar tablas con millones de registros y ralentizar la base de datos. Es recomendable usar este tipo de trigger sólo en el caso de tablas importantes para las que se quiera auditar todos los cambios, vigilando siempre el crecimiento de las tablas auxiliares.

Otras opciones y funcionalidades de los trigger en MySQL

  • Seguridad: si queremos que un usuario, además del superusuario «root» tenga permisos para crear triggers o disparadores en una tabla, ejecutaremos el comando SQL:

    GRANT CREATE TRIGGER ON nombre_tabla TO nombre_usuario

  • Seguridad: para dar permisos de creación de triggers para un usuario para todas las tablas ejecutaremos el comando SQL:

GRANT CREATE TRIGGER ON *.* TO nombre_usuario

  • Un ejemplo de un disparador o trigger para calcular el importe de comisión de una venta realizada: si tenemos una tabla donde guardamos las ventas realizadas por cada comercial, con la siguiente estructura:

Podremos realizar un trigger que, de forma automática, calcule la comisión que corresponde a cada venta, dicha comisión se guardará
en el campo «importecomision» y se calculará mediante el siguiente procedimiento almacenado:

Otras opciones y funcionalidades de los trigger en MySQL

El trigger para actualizar el valor del campo «importecomision» de forma automática será: