Automatización de Triggers (Disparadores) en SQLite

Introducción

En este laboratorio, explorarás la automatización de triggers (disparadores) de SQLite. Aprenderás cómo crear, validar y gestionar triggers, que ejecutan automáticamente sentencias SQL en respuesta a eventos específicos en una tabla. Este laboratorio te guiará a través de la creación de registros de auditoría y la gestión eficaz de triggers.

Skills Graph

Crear Tablas: employees y employee_audit

En este paso, crearás dos tablas: employees para almacenar los datos de los empleados y employee_audit para registrar las actualizaciones de salario.

Primero, abre la shell (consola) de SQLite en tu terminal.

sqlite3 /home/labex/project/test.db

Este comando abre la shell de SQLite y se conecta a un archivo de base de datos llamado test.db. Si el archivo no existe, SQLite lo creará.

Ahora, crea la tabla employees con la siguiente estructura:

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

Este comando SQL crea una tabla llamada employees con tres columnas:

• id: Un entero que sirve como clave primaria (primary key) para cada empleado.
• name: Un campo de texto para almacenar el nombre del empleado.
• salary: Un número real para almacenar el salario del empleado.

A continuación, crea la tabla employee_audit:

CREATE TABLE employee_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    old_salary REAL,
    new_salary REAL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Esta tabla almacenará un registro de los cambios de salario. Las columnas son:

• id: Un identificador único para cada entrada de auditoría, que se incrementa automáticamente.
• employee_id: El ID del empleado cuyo salario fue cambiado.
• old_salary: El salario antes de la actualización.
• new_salary: El salario después de la actualización.
• updated_at: Una marca de tiempo (timestamp) que indica cuándo ocurrió la actualización.

Insertar Datos de Ejemplo en employees

En este paso, insertarás algunos datos de ejemplo en la tabla employees. Estos datos se utilizarán para probar el trigger (disparador) que crearás en el siguiente paso.

Ejecuta los siguientes comandos SQL para insertar tres registros de empleados:

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000.00);
INSERT INTO employees (id, name, salary) VALUES (2, 'Bob', 60000.00);
INSERT INTO employees (id, name, salary) VALUES (3, 'Charlie', 70000.00);

Estos comandos añaden tres filas a la tabla employees, con los valores especificados de id, name y salary.

Para confirmar que los datos se añadieron correctamente, ejecuta este comando para ver todos los registros en la tabla:

SELECT * FROM employees;

Resultado Esperado:

1|Alice|50000.0
2|Bob|60000.0
3|Charlie|70000.0

Esta salida muestra el id, name y salary para cada registro.

Crear un Trigger: salary_update_trigger

Ahora, crearás un trigger (disparador) que registra automáticamente las actualizaciones de salario en la tabla employee_audit.

Ejecuta el siguiente comando SQL para crear el trigger:

CREATE TRIGGER salary_update_trigger
AFTER UPDATE OF salary ON employees
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.id, OLD.salary, NEW.salary);
END;

Este comando crea un trigger llamado salary_update_trigger. Este trigger se ejecutará automáticamente después de cualquier actualización en la columna salary de la tabla employees.

• AFTER UPDATE OF salary ON employees: Especifica que el trigger se activará después de una actualización en la columna salary de la tabla employees.
• BEGIN ... END: Encierra las sentencias SQL que se ejecutarán cuando se active el trigger.
• INSERT INTO employee_audit ...: Inserta un nuevo registro en la tabla employee_audit con el ID del empleado, el salario anterior y el salario nuevo.
  • OLD.id: Se refiere al id de la fila antes de la actualización.
  • OLD.salary: Se refiere al salary antes de la actualización.
  • NEW.salary: Se refiere al salary después de la actualización.

Probar el Trigger (Disparador)

En este paso, probarás el trigger actualizando el salario de un empleado y luego verificando que se agrega un nuevo registro a la tabla employee_audit.

Actualicemos el salario de Bob:

UPDATE employees SET salary = 65000.00 WHERE id = 2;

Este comando actualiza la columna salary para el empleado con id 2 (Bob) a 65000.00.

Ahora, consulta la tabla employee_audit para ver si el trigger funcionó:

SELECT * FROM employee_audit;

Resultado Esperado:

1|2|60000.0|65000.0|...

Deberías ver una fila en la tabla employee_audit con el ID del empleado Bob, el salario anterior y el salario nuevo. La columna updated_at mostrará la marca de tiempo (timestamp) de la actualización. La marca de tiempo exacta variará dependiendo de cuándo ejecutaste la actualización.

Eliminar el Trigger (Disparador)

En este paso, aprenderás cómo eliminar o remover un trigger de tu base de datos SQLite.

Para eliminar el salary_update_trigger, ejecuta el siguiente comando:

DROP TRIGGER salary_update_trigger;

Este comando elimina el salary_update_trigger de la base de datos.

Para verificar que el trigger ha sido eliminado, puedes consultar la tabla sqlite_master:

SELECT name FROM sqlite_master WHERE type='trigger' AND name='salary_update_trigger';

Esta consulta debería devolver un conjunto de resultados vacío, indicando que el trigger ya no existe.

Ahora, intentemos actualizar el salario de Alice para confirmar que el trigger realmente ha desaparecido:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

Luego, verifica la tabla employee_audit:

SELECT * FROM employee_audit;

Deberías ver que no se ha agregado ninguna entrada nueva a la tabla employee_audit, confirmando que el trigger se ha eliminado correctamente.

Finalmente, sal de la shell de SQLite:

.exit

Resumen

En este laboratorio, has aprendido cómo crear triggers (disparadores) de SQLite que ejecutan automáticamente sentencias SQL en respuesta a eventos de actualización en una tabla específica. Creaste una tabla employees y una tabla employee_audit. Luego, creaste un trigger llamado salary_update_trigger que registra las actualizaciones de salario en la tabla employee_audit. Finalmente, aprendiste cómo eliminar un trigger.