CREATE TRIGGER

La sentencia CREATE TRIGGER se usa para añadir disparadores al esquema de base de datos. Los disparadores son operaciones de base de datos que se realizan automáticamente cuando se produce un evento específico en la base de datos.

Un disparador puede ser especificado para lanzarse cada vez que ocurra un DELETE, INSERT o UPDATE en una tabla en particular de una base de datos. o cada vez que se produzca un UPDATE en una o más columnas especificadas de una tabla.

Actualmente SQLite sólo soporta disparadores FOR EACH ROW, no FOR EACH STATEMENT. Por lo tanto, especificar explícitamente FOR EACH ROW es opcional. FOR EACH ROW implica que las sentencias SQL especificadas en el disparador pueden ser ejecutadas (dependiendo de la cláusula WHEN) para cada fila de la base de datos insertada, modificada o borrada por la sentencia que active el disparador.

Tanto la claúsual WHEN como pas acciones del disparador pueden tener acceso a los elementos de la fila que se inserta, borra o actualiza usando referencias con el formato "NEW.nombre-columna" y "OLD.nombre-columna", donde nombre-columna es el nombre de una columna de la tabla a la que está asociado el disparador. Las referencias OLD y NEW sólo se pueden usar con disparadores para los que los eventos son relevantes, como sigue:

• INSERT las referencias NEW son válidas
• UPDATE las referencias NEW y OLD son válidas
• DELETE las referencias OLD son válidas

Si se indica una cláusula WHEN, las sentencias SQL especificadas sólo son ejecutadas para filas para las que la cláusula WHEN es verdadera. Si no se especifica una cláusula WHEN, las sentencias SQL se ejecutarán para todas las filas.

Las palabras clave BEFORE o AFTER determinan cuando se ejecutarán las acciones del disparador en relación a la inserción, modificación o borrado de la fila asociada.

Una cláusula ON CONFLICT puede ser especificada como parte de una acción UPDATE o INSERT dentro del cuerpo del disparador. Sin embargo si se especifica una cláusula ON CONFLICT como parte de la sentencia que active el disparador, entonces se utiliza en su lugar el proceso de manipulación de conflictos de la sentencia externa.

Los disparadores se eliminan automáticamente cuando la tabla a la que están asociados (la tabla table-name) es eliminada. Sin embargo, si las acciones del disparador hacen referencia a otras tablas, el disparador no es eliminado o modificado si esas otras tablas son eliminadas o modificadas.

Los disparadores se eliminan usando la sentencia DROP TRIGGER.

Restricciones de sintaxis en sentencias UPDATE, DELETE y INSERT en el interior de de disparadores

Las sentencias UPDATE, DELETE e INSERT en el interior de los disparadores no soportan la sintaxis completa para las sentencias UPDATE, DELETE e INSERT. Se aplican las siguientes restricciones:

• El nombre de la tabla a modificar en una sentencia UPDATE, DELETE o INSERT debe ser un nombre de tabla no calificada. En otras palabras, se debe usar sólo "nombretabla", no "basedatos.nombretabla" cuando se especifique la tabla. La tabla a modificar debe existir en la misma base de datos que la tabla o vista a la que estña asociado el disparador.
• El formato "INSERT INTO table DEFAULT VALUES" de la sentencia INSERT no está soportado.
• Las cláusulas INDEXED BY y NOT INDEXED no están soportados para las sentencias UPDATE y DELETE.
• Las cláusulas ORDER BY y LIMIT en las sentencias UPDATE y DELETE no están soportadas. ORDER BY y LIMIT normalmente no están soportadas para UPDATE y DELETE en ningún contexto pero pueden ser habilitadas para sentencias de nivel superior usando la opción de compilación SQLITE_ENABLE_UPDATE_DELETE_LIMIT. Sin embargo, esta opción de compilación sólo se aplica a sentencias UPDATE y DELETE de mayor nivel, no para sentencias UPDATE y DELETE dentro de disparadores.

INSTEAD OF trigger

Los disparadores pueden ser creados en vistas, igual que en tablas normales, especificando INSTEAD OF en la sentencia CREATE TRIGGER. Si se definen uno o más disparadores ON INSERT, ON DELETE o ON UPDATE para una vista, entonces no es un error ejecutar una sentencia INSERT, DELETE o UPDATE en una vista, respectivamente. En su lugar, ejecutar un INSERT, DELETE o UPDATE en una vista hace que los disparadores asociados se activen. Las tablas reales subyacentes no son modificadas (excepto tal vez de forma explícita, por un programa disparador.

Hay que tener en cuenta que las funciones del API sqlite3_changes() y sqlite3_total_changes() no cuentan las activaciones del disparador INSTEAD OF, pero el pragma count_changes si los cuenta.

Ejemplos

Suponiendo que los registros del cliente se almacenan en la tabla de "customers", y que el los registros de pedidos son almacenados en la tabla "orders", el siguiente disparador asegura que todos los "orden" asociados se redirigen cuando un cliente cambia su dirección:

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

Con este disparador, ejecutar la sentencia:

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

hace que se ejecute automáticamente lo siguiente:

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

Cautions On The Use Of BEFORE triggers

Si un disparador BEFORE UPDATE o BEFORE DELTE modifica o borra una fila que iba a ser actualizada o borrada, entonces el resultado de la subsiguiente operación de actualización o borrado queda indefinida. Por otra parte, su un disparador BEFORE modifica o borra una fila, entonces queda indefinido si se ejecutarán o no otros disparadores AFTER, que en otro caso se ejecutarían en esas filas.

El valor de NEW.rowid es indefinido en un disparador BEFORE INSERT en el que el rowid no está asignado explícitamente a un entero.

Debido a los comportamientos descritos, se alienta a los programadores a que se decanten por los disparadores AFTER con preferencia a los BEFORE.

La función RAISE()

Se puede usar una función SQL especial RAISE() en el interior de un programa disparador, con la sintaxis siguiente:

raise-function:

Cuando es invocada una de las tres últimas formas durante la ejecución de un programa de disparo, se ejecuta el proceso especificado por ON CONFLICT (ya sea ABORT, FAIL o ROLLBACK) y la consulta actual termina. Se devuelve un error SQLITE_CONSTRAINT a la aplicación, junto con el mensaje de error especificado.

Cuando se invoca RAISE(IGNORE), el resto del programa actual del disparador, la sentencia que provocó la ejecución del disparador y los programas de disparo subsiguientes que fueron ejecutados son abandonados. No se deshace ningún cambio en la base de datos. Si la sentencia que hizo que se ejecutase el programa disparador es parte del programa disparador, entonces el programa de disparo, entonces el programa de disparo reanuda su ejecución desde el comienzo del siguiente paso.