Disparadores SQL

Conceptos básicos de los disparadores SQL

• Evento desencadenante: El evento que provoca la ejecución de un desencadenante, como las sentencias INSERT, UPDATE o DELETE.
• Acción del Activador: La acción realizada por el desencadenante, que normalmente implica modificaciones en una o más tablas.
• Evento desencadenante: El suceso que inicia la ejecución del activador. Esto incluye operaciones de manipulación de datos como insertar, actualizar o eliminar registros en una tabla.
• Tabla desencadenante: La tabla en la que se define el evento desencadenante, como la tabla que se modifica o a la que se accede.

Cómo funcionan los disparadores SQL

Tipos de disparadores SQL y casos de uso

Activadores Antes y Después

• Disparadores Before(ANTES DE INSERTAR, ANTES DE ACTUALIZAR, ANTES DE ELIMINAR): Estos desencadenantes se ejecutan antes del evento desencadenante. Se pueden utilizar para modificar los datos antes de que se guarden en la base de datos, lo que resulta útil para la validación o limpieza de datos.
• Disparadoresposteriores(DESPUÉS DE INSERTAR, DESPUÉS DE ACTUALIZAR, DESPUÉS DE ELIMINAR): Estos desencadenantes se ejecutan después del evento desencadenante. Pueden ser útiles para acciones que dependen de los cambios realizados por el evento, como mantener un registro de cambios o actualizar tablas relacionadas.

Disparadores de nivel de fila y de nivel de sentencia

• Disparadoresa nivel de fila(PARA CADA FILA): Estos desencadenantes se ejecutan una vez por cada fila afectada por el evento desencadenante. Son útiles para operaciones que deben aplicarse a registros individuales, como mantener un historial de cambios para cada fila.
• Disparadoresde nivel de estado: Estos desencadenantes se ejecutan una vez por cada evento desencadenante, independientemente del número de filas afectadas. Son adecuados para operaciones que deben realizarse una vez por evento, como el mantenimiento de tablas resumen o de auditoría.

Ejemplos de trabajo con disparadores SQL

Sintaxis para crear un activador SQL

 CREATE TRIGGER nombre_disparador {AnTES|DesPUÉS} {INSERTAR|ACTUALIZAR|ELIMINAR} EN nombre_tabla [PARA CADA FILA] cuerpo_disparador;

• CREATE TRIGGER: Es el comando que se utiliza para crear un nuevo trigger.
• nombre_disparador: Un nombre único para el disparador.
• ANTES| DESPUÉS: La temporización del desencadenador, que determina cuándo se ejecuta la acción del desencadenador con respecto al evento desencadenante.
• INSERTAR|ACTUALIZAR|ELIMINAR: El evento desencadenante que activa el activador.
• ON nombre_tabla: Especifica la tabla sobre la que crear el desencadenador.
• PARA CADA FILA: Modificador opcional para disparadores a nivel de fila.
• trigger_body: El código que se ejecutará cuando se active el disparador.

Caso práctico: Creación de un activador

 CREATE TRIGGER trg_salary_history AFTER UPDATE OF salary ON employees FOR EACH ROW INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on) VALUES (:old.employee_id, :old.salary, :new.

, CURRENT_TIMESTAMP);

Modificar y eliminar disparadores SQL

Ejemplo de modificación de un disparador SQL

 ALTER TRIGGER trg_salary_history AFTER UPDATE OF salary ON employees FOR EACH ROW INSERT INTO employees_salary_history (employee_id, old_salary, new_salary, changed_on, updated_by) VALUES (:antiguo.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP, USER);

Ejemplo de eliminación de un disparador SQL

 DROP TRIGGER trg_salary_history;

Este código elimina el disparador 'trg_salary_history' de la base de datos. Ten en cuenta que eliminar un desencadenador no afecta a la tabla ni a los datos de la tabla, sólo elimina la funcionalidad del desencadenador.

