Tutorial SQL: Triggers y eventos

Triggers BEFORE/AFTER

Los triggers (disparadores) son objetos de base de datos que se ejecutan automáticamente en respuesta a ciertos eventos sobre una tabla. Una característica fundamental de los triggers es el momento en que se ejecutan con respecto a la operación que los activa, lo que se define mediante los modificadores BEFORE y AFTER.

Un trigger puede ejecutarse en dos momentos distintos: justo antes de que ocurra la operación que lo activa (BEFORE) o inmediatamente después de que esta se complete (AFTER). Esta distinción temporal es crucial para determinar qué tipo de acciones podemos realizar y cómo afectarán a nuestros datos.

Triggers BEFORE

Los triggers BEFORE se ejecutan antes de que la operación de modificación de datos tenga lugar. Esto significa que el trigger se activa antes de que los cambios se apliquen a la tabla.

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Código del trigger
END;

Características principales:

• Validación de datos: Permiten verificar que los datos cumplen ciertas reglas antes de insertarlos o modificarlos.

CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    ELSEIF NEW.salary > 100000 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'El salario no puede ser mayor a 100,000';
    END IF;
END;

• Modificación de valores: Pueden alterar los valores que se van a insertar o actualizar antes de que lleguen a la tabla.

CREATE TRIGGER set_create_date
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.create_date = NOW();
    SET NEW.status = 'pendiente';
END;

• Cancelación de operaciones: Pueden impedir que una operación se complete si no cumple ciertas condiciones.

-- PostgreSQL
CREATE OR REPLACE FUNCTION check_product_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.stock < 0 THEN
        RAISE EXCEPTION 'El stock no puede ser negativo';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION check_product_stock();

Triggers AFTER

Los triggers AFTER se ejecutan después de que la operación de modificación de datos se haya completado. Esto significa que el trigger se activa cuando los cambios ya se han aplicado a la tabla.

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- Código del trigger
END;

Características principales:

• Auditoría y registro: Ideales para mantener registros históricos o de auditoría de cambios.

CREATE TRIGGER log_employee_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit_log (employee_id, field, old_value, new_value, change_date)
    VALUES (NEW.id, 'salary', OLD.salary, NEW.salary, NOW());
END;

• Actualización de tablas relacionadas: Permiten mantener la integridad referencial o actualizar datos en tablas relacionadas.

CREATE TRIGGER update_inventory_after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE inventory
    SET quantity = quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

• Notificaciones: Pueden utilizarse para enviar notificaciones o alertas cuando ocurren ciertos eventos.

-- MySQL
CREATE TRIGGER notify_low_stock
AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
    IF NEW.quantity < 10 AND OLD.quantity >= 10 THEN
        INSERT INTO notifications (message, created_at)
        VALUES (CONCAT('Stock bajo para producto ID: ', NEW.product_id), NOW());
    END IF;
END;

Diferencias clave entre BEFORE y AFTER

La elección entre un trigger BEFORE o AFTER depende del propósito específico:

• Acceso a los datos:

• BEFORE: Puede modificar los datos que se van a insertar/actualizar (usando NEW)

• AFTER: No puede modificar los datos de la operación actual, ya que esta ya se completó

• Validación:

• BEFORE: Ideal para validar datos antes de que se guarden

• AFTER: No puede prevenir la operación, solo reaccionar a ella

• Rendimiento:

• BEFORE: Puede evitar operaciones innecesarias al cancelarlas antes de que ocurran

• AFTER: Siempre se ejecuta después de que la operación principal se complete

• Tablas relacionadas:

• BEFORE: Limitado para actualizar otras tablas (en algunas implementaciones)

• AFTER: Perfecto para mantener la consistencia entre tablas relacionadas

Consideraciones de implementación

Al trabajar con triggers BEFORE y AFTER, es importante tener en cuenta:

• Sintaxis específica: MySQL y PostgreSQL tienen diferencias sintácticas en la implementación de triggers.

-- MySQL
CREATE TRIGGER example_trigger
BEFORE INSERT ON tabla
FOR EACH ROW
BEGIN
    -- Código del trigger
