Disparadores en la base de datos

Disparadores en la base de datos

Empezar

av.riquelmealfonso@edu.gva.es

Introducción

En MySQL, un disparador es un programa almacenado invocado automáticamente en respuesta a un evento (INSERT, UPDATE, or DELETE) que está asociado a una tabla. Por ejemplo, se puede definir un disparador que se invoca automáticamente antes de que una nueva fila sea insertada.MySQL soporta disparadores que se invocan con un evento INSERT, UPDATE or DELETE.

Tipos de disparadores

El estándar de SQL define dos tipos de disparadores: a nivel de fila y a nivel de sentencia.

• A nivel de fila el disparador se activa por cada fila que es insertada, actualizada o eliminada. Por ejemplo, si una tabla ha insertado, actualizado o eliminado 100 filas, el disparador automáticamente se invocará 100 veces, por las 100 filas afectadas.
• A nivel de sentencia el disparador se ejecuta una vez por cada transacción independientemente de las filas insertadas, actualizadas o eliminadas.
• MySQL únicamente soporta los disparadores a nivel de fila. NO soporta los disparadores a nivel de sentencia.

Ventajas

• Los disparadores proporcionan otro modo de comprobar la integridad de los datos.
• Los disparadores gestionan los errores de la capa de la base de datos.
• Los disparadores proporcionana la forma de gestionar la tareas programadas. A través del uso de disparadores, no es necesario esperar a los eventos programados sino que los disparadores ya los invocan automáticamente.

• Los disparadores tan solo pueden proporcionar validaciones extendidas, no todas las validaciones. Para validaciones simples se usan las restricciones NOT NULL, UNIQUE, CHECK and FOREIGN KEY.
• Los disparadores pueden ser difíciles de identificar porque se ejecutan automáticamente en la base de datos, por los que no es visible para las aplicaciones cliente.

Desventajas

Disparadores

A nivel de registro

Los disparadores manejan los prefijos OLD y NEW para distinguir si los valores son los que tenía la tabla antes o después de que se haya producido el evento.

