Mysql Triggers

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introducción

  • Notas aclaratorias:
  • Este manual de la wiki está pensado para los alumnos que cursan el C.S. Administración de Sistemas Informáticos.
Por lo tanto la programación de triggers está un poco más orientada a la administración del gestor que al uso que le pueda dar un programador de aplicaciones y no se entra en profundidad sobre como programar y recursos que se pueden emplear como transacciones, bloqueos,...
Si se quiere profundizar en el tema, recomiendo seguir el Curso asociado al módulo de Bases de datos del C.S. Desarrollo de Aplicaciones Multiplataforma, que estoy escribiendo actualmente.


  • Un trigger viene a ser como un procedimiento almacenado que se ejecuta automáticamente cuando sobre una tabla se realiza alguna operación que implique modificar sus datos (DELETE, INSERT, UPDATE).
Por lo tanto un trigger va a estar 'asociado' a una tabla y a un 'tipo de operación' sobre la tabla.
El trigger no tiene que realizar la operación sobre la tabla. La operación sobre la tabla viene a través de una orden SQL (UPDATE, DELETE, INSERT) que se ejecuta. Después o antes de que se ejecute esa orden, por cada fila modificada de la tabla se va a ejecutar el conjunto de instrucciones del trigger.


  • Los usos que le podemos dar a un trigger son:
  • Monitorizar y registrar operaciones sobre las tablas (podemos guardar información sobre quien y cuando se realizó alguna modificación).
  • Verificar que los datos sean correctos antes de añadirlos o usarlos para una modificación.
  • Un mecanismo para implementar columnas calculadas (por ejemplo, la tabla Libros tiene una columna 'numero de ejemplares' y disponemos de la tabla Ejemplares, donde guardamos cada ejemplar. Cada vez que demos de alta un nuevo ejemplo, tendremos que actualizar la columna de Libros).
  • Copias de seguridad de los datos antes de que sean modificados o borrados.


  • Debemos de tener en cuenta que un trigger no se va a ejecutar en caso de una operación en cascada debido a las restricciones de integridad referencial (clave primaria-clave foránea).


  • Un trigger se puede definir para que se ejecute antes (before) o después (after) que la orden SQL que provoca su ejecución.
Normalmente se usa el tipo BEFORE para chequear que los datos son correctos antes de que sean incorporados a la tabla.
El tipo AFTER se suele utilizar para columnas calculadas o para realizar o registrar las operaciones una vez que estas son realizadas en la tabla.


Crear Triggers

Disponéis de varios ejemplos en este enlace.


  • Debéis utilizar la orden DELIMITER al igual que con los procedimientos almacenados, si los creáis desde una ventana de ejecución de consultas.


  • Es necesario tener el permiso TRIGGER otorgado para poder crear un trigger.
Dicho permiso se encuentra en el nivel de seguridad de tablas, por lo que es necesario indicar sobre qué tabla va a tener permiso para crear el trigger.


  • Básicamente cuando decidimos crear un trigger necesitamos determinar:
  • Sobre qué tabla va a aplicarse el trigger.
  • Sobre qué operación se va a aplicar (INSERT / DELETE / UPDATE)
  • Queremos que el trigger se ejecute antes o después de que se realice la operación sobre la tabla (BEFORE => antes; AFTER => después).


  • Indicar que si la operación SQL que va a provocar la ejecución del trigger afecta a múltiples filas (por ejemplo un borrado de muchas filas), el trigger se ejecutará una vez por cada fila afectada.


  • Dentro del trigger vamos a poder acceder a los valores de la fila de la tabla anterior y posterior a la ejecución de la orden sql. Es decir, si realizo una operación de UPDATE sobre una columna, dicha columna tendrá un valor antes de la ejecución de la orden SQL y otro después de la ejecución, ya que le estoy enviando un nuevo valor. Dentro del trigger puedo acceder a dos 'alias' de la tabla a la que afecta el trigger con las mismas columnas que la tabla original:
  • NEW: Tabla que posee los datos de cada columna con los nuevos valores.
  • OLD: Tabla que posee los datos de cada columna con los valores antiguos.
Por ejemplo:
TABLA ALUMNOS:
---nif---- ---nombre----
---1a----- ---Angel-----
Realiza una operación UPDATE con la orden: UPDATE ALUMNOS SET nombre = 'Pedro' WHERE nif='1a'
Si tengo asociado un trigger UPDATE (de cualquiera de los dos tipos, before/after), dentro del código del trigger:
  • NEW.nombre tendrá el valor 'Pedro'.
  • OLD.nombre tendrá el valor 'Angel'.