END;

-- PostgreSQL
CREATE OR REPLACE FUNCTION example_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Código de la función
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER example_trigger
BEFORE INSERT ON tabla
FOR EACH ROW
EXECUTE FUNCTION example_function();

• Recursividad: Ten cuidado con los triggers que pueden causar actualizaciones recursivas. Por ejemplo, un trigger AFTER UPDATE que a su vez actualiza la misma tabla podría causar un bucle infinito.

• Orden de ejecución: Cuando existen múltiples triggers para el mismo evento y momento, es importante conocer el orden en que se ejecutarán:

• En MySQL, se pueden definir con FOLLOWS y PRECEDES

• En PostgreSQL, se ejecutan en orden alfabético por nombre

-- MySQL: Definir orden de ejecución
CREATE TRIGGER second_trigger
AFTER INSERT ON orders
FOR EACH ROW
FOLLOWS first_trigger
BEGIN
    -- Este trigger se ejecutará después de first_trigger
END;

Los triggers BEFORE y AFTER son herramientas poderosas que, cuando se utilizan correctamente, pueden ayudar a mantener la integridad de los datos, automatizar tareas y simplificar la lógica de la aplicación al mover parte de ella a la capa de base de datos.

Triggers por operación

Los triggers en SQL no solo se clasifican por el momento de ejecución (BEFORE/AFTER), sino también por el tipo de operación que los activa. Cada operación de modificación de datos (INSERT, UPDATE, DELETE) puede tener triggers específicos asociados, permitiendo un control granular sobre diferentes tipos de cambios en nuestras tablas.

Tipos de triggers según la operación

Podemos crear triggers que respondan a tres operaciones fundamentales de manipulación de datos:

• INSERT: Se activan cuando se añaden nuevos registros a una tabla
• UPDATE: Se activan cuando se modifican registros existentes
• DELETE: Se activan cuando se eliminan registros de una tabla

Cada tipo de operación presenta escenarios de uso particulares y consideraciones específicas que debemos tener en cuenta al diseñar nuestros triggers.

Triggers para operaciones INSERT

Los triggers INSERT se activan cuando se añaden nuevos registros a una tabla. Son especialmente útiles para:

• Inicializar valores automáticamente
• Validar datos antes de su inserción
• Mantener tablas de auditoría
• Actualizar tablas relacionadas

-- MySQL: Trigger que genera un código único para cada nuevo producto
CREATE TRIGGER generate_product_code
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.product_code = CONCAT('PROD-', LPAD(RAND() * 10000, 4, '0'));
END;

-- PostgreSQL: Trigger que registra nuevos usuarios en una tabla de auditoría
CREATE OR REPLACE FUNCTION log_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_audit (user_id, action, action_date)
    VALUES (NEW.id, 'INSERT', CURRENT_TIMESTAMP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION log_new_user();

Triggers para operaciones UPDATE

Los triggers UPDATE se activan cuando se modifican registros existentes. Son particularmente valiosos para:

• Validar cambios antes de aplicarlos
• Registrar historial de modificaciones
• Sincronizar datos entre tablas
• Implementar reglas de negocio complejas

-- MySQL: Trigger que impide modificar pedidos ya enviados
CREATE TRIGGER prevent_shipped_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status = 'shipped' AND NEW.status != 'shipped' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No se pueden modificar pedidos ya enviados';
    END IF;
END;

-- PostgreSQL: Trigger que mantiene un historial de cambios de precio
CREATE OR REPLACE FUNCTION track_price_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, change_date)
        VALUES (NEW.id, OLD.price, NEW.price, CURRENT_TIMESTAMP);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION track_price_changes();

Triggers para operaciones DELETE

Los triggers DELETE se activan cuando se eliminan registros de una tabla. Son especialmente útiles para:

• Implementar eliminaciones lógicas en lugar de físicas
• Mantener registros de auditoría de elementos eliminados
• Limpiar datos relacionados en otras tablas
• Validar si un registro puede ser eliminado

