SQLite - Déclencheurs

SQLite Triggerssont des fonctions de rappel de base de données, qui sont automatiquement exécutées / appelées lorsqu'un événement de base de données spécifié se produit. Voici les points importants sur les déclencheurs SQLite -

  • Le déclencheur SQLite peut être spécifié pour se déclencher chaque fois qu'un DELETE, INSERT ou UPDATE d'une table de base de données particulière se produit ou chaque fois qu'un UPDATE se produit sur une ou plusieurs colonnes spécifiées d'une table.

  • Pour le moment, SQLite prend en charge uniquement les déclencheurs FOR EACH ROW, et non les déclencheurs FOR EACH STATEMENT. Par conséquent, spécifier explicitement POUR CHAQUE RANG est facultatif.

  • La clause WHEN et les actions de déclenchement peuvent accéder aux éléments de la ligne en cours d'insertion, de suppression ou de mise à jour à l'aide des références du formulaire NEW.column-name et OLD.column-name, où nom-colonne est le nom d'une colonne de la table à laquelle le déclencheur est associé.

  • Si une clause WHEN est fournie, les instructions SQL spécifiées ne sont exécutées que pour les lignes pour lesquelles la clause WHEN est vraie. Si aucune clause WHEN n'est fournie, les instructions SQL sont exécutées pour toutes les lignes.

  • Le mot clé BEFORE ou AFTER détermine quand les actions de déclenchement seront exécutées par rapport à l'insertion, la modification ou la suppression de la ligne associée.

  • Les déclencheurs sont automatiquement supprimés lorsque la table à laquelle ils sont associés est supprimée.

  • La table à modifier doit exister dans la même base de données que la table ou la vue à laquelle le déclencheur est attaché et il faut simplement utiliser tablename ne pas database.tablename.

  • Une fonction SQL spéciale RAISE () peut être utilisée dans un programme déclencheur pour lever une exception.

Syntaxe

Voici la syntaxe de base de la création d'un trigger.

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

Ici, event_namepeut être une opération de base de données INSERT, DELETE et UPDATE sur la table mentionnéetable_name. Vous pouvez éventuellement spécifier FOR EACH ROW après le nom de la table.

Voici la syntaxe pour créer un déclencheur sur une opération UPDATE sur une ou plusieurs colonnes spécifiées d'une table.

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

Exemple

Prenons un cas où nous voulons garder un essai d'audit pour chaque enregistrement inséré dans la table COMPANY, que nous créons nouvellement comme suit (Supprimez la table COMPANY si vous l'avez déjà).

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

Pour garder un essai d'audit, nous allons créer une nouvelle table appelée AUDIT où les messages du journal seront insérés chaque fois qu'il y a une entrée dans la table COMPANY pour un nouvel enregistrement.

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

Ici, ID est l'ID d'enregistrement AUDIT, et EMP_ID est l'ID qui proviendra de la table COMPANY et DATE conservera l'horodatage lorsque l'enregistrement sera créé dans la table COMPANY. Créons maintenant un déclencheur sur la table COMPANY comme suit -

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

Maintenant, nous allons commencer le travail réel, commençons à insérer un enregistrement dans la table COMPANY qui devrait aboutir à la création d'un enregistrement de journal d'audit dans la table AUDIT. Créez un enregistrement dans la table COMPANY comme suit -

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

Cela créera un enregistrement dans la table COMPANY, qui est comme suit -

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

En même temps, un enregistrement sera créé dans la table AUDIT. Cet enregistrement est le résultat d'un déclencheur, que nous avons créé lors de l'opération INSERT dans la table COMPANY. De même, vous pouvez créer vos déclencheurs sur les opérations UPDATE et DELETE en fonction de vos besoins.

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

Liste des déclencheurs

Vous pouvez lister tous les déclencheurs de sqlite_master tableau comme suit -

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

L'instruction SQLite ci-dessus répertorie une seule entrée comme suit -

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

Si vous souhaitez répertorier les déclencheurs sur une table particulière, utilisez la clause AND avec le nom de la table comme suit -

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

L'instruction SQLite ci-dessus répertorie également une seule entrée comme suit -

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

Suppression de déclencheurs

Voici la commande DROP, qui peut être utilisée pour supprimer un déclencheur existant.

sqlite> DROP TRIGGER trigger_name;