Disparadores SQL vs Funciones

Cuándo utilizar activadores y cuándo utilizar funciones

• Utilizar Disparadores: Elige disparadores cuando quieras automatizar tareas en respuesta a eventos de manipulación de datos, realizar comprobaciones de coherencia entre tablas o mantener registros históricos.
• Utilizar funciones: Considera las funciones cuando necesites encapsular lógica reutilizable, realizar cálculos complejos o manipular datos sin desencadenar un evento. Las funciones deben utilizarse cuando la lógica requiera flexibilidad y no dependa de eventos específicos de la tabla.

Consideraciones sobre el rendimiento

• Disparadores: Dado que los desencadenantes se ejecutan automáticamente, pueden introducir una sobrecarga de procesamiento y ralentizar las operaciones de manipulación de datos. El uso excesivo de triggers o la implementación de lógica compleja dentro de los triggers puede afectar negativamente al rendimiento de la base de datos.
• Funciones: Las funciones pueden optimizarse para el rendimiento, ya que se llaman explícitamente, y su tiempo de ejecución suele ser más rápido. Sin embargo, las funciones mal escritas también pueden afectar al rendimiento general del sistema si consumen muchos recursos o se llaman con frecuencia dentro de las operaciones de manipulación de datos.

Recomendaciones para utilizar disparadores y funciones

• Disparadores:
  1. Evita utilizar un número excesivo de desencadenadores, ya que esto puede entorpecer el rendimiento de la base de datos.
  2. Mantén la lógica de los disparadores simple, limpia y centrada en un único propósito.
  3. Utiliza los disparadores principalmente para reforzar la integridad y coherencia de los datos entre tablas relacionadas.
• Funciones:
  1. Optimiza el rendimiento de las funciones minimizando el consumo de recursos y evitando las llamadas a funciones anidadas.
  2. Encapsula los cálculos complejos y la lógica de manipulación de datos en funciones para mejorar la organización y reutilización del código.
  3. Utiliza funciones integradas siempre que sea posible, ya que suelen ser más eficientes que las funciones definidas por el usuario.

Trabajar con disparadores SQL Tablas insertadas

Finalidad de la tabla insertada

• Almacenando nuevos valores para las filas insertadas o actualizadas, permitiendo que el activador haga referencia o compare estos valores.
• Facilitar la implementación de la lógica empresarial, la aplicación de restricciones y las funciones de auditoría, proporcionando un medio para examinar los cambios realizados.
• Mejorar la integridad de los datos, permitiendo que el desencadenador detecte discrepancias entre los valores de los datos antiguos y los nuevos, y aplique las acciones apropiadas antes de consignar los cambios.

Cómo se utilizan los activadores SQL Tabla insertada

• Operaciones INSERT: La tabla insertada contiene todas las filas recién añadidas.
• Operaciones UPDATE: La tabla insertada contiene las filas actualizadas con sus nuevos valores.
• Operaciones DELETE: La tabla insertada no se utiliza, ya que las filas eliminadas se almacenan en otra tabla temporal llamada tabla "eliminada".

Ejemplos prácticos con SQL Triggers Tablas insertadas

Aquí tienes algunos ejemplos de uso de SQL Triggers.

Ejemplo: Registro de cambios en una tabla

 CREATE TRIGGER trg_salary_log AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, modified_at) VALUES (:old.employee_id, :old.salary, :new.salary, CURRENT_TIMESTAMP); END

Ejemplo: Garantizar la integridad de los datos con activadores SQL insertados

 CREATE TRIGGER trg_validate_order BEFORE INSERT ON orders FOR EACH ROW BEGIN DECLARE @stock INT; SELECT stock INTO @stock FROM products WHERE product_id = :new.product_id; IF @stock < :new.quantity THEN ROLLBACK; -- Si la cantidad solicitada es superior a las existencias disponibles, retrocede la transacción RETURN; END IF; END;