Desencadenador (Trigger) de SQLite

Los desencadenadores (Trigger) de SQLite son funciones de retroalimentación de base de datos, que se ejecutan automáticamente cuando ocurren eventos de base de datos especificados/Llame a estas funciones. A continuación se presentan los puntos clave sobre los desencadenadores (Trigger) de SQLite-

• Los desencadenadores (Trigger) de SQLite pueden especificarse para activarse en respuesta a DELETE, INSERT o UPDATE en una tabla de base de datos específica, o cuando se actualizan una o más columnas en una o más tablas especificadas.

• SQLite solo admite desencadenadores (Trigger) de FOR EACH ROW, no hay desencadenadores (Trigger) de FOR EACH STATEMENT. Por lo tanto, la especificación clara de FOR EACH ROW es opcional.

• La cláusula WHEN y la acción del gatillo (Trigger) pueden acceder a las formas utilizadas NEW.column-name Y OLD.column-name El elemento de la fila insertada, eliminada o actualizada, donde column-name es el nombre de la columna de la tabla con la que el gatillo está asociado.

• Si se proporciona una cláusula WHEN, solo se ejecutan las instrucciones SQL para las filas para las que la cláusula WHEN es verdadera. Si no se proporciona la cláusula WHEN, se ejecutan las instrucciones SQL para todas las filas.

• Las palabras clave BEFORE o AFTER deciden cuándo se ejecuta la acción del gatillo, decidiendo si se ejecuta antes o después de la inserción, modificación o eliminación de las filas asociadas.

• Al eliminar la tabla con la que el gatillo está asociado, se elimina automáticamente el gatillo (Trigger).

• La tabla que se va a modificar debe existir en la misma base de datos, como tabla o vista al que se adjunta el gatillo, y debe usar solo tablename，en lugar de database.tablename。

• Una función especial de SQL RAISE() se puede usar para lanzar excepciones dentro de programas de gatillos.

Sintaxis

La siguiente es la sintaxis básica para crear un gatillo.

CREATE　　TRIGGER　trigger_name　[BEFORE|AFTER]　event_name　
ON　table_name
BEGIN
　--　La lógica del gatillo...
END;

Aquí,event_namePueden ser eventos para las tablasINSERT, DELETEYUPDATEOperaciones de base de datostable_name。Puede especificar FOR EACH ROW después del nombre de la tabla.

La siguiente es la sintaxis para crear un gatillo en una o más columnas específicas de una tabla.

CREATE　TRIGGER　trigger_name　[BEFORE|AFTER]　UPDATE　OF　column_name　
ON　table_name
BEGIN
　　　--　La lógica del gatillo aquí...
END;

Ejemplo

Consideremos una situación en la que deseamos auditar cada registro insertado en la tabla COMPANY, que acabamos de crear (si ya existía, se elimina la tabla COMPANY).

sqlite>　CREATE　TABLE　COMPANY,
　　　ID　INT　PRIMARY　KEY　　　　　NOT　NULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOT　NULL,
　　　AGE　　　　　　　　　　　INT　　　　　NOT　NULL,
　　　ADDRESS                            CHAR(50),
　　　SALARY                            REAL
);

Para mantener el estado de prueba de auditoría, siempre que haya nuevas entradas de registros en la tabla COMPANY, crearemos una nueva tabla llamada AUDIT donde insertaremos los mensajes de registro.

sqlite> CREATE TABLE AUDIT(
　　　EMP_ID INT NOT NULL,
　　　ENTRY_DATE TEXT NOT NULL
);

Aquí, ID es el ID del registro AUDIT, EMP_ID es el ID de la tabla COMPANY, DATE conservará la marca de tiempo cuando se cree el registro en la tabla COMPANY. Ahora creamos un desencadenador en la tabla COMPANY, como se muestra a continuación:

sqlite> CREATE TRIGGER audit_log AFTER INSERT　
ON COMPANY
BEGIN
　　　INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;

Ahora, comenzaremos con el trabajo real, comencemos a insertar registros en la tabla COMPANY, lo que causará la creación de un registro de auditoría en la tabla AUDIT. Creemos un registro en la tabla COMPANY, como se muestra a continuación-

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul',　32, 'California',　20000.00 );

Esto creará un registro en la tabla COMPANY, como se muestra a continuación-

ID                            NAME                            AGE                            ADDRESS                    SALARY
----------　　----------　　----------　　----------　　----------
1　　　　　　　　　　　Paul　　　　　　　　32　　　　　　　　　　California　　20000.0

Al mismo tiempo, se creará un registro en la tabla AUDIT. Este registro es el resultado del desencadenador que creamos en la operación INSERT en la tabla COMPANY. Del mismo modo, puede crear desencadenadores para operaciones UPDATE y DELETE según sea necesario.

EMP_ID                     ENTRY_DATE
----------　　-------------------
1　　　　　　　　　　　2013-04-05　06:26:00

listar desencadenadores

Puede obtenersqlite_masterSe listan todos los desencadenadores en la tabla, como se muestra a continuación:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';

La sentencia SQLite anterior solo listará una entrada, como se muestra a continuación-

name
----------
audit_log

Para listar los desencadenadores específicos de una tabla, utilice la cláusula AND y el nombre de la tabla, como se muestra a continuación:

sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

La sentencia SQLite superior también solo lista una entrada, como se muestra a continuación-

name
----------
audit_log

Eliminar disparador

A continuación, se muestra el comando DROP, que se puede usar para eliminar disparadores existentes.

sqlite> DROP TRIGGER trigger_name;