Tejne

Referencia en documentación oficial: https://www.sqlite.org/lang_createtrigger.html

¿Qué son los triggers?

Los triggers (disparadores) son operaciones que se realizan automáticamente ante un evento específico de la base de datos. Pueden desencadenarse por una operación DELETE, INSERT o UPDATE de una tabla, o por la operación UPDATE sobre una o más columnas específicas.

Referencias a columnas dentro de un trigger

Las acciones del trigger y la cláusula WHEN pueden acceder a los elementos de la fila afectada mediante referencias del tipo "NEW.nombre-columna" y "OLD.nombre-columna". OLD y NEW son referencias relevantes en los siguientes casos:

• NEW en INSERT o UPDATE
• OLD en UPDATE o DELETE

AFTER Y BEFORE

Las palabras clave BEFORE y AFTER, que pueden anteceder al tipo de evento para el cual el trigger se define, determinan cuándo se ejecutará el trigger en relación a la inserción, modificación o eliminación de la fila.

Es necesario tener esto en cuenta, por ejemplo, en un trigger cuyas acciones involucren un campo autoincremental, que tomará su valor recién después de la ejecución de un INSERT.

Ejemplo de trigger UPDATE

Ejemplo tomado de la documentación de CREATE TRIGGER.

Asumiendo que los registros de cliente son almacenados en la tabla "customers", y las órdenes de los clientes en la tabla "orders", el siguiente trigger UPDATE se asegura que todas las ordenes asociadas sean redirigidas cuando un cliente cambia su dirección postal.

CREATE TRIGGER update_customer_address UPDATE OF address ON customers 
    BEGIN
        UPDATE orders SET address = new.address WHERE customer_name = old.name;
    END;

Entonces, cuando se ejecuta la sentencia

UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';

se dispara la siguiente acción

UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';

Referencia en documentación oficial: https://www.sqlite.org/foreignkeys.html

Asumiendo que la librería está compilada con las restricciones de cláve foránea habilitadas, estas deben ser igualmente habilitadas en tiempo de ejecución para cada conexión con el comando PRAGMA foreign_keys.

sqlite> PRAGMA foreign_keys = ON;

Este comando debe llamarse fuera de una transacción BEGIN...COMMIT ya que dentro de esta no surtirá ningún efecto.

Definición de claves foráneas

Una clave foránea se define dentro de la tabla que la contiene (child table) de la forma:

FOREIGN KEY(nombre_child_key) REFERENCES nombre_parent_table(nombre_parent_key)

ON DELETE y ON UPDATE

Las cláusulas ON DELETE y ON UPDATE determinan la acción que tomará lugar al eliminar filas de la tabla maestra o modificar, en esta tabla, el valor de la clave asociada de un registro existente.

La acción puede ser:

• NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.
• RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.
• SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.
• SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.
• CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.