CREATE TRIGGER nombre_disparador {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON nombre_tabla_dispara_evento FOR EACH ROW cuerpo_disparador;

SINTAXIS

Disparadores

VARIAS LÍNEAS DE CÓDIGO

ASIGNANDO ATRIBUTOS UNO A UNO

DELIMITER $$CREATE TRIGGER nombre_trigger AFTER INSERT ON nombre_tabla FOR EACH ROW BEGIN //Instrucciones SQL END;$$ DELIMITER ;

CREATE TRIGGER nombre_triggerBEFORE UPDATE ON nombre_tabla_trigger_ev FOR EACH ROW INSERT INTO nombre_tabla_audit SET nombre_atributo = OLD.nombre_atributo, nombre_atributo = OLD.nombre_atributo, nombre_atributo = OLD.nombre_atributo;

SINTAXIS

SINTAXIS

BORRAR DISPARADOR

DROP TRIGGER nombre_trigger;

MySQL SIGNAL statement

1. El valor SQLSTATE. El valor SQLSTATE devuelve el resultado de la sentencia ejecutada. Para devolver un valor genérico, se usa '45000', el cual indica "unhandled user-defined exception".
2. Se pueden personalizar los valores SQLSTATE, pero lo veremos más adelante.
3. La cláusula SIGNAL incluye la cláusula SET que contiene múltiples elementos de tipo signal. Cada elemento signal tiene el siguiente formato:

MySQL SIGNAL statement permite declarar una excepción dentro de un programa almacenado, tanto si es un procedimiento o función almacenada, un trigger (disparador) o un evento.

SIGNAL condition_value[SET signal_information_item, signal_information_item,...];

SINTAXIS

En esta sintaxis:condition_valueEl valor de la condición indica el valor del error que se quiere mostrar. Podría ser:

condition_information_item_name = simple_value_specification

SINTAXIS

Cada elemento signal se separa con comas.

Ejemplo

DELIMITER $$CREATE PROCEDURE update_salary( IN p_employee_id INT, IN p_salary DECIMAL)BEGIN DECLARE employee_count INT; -- check if employee exists SELECT COUNT(*) INTO employee_count FROM employees WHERE id = p_employee_id; IF employee_count = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Employee not found'; END IF;

-- validate salary IF p_salary < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative'; END IF; -- if every is fine, update the salary UPDATE employees SET salary = p_salary WHERE id = p_employee_id; END $$DELIMITER ;

SINTAXIS

SINTAXIS

Práctica disparadores (UPDATE)

1. Dentro de la base de datos que tienes en Aules. Crea una nueva tabla llamada employees_audit para mantener los cambios de la tabla empleados.
2. Vamos a rellenar esa tabla con la información cada vez que haya una actualización en la tabla employees.

3. Para ello, crea un disparador llamado before_employee_update que se invoque antes de que se realice un cambio en la tabla de employees. 4. Rellena los atributos con los datos de la tabla empleado. El atributo action rellénalo con la palabra update y que la fecha de actualización sea la actual.

CREATE TABLE employees_audit ( id INT AUTO_INCREMENT PRIMARY KEY, employeeNumber INT NOT NULL, lastname VARCHAR(50) NOT NULL, changedat DATETIME DEFAULT NULL, action VARCHAR(50) DEFAULT NULL );

SINTAXIS

Práctica disparadores (UPDATE)

Muestra todos los disparadores con la sentencia:

SHOW TRIGGERS;

SINTAXIS

Actualiza una fila de la tabla de employees.

Visualiza la tabla employees_audit para ver si el disparador se ha activado.

UPDATE employees SET lastName = 'Phan' WHERE employeeNumber = 1056;

SINTAXIS

SELECT * FROM employees_audit;

Práctica disparadores (UPDATE)

SOLUCIÓN

CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW INSERT INTO employees_audit SET action = 'update', employeeNumber = OLD.employeeNumber, lastname = OLD.lastname, changedat = NOW();

SINTAXIS

Se usa el prefijo OLD para acceder a los valores de las columnas employeeNumber y lastname de la fila afectada por el disparador.

Práctica disparadores (INSERT)

2. En la misma base de datos crea la tabla WorkCenterStats.

1. En la misma base de datos crea la tabla WorkCenters.

CREATE TABLE WorkCenterStats ( totalCapacity INT NOT NULL );

CREATE TABLE WorkCenters ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, capacity INT NOT NULL );

SINTAXIS

SINTAXIS

3. Crea un disparador que te devuelva la capacidad total que hay en los centros de trabajo si hay registros en la tabla WorkCenterStats, si es el primer registro guarda el dato de la capacidad de los centros de trabajo.

Práctica disparadores (INSERT)

Añade estos datos a la tabla WorkCenters y comprueba si ha habido algún cambio en la tabla WorkCenterStats.

INSERT INTO WorkCenters(name, capacity)VALUES('Mold Machine', 100);

Añade ahora estos datos y vuelve a llamar a la tabla WorkCenterStats.

INSERT INTO WorkCenters(name, capacity)VALUES('Packing', 200);

SELECT * FROM WorkCenterStats;

Práctica disparadores (INSERT)

SOLUCIÓN

DELIMITER $$CREATE TRIGGER before_workcenters_insert BEFORE INSERT ON WorkCenters FOR EACH ROW BEGIN DECLARE rowcount INT; SELECT COUNT(*) INTO rowcount FROM WorkCenterStats; IF rowcount > 0 THEN UPDATE WorkCenterStats SET totalCapacity = totalCapacity + NEW.capacity; ELSE INSERT INTO WorkCenterStats(totalCapacity) VALUES(NEW.capacity); END IF; END $$ DELIMITER ;

SINTAXIS