Casos de uso de PostgreSQL

Los desencadenantes en PostgreSQL son funciones de retrollamada de la base de datos que se ejecutan automáticamente cuando se produce un evento especificado en la base de datos, como una operación `INSERT`, `UPDATE` o `DELETE`. Se utilizan para hacer cumplir las normas empresariales, validar los datos de entrada y mantener la integridad de los datos.

Utilización

Los desencadenantes automatizan tareas y aplican reglas a nivel de base de datos, garantizando que se produzcan operaciones específicas en respuesta a cambios en los datos. Constan de un evento desencadenante, una temporización y la función asociada que debe ejecutarse.

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE }
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

En esta sintaxis, "CREAR DISPARADOR" define un nuevo disparador, especificando cuándo debe dispararse ("ANTES", "DESPUÉS" o "ANTES DE" un evento) y la función que debe ejecutar. Los activadores "ANTES" se suelen utilizar para modificar datos antes de que se escriban en la base de datos, los activadores "DESPUÉS" se utilizan para acciones que deben producirse una vez que los datos se han consignado, y los activadores "ANTES DE" se suelen utilizar en las vistas para realizar las modificaciones necesarias en las tablas subyacentes.

• FOR EACH ROW indica que la función desencadenante se ejecutará una vez por cada fila afectada por el evento desencadenante. En cambio, FOR EACH STATEMENT podría utilizarse para ejecutar la función desencadenante una vez por sentencia SQL, independientemente del número de filas afectadas.
• NEW y OLD son variables de registro especiales dentro de los activadores: NEW contiene los datos de la nueva fila para las operaciones INSERT o UPDATE, y OLD contiene los datos de la fila existente para las operaciones UPDATE o DELETE.

Ejemplos

1. Activador básico de inserción

CREATE FUNCTION log_insert() RETURNS TRIGGER AS $$
BEGIN
    -- Log every insert operation into log_table
    INSERT INTO log_table(action, timestamp) VALUES ('Insert', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert
BEFORE INSERT ON main_table
FOR EACH ROW
EXECUTE FUNCTION log_insert();

Este ejemplo crea un activador que registra cada operación de inserción en la `tabla_principal` en una `tabla_de_registro`.

2. Actualizar activador con condición

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    -- Update the updated_at timestamp field when a row is modified
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_time
BEFORE UPDATE ON main_table
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION update_timestamp();

Este activador actualiza el campo de fecha y hora `updated_at` de `main_table` cada vez que se modifica una fila.

3. Desencadenante complejo de auditoría

CREATE FUNCTION audit_changes() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        -- Log DELETE operations into audit_log
        INSERT INTO audit_log(action, old_data, timestamp) VALUES ('DELETE', OLD, now());
    ELSIF (TG_OP = 'UPDATE') THEN
        -- Log UPDATE operations with both old and new data
        INSERT INTO audit_log(action, old_data, new_data, timestamp) VALUES ('UPDATE', OLD, NEW, now());
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON main_table
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

Este activador registra los cambios realizados en `main_table` en un `audit_log`, capturando tanto los datos antiguos como los nuevos para las operaciones `UPDATE`.

Consejos y buenas prácticas

• Optimiza el rendimiento. Los desencadenantes pueden añadir sobrecarga a las operaciones de la base de datos, así que asegúrate de que sean eficientes.
• Utiliza los desencadenantes con moderación. Evita el uso excesivo de activadores para evitar la complejidad y los posibles problemas de rendimiento.
• Garantiza la atomicidad. Asegúrate de que las funciones desencadenantes son atómicas y gestionan las excepciones para mantener la integridad de los datos.
• Prueba a fondo. Prueba rigurosamente los activadores en un entorno de desarrollo antes de desplegarlos en producción para evitar efectos secundarios no deseados.
• Considera alternativas. Evalúa si los procedimientos almacenados o la lógica de la aplicación podrían ser más adecuados para determinadas operaciones, a fin de garantizar la claridad y la mantenibilidad.