Triggers en SQLite: CREATE TRIGGER, BEFORE/AFTER y OLD/NEW

Un trigger ejecuta SQL de forma automática

Un trigger en SQLite es un bloque de SQL guardado que se dispara cada vez que ocurre un evento concreto sobre una tabla determinada. Lo escribes una sola vez y SQLite se encarga del "cuándo" por ti.

La estructura es así:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

CREATE TABLE price_history (
    product_id INTEGER,
    old_price REAL,
    new_price REAL,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER log_price_change
AFTER UPDATE OF price ON products
BEGIN
    INSERT INTO price_history(product_id, old_price, new_price)
    VALUES (OLD.id, OLD.price, NEW.price);
END;

INSERT INTO products(name, price) VALUES ('Cuaderno', 5.00);
UPDATE products SET price = 6.50 WHERE name = 'Cuaderno';
SELECT * FROM price_history;

Fíjate que en ningún momento escribimos un INSERT explícito sobre price_history. Lo hizo el trigger. A partir de ahora, cualquier cambio de precio quedará registrado igual, venga de la CLI, de un script o de una aplicación.

Anatomía de CREATE TRIGGER en SQLite

Vamos a desmenuzar la sintaxis trozo a trozo:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE [ OF column_list ] | DELETE }
ON table_name
[ FOR EACH ROW ]
[ WHEN condition ]
BEGIN
    -- una o más sentencias
END;

• Timing — BEFORE se ejecuta antes del cambio, AFTER después, e INSTEAD OF lo reemplaza (solo en vistas).
• Evento — qué operación dispara el trigger. UPDATE OF col1, col2 limita los UPDATE a columnas concretas.
• Tabla — la tabla bajo vigilancia.
• FOR EACH ROW — SQLite solo admite triggers a nivel de fila, así que es implícito. Puedes escribirlo para mayor claridad; no cambia nada.
• WHEN — una condición opcional. El cuerpo del trigger solo se ejecuta si se cumple.
• Cuerpo — una o más sentencias entre BEGIN y END. Cada una debe terminar con punto y coma.

Esa es toda la gramática. La mayoría de los triggers reales tienen entre cinco y diez líneas.

OLD y NEW: la fila que está cambiando

Dentro del cuerpo, dos pseudo-filas te permiten acceder a los datos:

• NEW — la fila entrante. Disponible en triggers de INSERT y UPDATE.
• OLD — la fila existente. Disponible en triggers de UPDATE y DELETE.

Un trigger de DELETE solo tiene OLD. Uno de INSERT solo tiene NEW. Uno de UPDATE tiene ambos.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL NOT NULL
);

CREATE TABLE deletions (
    table_name TEXT,
    row_id INTEGER,
    snapshot TEXT,
    deleted_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER archive_account
AFTER DELETE ON accounts
BEGIN
    INSERT INTO deletions(table_name, row_id, snapshot)
    VALUES ('accounts', OLD.id, OLD.name || ' / ' || OLD.balance);
END;

INSERT INTO accounts(name, balance) VALUES ('Rosa', 120.00);
DELETE FROM accounts WHERE name = 'Rosa';
SELECT * FROM deletions;

La fila eliminada ya no está en accounts, pero sus datos quedaron registrados en deletions antes de desaparecer.

BEFORE: validar o ajustar la fila

Los triggers BEFORE se ejecutan antes de que el cambio se escriba en disco. Son ideales para lanzar un error o normalizar datos:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TEXT
);

CREATE TRIGGER lowercase_email
BEFORE INSERT ON users
BEGIN
    UPDATE users SET email = email; -- noop, solo para ilustrar el contexto
END;

CREATE TRIGGER reject_empty_email
BEFORE INSERT ON users
WHEN NEW.email IS NULL OR trim(NEW.email) = ''
BEGIN
    SELECT RAISE(ABORT, 'el correo no puede estar vacío');
END;

INSERT INTO users(email) VALUES ('ada@example.com');
INSERT INTO users(email) VALUES ('');
SELECT * FROM users;

El segundo INSERT se cancela antes de escribir cualquier fila. RAISE(ABORT, '...') aborta la sentencia actual y revierte todo lo hecho dentro de ella; si necesitas un control más fino, tienes RAISE(FAIL, ...), RAISE(ROLLBACK, ...) y RAISE(IGNORE).

Para validaciones puras de datos, es mejor usar restricciones CHECK: son declarativas y el optimizador las tiene en cuenta. Deja los triggers BEFORE para cuando la regla tenga que consultar otras tablas o hacer algo que un CHECK no pueda expresar.

WHEN: triggers condicionales en SQLite