Operaciones
  • UPDATE:
  • NEW: Valores nuevos
  • OLD: Valores antiguos
  • INSERT
  • NEW: Valores nuevos
  • OLD: NO EXISTE
  • DELETE
  • NEW: NO EXISTE
  • OLD: Valores antiguos


  • La orden LOAD DATA también provoca la ejecución del trigger ya que realiza operaciones de INSERT sobre las tablas.


  • Es posible tener más de un trigger sobre la misma tabla y con el mismo evento.
En estos casos, la ejecución se hará uno después de otro en el orden en que fueron creados.
Si queremos modificar dicho orden, a la hora de crear el trigger tendremos que utilizar la opción trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
De tal forma que el trigger que se está creando se ejecute antes (PRECEDES) o después (FOLLOWS) que el trigger que ya existe (other_trigger_name)


  • Cuando se define un trigger, este estás asociado al usuario que lo creó (CREATE DEFINER=`root`@`localhost` TRIGGER .....)
Dicho usuario debe tener el permiso TRIGGER otorgado con la orden grant sobre la tabla, lo que le da derecho a crear, borrar, mostrar y ejecutar un trigger.
Cuando otro usuario realiza una operación SQL sobre una tabla en la que está asociado el trigger, el usuario que creó el trigger debe conservar el permiso TRIGGER para que se pueda ejecutar.


  • Dentro del cuerpo del trigger vamos a poder acceder a los valores antiguos y nuevos como hemos visto.
  • Para poder modificar el valor nuevo (SET NEW.col=valor) el usuario que creó el trigger necesita tener permiso UPDATE sobre la tabla.
  • Para poder consultar el valor nuevo o antiguo (OLD.col o NEW.col) el usuario que creó el trigger necesita tener el permiso SELECT sobre la tabla.


Gráficamente




Visualizar triggers

  • La orden SQL para ver los triggers asociados a una base de datos o a una tabla es SHOW TRIGGERS.
  • Por defecto muestra los triggers de la base de datos activa. Si queremos mostrar los de otra debemos de utilizar la opción FROM nombre_BD o IN nombre_BD.
  • Si queremos buscar por los triggers asociados a una determinada tabla podemos hacer uso de la cláusula LIKE que busca por patrón (símbolo comodín el %) nombres de tablas.
  • Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula WHERE. Por ejemplo: show triggers where `Definer` like 'root%'
