SQLite - Trigger

SQLite Triggerssono funzioni di callback del database, che vengono eseguite / invocate automaticamente quando si verifica un evento di database specificato. Di seguito sono riportati i punti importanti sui trigger di SQLite:

  • È possibile specificare che il trigger SQLite si attivi ogni volta che si verifica un DELETE, INSERT o UPDATE di una particolare tabella di database o ogni volta che si verifica un UPDATE su una o più colonne specificate di una tabella.

  • Al momento, SQLite supporta solo trigger FOR EACH ROW, non trigger FOR EACH STATEMENT. Quindi, specificare esplicitamente FOR EACH ROW è facoltativo.

  • Sia la clausola WHEN che le azioni trigger possono accedere agli elementi della riga da inserire, eliminare o aggiornare utilizzando i riferimenti del modulo NEW.column-name e OLD.column-name, dove nome-colonna è il nome di una colonna della tabella a cui è associato il trigger.

  • Se viene fornita una clausola WHEN, le istruzioni SQL specificate vengono eseguite solo per le righe per le quali la clausola WHEN è vera. Se non viene fornita alcuna clausola WHEN, le istruzioni SQL vengono eseguite per tutte le righe.

  • La parola chiave BEFORE o AFTER determina quando verranno eseguite le azioni trigger relative all'inserimento, alla modifica o alla rimozione della riga associata.

  • I trigger vengono eliminati automaticamente quando viene eliminata la tabella a cui sono associati.

  • La tabella da modificare deve esistere nello stesso database della tabella o della vista a cui è collegato il trigger e si deve utilizzare solo tablename non database.tablename.

  • Una speciale funzione SQL RAISE () può essere utilizzata all'interno di un programma trigger per sollevare un'eccezione.

Sintassi

Di seguito è riportata la sintassi di base per la creazione di un file trigger.

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

Qui, event_namepotrebbe essere un'operazione di database INSERT, DELETE e UPDATE sulla tabella menzionatatable_name. Facoltativamente, è possibile specificare FOR EACH ROW dopo il nome della tabella.

Di seguito è riportata la sintassi per la creazione di un trigger su un'operazione UPDATE su una o più colonne specificate di una tabella.

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

Esempio

Consideriamo un caso in cui vogliamo mantenere la prova di audit per ogni record inserito nella tabella AZIENDA, che creiamo di recente come segue (Elimina tabella AZIENDA se già ce l'hai).

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

Per mantenere l'audit di prova, creeremo una nuova tabella chiamata AUDIT in cui verranno inseriti i messaggi di log, ogni volta che c'è una voce nella tabella COMPANY per un nuovo record.

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

Qui, ID è l'ID del record AUDIT e EMP_ID è l'ID che verrà dalla tabella COMPANY e DATE manterrà il timestamp quando il record verrà creato nella tabella COMPANY. Ora creiamo un trigger sulla tabella AZIENDA come segue:

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

Ora, inizieremo il lavoro effettivo, iniziamo a inserire il record nella tabella AZIENDA che dovrebbe comportare la creazione di un record del registro di controllo nella tabella AUDIT. Crea un record nella tabella AZIENDA come segue:

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

Questo creerà un record nella tabella AZIENDA, che è il seguente:

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

Allo stesso tempo, verrà creato un record nella tabella AUDIT. Questo record è il risultato di un trigger, che abbiamo creato durante l'operazione INSERT nella tabella COMPANY. Allo stesso modo, puoi creare i tuoi trigger nelle operazioni UPDATE e DELETE in base alle tue esigenze.

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

Trigger dell'elenco

Puoi elencare tutti i trigger da sqlite_master tabella come segue -

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

L'istruzione SQLite sopra elencherà solo una voce come segue:

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

Se vuoi elencare i trigger su una particolare tabella, usa la clausola AND con il nome della tabella come segue:

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

L'istruzione SQLite sopra elencherà anche solo una voce come segue:

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

Trigger in caduta

Di seguito è riportato il comando DROP, che può essere utilizzato per rilasciare un trigger esistente.

sqlite> DROP TRIGGER trigger_name;