-- MySQL: Trigger que implementa borrado lógico
CREATE TRIGGER soft_delete_customer
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO deleted_customers 
    SELECT *, NOW() FROM customers WHERE id = OLD.id;
    
    -- Alternativa al DELETE físico (si se quiere cancelar el DELETE original)
    -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Uso de DELETE directo no permitido';
END;

-- PostgreSQL: Trigger que limpia datos relacionados al eliminar un usuario
CREATE OR REPLACE FUNCTION clean_user_data()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM user_preferences WHERE user_id = OLD.id;
    DELETE FROM user_sessions WHERE user_id = OLD.id;
    
    INSERT INTO user_audit (user_id, action, action_date)
    VALUES (OLD.id, 'DELETE', CURRENT_TIMESTAMP);
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION clean_user_data();

Combinando múltiples operaciones en un trigger

En algunos casos, podemos querer que un mismo trigger responda a diferentes tipos de operaciones. Tanto MySQL como PostgreSQL permiten crear triggers que se activen con múltiples operaciones:

-- MySQL: Trigger que responde a INSERT y UPDATE
CREATE TRIGGER validate_employee_data
BEFORE INSERT, UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'El salario no puede ser negativo';
    END IF;
    
    IF NEW.hire_date > CURDATE() THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'La fecha de contratación no puede ser futura';
    END IF;
END;

-- PostgreSQL: Trigger que audita todas las operaciones
CREATE OR REPLACE FUNCTION audit_all_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, operation, change_time)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', CURRENT_TIMESTAMP);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, change_time)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', CURRENT_TIMESTAMP);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, operation, change_time)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', CURRENT_TIMESTAMP);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION audit_all_changes();

Consideraciones específicas por tipo de operación

Cada tipo de trigger por operación tiene consideraciones particulares:

• Triggers INSERT:

• Solo tienen acceso a los valores NEW (los que se están insertando)

• No pueden acceder a valores OLD (no existen para inserciones)

• Son ideales para generar valores derivados o por defecto

• Triggers UPDATE:

• Tienen acceso tanto a valores OLD como NEW

• Permiten comparar el estado anterior y posterior del registro

• Son perfectos para validar cambios específicos en ciertos campos

• Triggers DELETE:

• Solo tienen acceso a los valores OLD (los que se están eliminando)

• No pueden acceder a valores NEW (no existen para eliminaciones)

• Son útiles para preservar datos históricos antes de su eliminación

Patrones comunes según el tipo de operación

Ciertos patrones de diseño son más adecuados para tipos específicos de operaciones:

• Patrón de auditoría:
• INSERT: Registrar creación de nuevos elementos
• UPDATE: Registrar cambios en campos específicos
• DELETE: Preservar copia del registro eliminado

-- MySQL: Sistema de auditoría completo
CREATE TRIGGER audit_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, operation, record_id, user, timestamp)
    VALUES ('customers', 'INSERT', NEW.id, CURRENT_USER(), NOW());
END;

CREATE TRIGGER audit_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, operation, record_id, user, timestamp)
    VALUES ('customers', 'UPDATE', NEW.id, CURRENT_USER(), NOW());
END;

CREATE TRIGGER audit_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, operation, record_id, user, timestamp)
    VALUES ('customers', 'DELETE', OLD.id, CURRENT_USER(), NOW());
END;

• Patrón de validación:

• INSERT: Validar que los nuevos datos cumplan reglas de negocio

• UPDATE: Validar que los cambios sean permitidos según el estado actual

• DELETE: Validar que la eliminación no viole restricciones de integridad

• Patrón de sincronización:

• INSERT: Propagar nuevos registros a tablas relacionadas

• UPDATE: Mantener consistencia entre tablas cuando cambian valores clave

• DELETE: Limpiar datos dependientes o actualizar contadores

Limitaciones y buenas prácticas

Al trabajar con triggers por operación, es importante considerar:

• Rendimiento: Los triggers añaden sobrecarga a las operaciones DML. Considera su impacto en operaciones masivas.

• Depuración: Los triggers se ejecutan "silenciosamente", lo que puede dificultar la depuración. Implementa un sistema de registro para facilitar el seguimiento.

