Auditoría de tablas con triggers
Construya un registro de auditoría con triggers AFTER INSERT/UPDATE/DELETE que escriban en una tabla audit_log.
Auditoría de tablas con triggers es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.
¿Por qué auditar?
Los registros de auditoría responden a «quién cambió qué y cuándo». Son necesarios para el cumplimiento normativo (HIPAA, SOX e investigaciones del derecho de supresión del RGPD) y para el análisis forense operativo.
La tabla de auditoría
Una tabla central registra todos los cambios:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
user_name TEXT NOT NULL DEFAULT CURRENT_USER,
table_name TEXT NOT NULL,
action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
row_id TEXT,
old_data JSONB,
new_data JSONB
);La función de trigger de auditoría
Una función reutilizable en muchas tablas:
CREATE OR REPLACE FUNCTION audit_row()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, action, row_id, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id::TEXT, OLD.id::TEXT),
CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) END
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;Asociarla a las tablas
Use un trigger para AFTER INSERT/UPDATE/DELETE:
CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_row();to_jsonb(NEW)
El recurso de to_jsonb captura genéricamente la fila completa, sin código específico para cada columna. Funciona con cualquier tabla que tenga una columna id.
Seguimiento del actor
Si la aplicación se ejecuta con un único usuario de la base de datos, CURRENT_USER no es suficiente. Pase el usuario de la aplicación mediante una GUC de sesión:
-- App sets:
SET LOCAL app.user_id = '42';
-- Trigger reads:
INSERT INTO audit_log (... actor_id ...) VALUES (..., current_setting('app.user_id', true)::BIGINT);Auditar columnas específicas
Use la cláusula WHEN del trigger para realizar una auditoría selectiva:
CREATE TRIGGER trg_audit_role_change
AFTER UPDATE OF role ON users
FOR EACH ROW WHEN (OLD.role IS DISTINCT FROM NEW.role)
EXECUTE FUNCTION audit_role_change();Tablas de auditoría por tabla
Alternativa: una tabla de auditoría por cada tabla real (por ejemplo, users_audit) con el mismo esquema y metadatos de auditoría. Es más fácil de consultar, pero requiere mantener más DDL.
Auditoría solo de diferencias
Almacene únicamente las columnas modificadas:
INSERT INTO audit_log (table_name, action, changes)
VALUES (
TG_TABLE_NAME, TG_OP,
(SELECT jsonb_object_agg(key, value)
FROM jsonb_each(to_jsonb(NEW))
WHERE NEW.* IS DISTINCT FROM OLD.* AND to_jsonb(NEW)->key IS DISTINCT FROM to_jsonb(OLD)->key)
);Rendimiento de la tabla de auditoría
La tabla de auditoría crece rápidamente en sistemas con mucha actividad. Particiónela por tiempo:
CREATE TABLE audit_log (...) PARTITION BY RANGE (ts);
CREATE TABLE audit_log_2024_q1 PARTITION OF audit_log
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');Compromiso de rendimiento
Ahora cada INSERT/UPDATE/DELETE escribe una segunda fila. En tablas con muchísimas escrituras, esto puede duplicar la E/S. Haga pruebas, supervise el sistema y decida si el compromiso merece la pena.
Cuándo NO usar triggers de base de datos
Si necesita integración con un bus de eventos, ponga los eventos en cola con NOTIFY o LISTEN, o use herramientas de replicación lógica/CDC (Debezium) en lugar de triggers.
Recapitulación
Los triggers de auditoría son la forma más sencilla de capturar el historial.
- Una función genérica con to_jsonb(NEW/OLD)
- Asóciela a cada tabla auditada
- Particione las tablas de auditoría por tiempo
- Registre explícitamente el actor de la aplicación mediante GUC
Comprobación rápida
Dentro de una función de trigger de auditoría, ¿cómo captura genéricamente la nueva fila como JSONB?
Preguntas frecuentes
¿La lección «Auditoría de tablas con triggers» es gratis?
Sí — el texto completo de «Auditoría de tablas con triggers» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.
¿Qué aprenderé en «Auditoría de tablas con triggers»?
Construya un registro de auditoría con triggers AFTER INSERT/UPDATE/DELETE que escriban en una tabla audit_log. Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.
¿Necesito experiencia previa para empezar SQL Academy?
No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.
¿Cuánto tiempo toma la lección «Auditoría de tablas con triggers»?
La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.
¿Puedo escribir y ejecutar código en esta lección de SQL Academy?
Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.
Todas las lecciones de este curso
- Anatomía de los triggers: BEFORE/AFTER, FOR EACH ROW
- Conceptos básicos de las funciones PL/pgSQL
- Bloques DO y código anónimo
- Auditoría de tablas con triggers