Déclencheur (Trigger) SQLite

Les déclencheurs (Trigger) SQLite sont des fonctions de rappel de base de données, qui s'exécutent automatiquement lors de l'occurrence d'événements de base de données spécifiés/Appeler ces fonctions. Voici les points principaux concernant les déclencheurs (Trigger) SQLite-

• Les déclencheurs (Trigger) de SQLite peuvent être spécifiés pour déclencher lors de l'exécution de DELETE, INSERT ou UPDATE sur une table de base de données spécifique, ou lors de la mise à jour de colonnes dans une ou plusieurs tables spécifiées.

• SQLite ne supporte que les déclencheurs (Trigger) FOR EACH ROW, pas de déclencheurs (Trigger) FOR EACH STATEMENT. Par conséquent, l'indication explicite de FOR EACH ROW est optionnelle.

• WHEN 子句和触发器(Trigger)动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。

• 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。

• BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。

• 当触发器相关联的表删除时，自动删除触发器(Trigger)。

• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。

• 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。

语法

以下是创建trigger的基本语法。

CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name　
ON table_name
BEGIN
　--　触发器逻辑....
END;

在这里，event_name可以是对上述表的INSERT，DELETE和UPDATE数据库操作table_name。您可以选择在表名称后指定FOR EACH ROW。

以下是在表的一个或多个指定列上的UPDATE操作上创建触发器的语法。

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name　
ON table_name
BEGIN
　　　--　触发逻辑在这里....
END;

示例

考虑一种情况，我们希望对插入到COMPANY表中的每条记录进行审计试用，这是我们新创建的，如下所示（如果已经有了，则删除COMPANY表）。

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

Pour maintenir l'état d'audit, tant que des entrées de nouvelles records existent dans la table COMPANY, nous créons une nouvelle table nommée AUDIT dans laquelle nous insérons les messages de journal.

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

Ici, ID est l'ID de l'enregistrement AUDIT, EMP_ID est l'ID provenant de la table COMPANY, DATE conserve le timestamp lors de la création de l'enregistrement dans la table COMPANY. Maintenant, créons un trigger 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 par insérer un enregistrement dans la table COMPANY, ce qui entraînera la création d'un enregistrement de journal d'audit dans la table AUDIT. Créons 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 une entrée dans la table COMPANY, comme suit :-

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

En même temps, une entrée sera créée dans la table AUDIT. Il s'agit du résultat du trigger que nous avons créé sur l'opération INSERT dans la table COMPANY. De même, vous pouvez créer des triggers pour les opérations UPDATE et DELETE selon vos besoins.

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

lister les triggers

Vous pouvez obtenirsqlite_masterTous les triggers listés dans la table, comme suit :

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

L'instruction SQLite ci-dessus ne listera qu'une entrée, comme suit :-

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

Pour lister les triggers spécifiques sur une table, utilisez l'opérateur AND et le nom de la table, comme suit :

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

La requête SQLite ci-dessus ne listera qu'une entrée, comme suit-

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

Suppression de déclencheur

Voici la commande DROP, utilisée pour supprimer un déclencheur existant.

sqlite> DROP TRIGGER trigger_name;