SQLite - gatilhos

SQLite Triggerssão funções de retorno de chamada de banco de dados, que são executadas / chamadas automaticamente quando ocorre um evento de banco de dados especificado. A seguir estão os pontos importantes sobre gatilhos SQLite -

O gatilho SQLite pode ser especificado para disparar sempre que ocorrer um DELETE, INSERT ou UPDATE de uma determinada tabela de banco de dados ou sempre que ocorrer um UPDATE em uma ou mais colunas especificadas de uma tabela.
No momento, o SQLite suporta apenas gatilhos FOR EACH ROW, não gatilhos FOR EACH STATEMENT. Portanto, especificar explicitamente FOR EACH ROW é opcional.
Tanto a cláusula WHEN quanto as ações de gatilho podem acessar elementos da linha que está sendo inserida, excluída ou atualizada usando referências do formulário NEW.column-name e OLD.column-name, em que nome-da-coluna é o nome de uma coluna da tabela à qual o acionador está associado.
Se uma cláusula WHEN for fornecida, as instruções SQL especificadas serão executadas apenas para linhas para as quais a cláusula WHEN é verdadeira. Se nenhuma cláusula WHEN for fornecida, as instruções SQL serão executadas para todas as linhas.
A palavra-chave BEFORE ou AFTER determina quando as ações do gatilho serão executadas em relação à inserção, modificação ou remoção da linha associada.
Os gatilhos são eliminados automaticamente quando a tabela à qual estão associados é eliminada.
A tabela a ser modificada deve existir no mesmo banco de dados que a tabela ou visão à qual o gatilho está anexado e deve-se usar apenas tablename não database.tablename.
Uma função SQL especial RAISE () pode ser usada dentro de um programa de gatilho para levantar uma exceção.

Sintaxe

A seguir está a sintaxe básica para criar um trigger.

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
 -- Trigger logic goes here....
END;

Aqui, event_namepoderia ser a operação de banco de dados INSERT, DELETE e UPDATE na tabela mencionadatable_name. Você pode opcionalmente especificar FOR EACH ROW após o nome da tabela.

A seguir está a sintaxe para criar um gatilho em uma operação UPDATE em uma ou mais colunas especificadas de uma tabela.

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name 
ON table_name
BEGIN
   -- Trigger logic goes here....
END;

Exemplo

Vamos considerar um caso em que queremos manter um teste de auditoria para cada registro inserido na tabela COMPANY, que criamos da seguinte forma (Abandone a tabela COMPANY se você já a tiver).

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

Para dar continuidade ao teste de auditoria, iremos criar uma nova tabela denominada AUDIT onde serão inseridas as mensagens de log, sempre que houver uma entrada na tabela COMPANY para um novo registo.

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

Aqui, ID é o ID do registro de AUDIT e EMP_ID é o ID que virá da tabela COMPANY e DATE manterá o carimbo de data / hora em que o registro será criado na tabela COMPANY. Agora vamos criar um gatilho na tabela COMPANY da seguinte forma -

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

Agora, vamos começar o trabalho real, vamos começar a inserir o registro na tabela COMPANY que deve resultar na criação de um registro de log de auditoria na tabela AUDIT. Crie um registro na tabela COMPANY da seguinte forma -

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

Isso criará um registro na tabela COMPANY, que é a seguinte -

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

Ao mesmo tempo, um registro será criado na tabela AUDIT. Este registro é o resultado de uma trigger, que criamos na operação INSERT na tabela COMPANY. Da mesma forma, você pode criar seus gatilhos em operações UPDATE e DELETE com base em seus requisitos.

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

Listagem de gatilhos

Você pode listar todos os gatilhos de sqlite_master tabela da seguinte forma -

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

A instrução SQLite acima listará apenas uma entrada da seguinte forma -

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

Se você quiser listar os gatilhos em uma tabela específica, use a cláusula AND com o nome da tabela da seguinte maneira -

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

A instrução SQLite acima também listará apenas uma entrada da seguinte forma -

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

Soltar gatilhos

A seguir está o comando DROP, que pode ser usado para descartar um gatilho existente.

sqlite> DROP TRIGGER trigger_name;