La cláusula WHEN filtra qué cambios de fila disparan realmente el cuerpo del trigger. Se evalúa fila por fila, una vez que OLD y NEW ya están vinculados:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE big_orders (
    order_id INTEGER,
    total REAL,
    flagged_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER flag_big_order
AFTER INSERT ON orders
WHEN NEW.total >= 1000
BEGIN
    INSERT INTO big_orders(order_id, total) VALUES (NEW.id, NEW.total);
END;

INSERT INTO orders(total, status) VALUES (50, 'paid');
INSERT INTO orders(total, status) VALUES (1500, 'paid');
INSERT INTO orders(total, status) VALUES (2200, 'paid');
SELECT * FROM big_orders;

El primer pedido no pasa el filtro. Los otros dos sí. Sin la cláusula WHEN, cada INSERT habría acabado escribiendo en big_orders, y tendrías que filtrar al leer en vez de al escribir.

INSTEAD OF: cómo hacer escribible una vista

Las vistas son de solo lectura por defecto. Un trigger INSTEAD OF intercepta una escritura sobre la vista y ejecuta tu SQL en su lugar; lo habitual es traducirla a escrituras sobre la tabla (o tablas) subyacentes:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    salary REAL
);

CREATE VIEW employee_summary AS
SELECT id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

CREATE TRIGGER employee_summary_insert
INSTEAD OF INSERT ON employee_summary
BEGIN
    INSERT INTO employees(first_name, last_name, salary)
    VALUES (
        substr(NEW.full_name, 1, instr(NEW.full_name, ' ') - 1),
        substr(NEW.full_name, instr(NEW.full_name, ' ') + 1),
        NEW.salary
    );
END;

INSERT INTO employee_summary(full_name, salary) VALUES ('Boris Petrov', 60000);
SELECT * FROM employees;

La aplicación interactúa con la vista como si fuera una tabla normal. El trigger se encarga de separar los datos en first_name y last_name por detrás, sin que tengas que preocuparte.

Listar y eliminar triggers en SQLite

Los triggers se guardan en sqlite_master junto con las tablas y los índices:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

CREATE TRIGGER touch_note
AFTER UPDATE ON notes
BEGIN
    SELECT 1; -- cuerpo de marcador de posición
END;

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS touch_note;

SELECT count(*) AS remaining
FROM sqlite_master
WHERE type = 'trigger';

DROP TRIGGER IF EXISTS name; es la forma segura. Si eliminas la tabla a la que pertenece un trigger, este se borra automáticamente, así que no hace falta limpiarlo aparte.

Trampas que conviene tener en cuenta

Hay varios detalles con los que la gente tropieza la primera vez:

• Los triggers se disparan por fila, no por sentencia. Un UPDATE que toca 1.000 filas dispara el trigger 1.000 veces. Si el cuerpo del trigger es costoso, la cuenta se acumula muy rápido.
• Los triggers se ejecutan dentro de la transacción que los envuelve. Si la sentencia externa hace rollback, lo que escribió el trigger también se revierte. Normalmente es justo lo que quieres, pero significa que un trigger no sirve como vía de escape para un "registra esto pase lo que pase".
• Los triggers recursivos están desactivados por defecto. Un trigger que modifica la misma tabla no se vuelve a disparar a sí mismo a menos que actives PRAGMA recursive_triggers = ON;. Déjalo apagado salvo que tengas un motivo concreto.
• El código de la aplicación puede saltárselos, pero solo si esquiva la base de datos. Mientras toda escritura pase por SQLite, el trigger se va a ejecutar. Incluso los ORMs que hacen batch con SQL crudo los disparan.
• No repartas la lógica de negocio entre un montón de triggers. Son invisibles desde el punto donde llamas a la consulta: cuando alguien intente depurar "¿de dónde salió esta fila?", le tocará hacer grep sobre sqlite_master. Úsalos para asuntos transversales (logs de auditoría, columnas derivadas, hacer escribibles las vistas) y deja el resto en el código de la aplicación.

Un ejemplo realista de log de auditoría

Vamos a juntar los patrones vistos para registrar todos los cambios de una tabla posts:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    updated_at TEXT
);

CREATE TABLE post_audit (
    post_id INTEGER,
    action TEXT,
    old_title TEXT,
    new_title TEXT,
    at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER posts_audit_update
AFTER UPDATE OF title, body ON posts
BEGIN
    UPDATE posts SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
    INSERT INTO post_audit(post_id, action, old_title, new_title)
    VALUES (NEW.id, 'actualización', OLD.title, NEW.title);
END;

INSERT INTO posts(title, body) VALUES ('Borrador', 'primera versión');
UPDATE posts SET title = 'Publicado' WHERE id = 1;
SELECT id, title, updated_at FROM posts;
SELECT * FROM post_audit;

Con un solo trigger mantienes updated_at al día y, de paso, escribes el registro de auditoría en un único sitio. El código de la aplicación que ejecuta el UPDATE no necesita enterarse de ninguna de las dos cosas.

Lo que viene: soporte para JSON

Los triggers se encargan de automatizar lo que ocurre alrededor de los eventos de fila. La siguiente pieza avanzada de SQLite tiene que ver con lo que puedes guardar dentro de una fila: JSON. SQLite trae un conjunto completo de funciones JSON para consultar y modificar datos estructurados sin salir de SQL, y de eso va la próxima página.