Fijarse que en este caso, en la parte where se pueden emplear todas las 'columnas' que aparecen cuando ejecutamos la orden SHOW TRIGGER. Dichas columnas deben ir entre ` (acento grave).



  • Si queremos obtener información sobre un trigger específico por su nombre, debemos hacer uso de la orden SQL SHOW CREATE TRIGGER.


  • Recordar que la base de datos y tabla donde se guarda toda la información de los triggers es: information_schema.triggers




Modificar triggers

  • La modificación de triggers no es posible.
Es necesario borrar y volver a crear el trigger.




Borrar triggers



Aspectos varios

  • Como comentamos al principio, uno de los objetivos de utilizar triggers era el de registrar las operaciones sobre las tablas.
Uno de los datos que puede que necesitemos es el de obtener el nombre del usuario que realiza la operación.
Para obtener podemos hacer uso de la función USER().
Cuidado con utilizar la función CURRENT_USER() ya que esta devuelve el usuario que creó el trigger


  • Funciones interesantes:
  • NOW(): obtiene el DATETIME actual
  • SYSDATE(): obtiene el DATETIME actual
  • CURDATE(): Obtiene la fecha actual.
  • DATE(SYSDATE()): obtiene el DATE actual
  • TIME(SYSDATE()): obtiene el TIME actual
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'mensaje a mostrar'
Cancela la operación que se estaba realizando y muestra un mensaje.
Lleva el valor 45000 ya que este valor significa 'excepción definida por el usuario' (“unhandled user-defined exception”)
Si queremos que los días-meses aparezcan en español, lo podemos cambiar con la variable del sistema lc_time_names o localmente con la orden: SET lc_time_names = 'es_ES';


Ejercicios propuestos

  • Haz que no se pueda añadir un nuevo departamento si el número de caracteres del nombre a añadir es inferior a 5 caracteres.


  • Cada vez que se añada un nuevo departamento, asigna como 'manager general' al empleado que lleva más tiempo ya como manager.


  • Crea una tabla de nombre REGISTRO con las columnas:
  • id autonumérica Clave primaria
  • usuario: varchar(100)
  • tabla: varchar(100)
  • operacion: varchar(10)
  • fecha-hora: datetime
Crea un nuevo usuario de nombre 'Ejecutar_Triggers' que tenga permiso para crear y ejecutar triggers.
Conéctate con dicho usuario y crea los triggers necesarios para:
  • Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'departments'.
Si ya existen triggers creados, nos los modifiques, crea uno nuevo.
  • Impide que se pueda añadir o modificar un salario a un empleado si este es inferior a 30000 euros o superior a 300000 euros anuales.
Nota: Acuérdate de otorgar los permisos necesarios al usuario creado para poder acceder a las tablas NEW y OLD.


  • Crea una tabla de nombre CONTADOR con las columnas:
  • id autonumérica Clave primaria
  • tipo: varchar(100)
  • valor: int
Añade dos filas con los valores para tipo/valor: 'numEmpleados'/0 'numDepartamentos'/0
Haz que cada vez que haya alguna operación que modifique el número de empleados o de departamentos, se actualice el número total de los mismos.


  • Ejecuta la orden SQL que muestre los triggers creados en la tabla 'departments'.


  • Ejecuta la orden SQL que muestre la información del primer trigger creado.



Solución Ejercicios propuestos

  • Haz que no se pueda añadir un nuevo departamento si el número de caracteres del nombre a añadir es inferior a 5 caracteres.
1 DELIMITER $$
2 CREATE DEFINER=`root`@`localhost` TRIGGER `employees`.`departments_BEFORE_INSERT` BEFORE INSERT ON `departments` FOR EACH ROW
3 BEGIN
4 
5 	IF (CHAR_LENGTH(NEW.dept_name)<5) then
6 		signal sqlstate '45000' set message_text='El número de caracteres del nombre del dept. debe ser superior a 4';
7     END IF;
8 END$$


  • Cada vez que se añada un nuevo departamento, asigna como 'manager general' al empleado que lleva más tiempo ya como manager.
 1 DELIMITER $$
 2 
 3 CREATE DEFINER=`root`@`localhost` TRIGGER `employees`.`departments_AFTER_INSERT` AFTER INSERT ON `departments` FOR EACH ROW
 4 BEGIN
 5 	declare numEmp integer;
 6     
 7     SELECT emp_no
 8     INTO numEmp
 9     FROM dept_manager
10     WHERE to_date='9999-01-01'
11     ORDER BY from_date ASC
12     LIMIT 1;
13     
14     INSERT INTO dept_manager (emp_no,dept_no, from_date, to_date)
15     VALUES (numEmp,NEW.dept_no,CURDATE(),'9999-01-01');
16     
17     
18 END$$


  • Crea una tabla de nombre REGISTRO con las columnas:
  • id autonumérica Clave primaria
  • usuario: varchar(100)
  • tabla: varchar(100)
  • operacion: varchar(10)
  • fecha-hora: datetime
Crea un nuevo usuario de nombre 'Ejecutar_Triggers' que tenga permiso para crear y ejecutar triggers.
Conéctate con dicho usuario y crea los triggers necesarios para:
  • Haz que se registren las operaciones de alta, baja y modificación sobre la tabla 'departments'.
Si ya existen triggers creados, nos los modifiques, crea uno nuevo.
  • Impide que se pueda añadir o modificar un salario a un empleado si este es inferior a 30000 euros o superior a 300000 euros anuales.
Nota: Acuérdate de otorgar los permisos necesarios al usuario creado para poder acceder a las tablas NEW y OLD.
Creamos la tabla REGISTRO:
1 CREATE TABLE `employees`.`REGISTRO` (
2   `id` INT NOT NULL AUTO_INCREMENT,
3   `usuario` VARCHAR(100) NOT NULL,
4   `tabla` VARCHAR(100) NOT NULL,
5   `operacion` VARCHAR(10) NOT NULL,
6   `fecha-hora` DATETIME NOT NULL DEFAULT now(),
7   PRIMARY KEY (`id`));
Fijarse que el campo 'fecha-hora' tiene un valor por defecto que será la fecha-hora del sistema.


Creamos el usuario Ejecutar_Trigger con permisos:
  • TRIGGER a nivel de base de datos.
  • INSERT a nivel de tabla REGISTRO
  • SELECT a nivel de tabla salaries




Enlace a la página principal de la UD3

Enlace a la página principal del curso



-- Ángel D. Fernández González -- (2017).