• Mantenimiento: Documenta claramente el propósito de cada trigger y cómo interactúa con otros triggers y con la lógica de la aplicación.

• Transacciones: Ten en cuenta que los triggers se ejecutan dentro de la misma transacción que la operación que los activa. Si un trigger falla, toda la transacción se revierte.

-- MySQL: Ejemplo de trigger con manejo de errores
CREATE TRIGGER safe_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO error_log (operation, table_name, error_time)
        VALUES ('UPDATE', 'products', NOW());
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Error en trigger de actualización de productos';
    END;
    
    -- Lógica del trigger
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'El precio no puede ser negativo';
    END IF;
END;

Los triggers por operación proporcionan un mecanismo flexible para implementar lógica de negocio a nivel de base de datos, garantizando la integridad de los datos independientemente de la aplicación que acceda a ellos. Cuando se diseñan e implementan correctamente, pueden simplificar significativamente el mantenimiento de la consistencia de los datos y la implementación de reglas de negocio complejas.

Valores OLD y NEW

Cuando trabajamos con triggers en SQL, necesitamos acceder a los datos que están siendo manipulados por la operación que activó el trigger. Para esto, SQL proporciona referencias especiales a los valores de las filas afectadas mediante los identificadores OLD y NEW. Estos identificadores actúan como tablas virtuales que contienen los valores antes y después de la operación, permitiéndonos examinar y manipular los datos durante la ejecución del trigger.

Significado y disponibilidad

Los identificadores OLD y NEW representan estados diferentes de los datos:

• OLD: Contiene los valores originales de la fila antes de ser modificada
• NEW: Contiene los nuevos valores que se están insertando o actualizando

La disponibilidad de estos identificadores depende del tipo de operación que activa el trigger:

Esta tabla muestra una lógica intuitiva: no podemos acceder a valores anteriores (OLD) en una inserción porque la fila no existía, ni a valores nuevos (NEW) en una eliminación porque la fila dejará de existir.

Acceso a los valores

Para acceder a los campos específicos dentro de OLD y NEW, utilizamos la notación de punto:

-- Acceder al campo 'precio' en los valores nuevos y antiguos
OLD.precio
NEW.precio

Veamos ejemplos prácticos para cada tipo de operación:

En triggers INSERT

En una operación INSERT, solo tenemos acceso a NEW:

-- MySQL: Trigger que calcula el precio con IVA antes de insertar
CREATE TRIGGER calcular_precio_con_iva
BEFORE INSERT ON productos
FOR EACH ROW
BEGIN
    SET NEW.precio_con_iva = NEW.precio * 1.21;
END;

-- PostgreSQL: Trigger que genera un código de producto basado en la categoría
CREATE OR REPLACE FUNCTION generar_codigo_producto()
RETURNS TRIGGER AS $$
BEGIN
    NEW.codigo = CONCAT(NEW.categoria, '-', NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_product_insert
BEFORE INSERT ON productos
FOR EACH ROW
EXECUTE FUNCTION generar_codigo_producto();

En triggers UPDATE

En una operación UPDATE, tenemos acceso tanto a OLD como a NEW, lo que nos permite comparar valores:

-- MySQL: Trigger que registra cambios de precio
CREATE TRIGGER registrar_cambio_precio
AFTER UPDATE ON productos
FOR EACH ROW
BEGIN
    IF OLD.precio != NEW.precio THEN
        INSERT INTO historial_precios (producto_id, precio_anterior, precio_nuevo, fecha_cambio)
        VALUES (NEW.id, OLD.precio, NEW.precio, NOW());
    END IF;
END;

-- PostgreSQL: Trigger que impide reducir el salario más de un 10%
CREATE OR REPLACE FUNCTION validar_reduccion_salario()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salario < OLD.salario * 0.9 THEN
        RAISE EXCEPTION 'No se permite reducir el salario más del 10%%';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON empleados
FOR EACH ROW
EXECUTE FUNCTION validar_reduccion_salario();

En triggers DELETE

En una operación DELETE, solo tenemos acceso a OLD:

-- MySQL: Trigger que archiva clientes eliminados
CREATE TRIGGER archivar_cliente
BEFORE DELETE ON clientes
FOR EACH ROW
BEGIN
    INSERT INTO clientes_archivados (id, nombre, email, telefono, fecha_eliminacion)
    VALUES (OLD.id, OLD.nombre, OLD.email, OLD.telefono, NOW());
END;

-- PostgreSQL: Trigger que actualiza inventario al eliminar un producto
CREATE OR REPLACE FUNCTION actualizar_inventario_eliminacion()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE categorias
    SET total_productos = total_productos - 1
    WHERE id = OLD.categoria_id;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_product_delete
AFTER DELETE ON productos
FOR EACH ROW
EXECUTE FUNCTION actualizar_inventario_eliminacion();

Modificación de valores

Una característica importante es que podemos modificar los valores en NEW en triggers BEFORE, pero no en triggers AFTER (donde los cambios ya se han aplicado):

-- MySQL: Trigger que normaliza datos antes de insertar
CREATE TRIGGER normalizar_datos_cliente
BEFORE INSERT ON clientes
FOR EACH ROW
BEGIN
    -- Convertir nombre a mayúsculas
    SET NEW.nombre = UPPER(NEW.nombre);
    
    -- Asegurar que el email esté en minúsculas
    SET NEW.email = LOWER(NEW.email);
    
    -- Establecer fecha de registro si no se proporcionó
    IF NEW.fecha_registro IS NULL THEN
        SET NEW.fecha_registro = CURDATE();
    END IF;
END;

En PostgreSQL, la modificación se realiza devolviendo el registro NEW modificado:

-- PostgreSQL: Trigger que normaliza datos
CREATE OR REPLACE FUNCTION normalizar_datos_cliente()
RETURNS TRIGGER AS $$
BEGIN
    -- Convertir nombre a mayúsculas
    NEW.nombre = UPPER(NEW.nombre);
    
    -- Asegurar que el email esté en minúsculas
    NEW.email = LOWER(NEW.email);
    
    -- Establecer fecha de registro si no se proporcionó
    IF NEW.fecha_registro IS NULL THEN
        NEW.fecha_registro = CURRENT_DATE;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_client_insert
BEFORE INSERT ON clientes
FOR EACH ROW
EXECUTE FUNCTION normalizar_datos_cliente();

Casos de uso prácticos

Los valores OLD y NEW son fundamentales para implementar diversas funcionalidades:

1. Validación de cambios específicos

Podemos validar cambios en campos particulares sin afectar otras operaciones:

-- MySQL: Impedir cambios en el campo 'fecha_creacion'
CREATE TRIGGER proteger_fecha_creacion
BEFORE UPDATE ON pedidos
FOR EACH ROW
BEGIN
    IF OLD.fecha_creacion != NEW.fecha_creacion THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No se permite modificar la fecha de creación';
    END IF;
END;

2. Auditoría selectiva de campos

Podemos registrar solo los cambios en campos específicos:

-- MySQL: Auditoría de cambios en datos sensibles
CREATE TRIGGER auditar_datos_sensibles
AFTER UPDATE ON usuarios
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO auditoria (usuario_id, campo, valor_anterior, valor_nuevo, fecha)
        VALUES (NEW.id, 'email', OLD.email, NEW.email, NOW());
    END IF;
    
    IF OLD.direccion != NEW.direccion THEN
        INSERT INTO auditoria (usuario_id, campo, valor_anterior, valor_nuevo, fecha)
        VALUES (NEW.id, 'direccion', OLD.direccion, NEW.direccion, NOW());
    END IF;
END;

3. Cálculos derivados

Podemos calcular valores basados en otros campos:

-- PostgreSQL: Calcular total con descuento
CREATE OR REPLACE FUNCTION calcular_total_con_descuento()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.cantidad >= 10 THEN
        NEW.descuento = 0.1;  -- 10% de descuento para pedidos grandes
    ELSE
        NEW.descuento = 0;
    END IF;
    
    NEW.total = NEW.precio_unitario * NEW.cantidad * (1 - NEW.descuento);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_order_item_insert
BEFORE INSERT ON items_pedido
FOR EACH ROW
EXECUTE FUNCTION calcular_total_con_descuento();

4. Detección de cambios significativos

Podemos identificar cambios que superen ciertos umbrales:

-- MySQL: Detectar cambios significativos de precio
CREATE TRIGGER detectar_cambio_significativo
AFTER UPDATE ON productos
FOR EACH ROW
BEGIN
    DECLARE porcentaje_cambio DECIMAL(10,2);
    
    IF OLD.precio != NEW.precio THEN
        SET porcentaje_cambio = ABS((NEW.precio - OLD.precio) / OLD.precio) * 100;
        
        IF porcentaje_cambio > 20 THEN
            INSERT INTO alertas (producto_id, mensaje, fecha)
            VALUES (NEW.id, CONCAT('Cambio de precio significativo: ', porcentaje_cambio, '%'), NOW());
        END IF;
    END IF;
END;

Diferencias entre MySQL y PostgreSQL

Existen algunas diferencias importantes en el manejo de OLD y NEW entre estos sistemas:

MySQL

• Acceso directo a OLD y NEW como identificadores
• Modificación de NEW mediante la sentencia SET
• No requiere retornar explícitamente NEW

CREATE TRIGGER ejemplo_mysql
BEFORE INSERT ON tabla
FOR EACH ROW
BEGIN
    SET NEW.campo = UPPER(NEW.campo);
    -- No es necesario retornar NEW
END;

PostgreSQL

• Acceso a OLD y NEW como variables dentro de la función del trigger
• Modificación directa de NEW mediante asignación
• Requiere retornar explícitamente NEW (o NULL para cancelar la operación)

CREATE OR REPLACE FUNCTION ejemplo_postgresql()
RETURNS TRIGGER AS $$
BEGIN
    NEW.campo = UPPER(NEW.campo);
    RETURN NEW;  -- Obligatorio retornar NEW
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ejemplo_trigger
BEFORE INSERT ON tabla
FOR EACH ROW
EXECUTE FUNCTION ejemplo_postgresql();

Buenas prácticas

Al trabajar con valores OLD y NEW, considera estas recomendaciones:

• Verificar cambios reales: Antes de ejecutar lógica costosa, verifica si realmente hubo cambios en los campos relevantes.

-- Solo ejecutar lógica si el precio realmente cambió
IF OLD.precio != NEW.precio THEN
    -- Lógica para manejar el cambio de precio
END IF;

• Validar valores NULL: Ten en cuenta que los campos pueden ser NULL, lo que puede causar comportamientos inesperados en comparaciones.

-- Manejo seguro de valores NULL
IF (OLD.email IS NULL AND NEW.email IS NOT NULL) OR 
   (OLD.email IS NOT NULL AND NEW.email IS NULL) OR
   (OLD.email != NEW.email) THEN
    -- El email ha cambiado
END IF;

• Evitar bucles infinitos: Ten cuidado al modificar la misma tabla dentro de un trigger, ya que podrías activar el mismo trigger recursivamente.

• Documentar el propósito: Incluye comentarios que expliquen claramente qué hace el trigger y cómo utiliza OLD y NEW.

-- Trigger para mantener historial de cambios de estado en pedidos
-- Registra en la tabla historial_estados cada vez que cambia el estado de un pedido
CREATE TRIGGER registrar_cambio_estado
AFTER UPDATE ON pedidos
FOR EACH ROW
BEGIN
    IF OLD.estado != NEW.estado THEN
        INSERT INTO historial_estados (pedido_id, estado_anterior, estado_nuevo, fecha_cambio)
        VALUES (NEW.id, OLD.estado, NEW.estado, NOW());
    END IF;
END;

Los valores OLD y NEW son herramientas fundamentales que nos permiten crear triggers inteligentes capaces de tomar decisiones basadas en el contexto de los datos que están siendo manipulados. Dominar su uso es esencial para implementar lógica de negocio robusta a nivel de base de datos.