Déclencheurs PostgreSQL

Les déclencheurs PostgreSQL sont des fonctions de rappel de base de données qui s'exécutent automatiquement lors de l'occurrence d'événements spécifiés dans la base de données./Appel

Voici quelques points importants sur les déclencheurs PostgreSQL :

• Les déclencheurs PostgreSQL peuvent être déclenchés dans les situations suivantes :

• Avant l'exécution de l'opération (avant de vérifier la contrainte et d'essayer d'insérer, de mettre à jour ou de supprimer).

• Après l'exécution de l'opération (après avoir vérifié la contrainte et terminé l'insertion, la mise à jour ou la suppression).

• Opérations de mise à jour (lors de l'insertion, de la mise à jour ou de la suppression d'une vue).

• L'attribut FOR EACH ROW du déclencheur est optionnel. Si sélectionné, le déclencheur est appelé pour chaque ligne modifiée ; sinon, si FOR EACH STATEMENT est sélectionné, le déclencheur est exécuté une seule fois pour chaque instruction marquée, peu importe le nombre de lignes modifiées.

• Lors de la clause WHEN et de l'opération du déclencheur en référençant NEW.column-name et OLD.column-name peut accéder à chaque élément de la ligne lors de l'insertion, de la suppression ou de la mise à jour.-name est le nom de la colonne de la table associée au déclencheur.

• Si une clause WHEN existe, l'instruction PostgreSQL ne s'exécutera que pour la ligne pour laquelle la clause WHEN est vérifiée, sinon l'instruction PostgreSQL s'exécutera pour chaque ligne.

• Les mots-clés BEFORE ou AFTER déterminent quand l'action du déclencheur est exécutée, c'est-à-dire avant ou après l'insertion, la modification ou la suppression de la ligne associée.

• La table à modifier doit exister dans la même base de données, en tant que table ou vue à laquelle le déclencheur est attaché, et doit utiliser uniquement tablename, plutôt que database.tablename.

• Lors de la création d'un déclencheur de contrainte, les options de contrainte sont spécifiées. Cela est similaire aux déclencheurs conventionnels, mais cette contrainte peut être utilisée pour ajuster le moment où le déclencheur est déclenché. Lorsque la contrainte implémentée par le déclencheur de contrainte est violée, elle lève une exception.

Syntaxe

La syntaxe de base pour créer un déclencheur est la suivante :

CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
　--　Logique du déclencheur....
];

Dans ce cas, event_name peut être une opération de base de données INSERT, DELETE et UPDATE sur la table table_name mentionnée. Vous pouvez choisir de spécifier FOR EACH ROW après le nom de la table.

La syntaxe suivante est utilisée pour créer un déclencheur sur une ou plusieurs colonnes spécifiées dans une table :

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
　--　Logique du déclencheur....
];

Exemple en ligne

Supposons une situation, nous devons conserver une expérience d'audit pour chaque enregistrement inséré dans la table COMPANY nouvellement créée (si elle existe déjà, elle sera supprimée et recréée) :

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

Pour conserver l'expérience d'audit, nous allons créer une nouvelle table nommée AUDIT. Chaque fois qu'il y a un nouvel élément dans la table COMPANY, un message de journal sera inséré :

w3codeboxdb=# CREATE TABLE AUDIT(
　　　EMP_ID INT NOT NULL,
　　　ENTRY_DATE TEXT NOT NULL
);

Dans ce cas, ID est l'ID de l'enregistrement AUDIT, EMP_ID est l'ID provenant de la table COMPANY, DATE conservera le timestamp de création de l'enregistrement dans COMPANY. Donc, créons un déclencheur sur la table COMPANY comme suit :

w3codeboxdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() est une programme PostgreSQL, dont la définition est la suivante :

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
　　　BEGIN
　　　　　　INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
　　　　　　RETURN NEW;
　　　END;
$example_table$ LANGUAGE plpgsql;

现在，我们开始往 COMPANY 表中插入数据：

w3codeboxdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul',　32, 'California',　20000.00 );

这时，COMPANY 表中插入了一条记录：

同时， AUDIT 表中也插入了一条记录，因为我们在插入 COMPANY 表时创建了一个触发器。相似的，我们也可以根据需求在更新和删除时创建触发器：

emp_id | entry_date
--------+-------------------------------
　　　　　　1　|　2013-05-05　15:49:59.968+05:30
(1　row)

列出触发器

你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来：

w3codeboxdb=# SELECT　*　FROM pg_trigger;

如果你想列举出特定表的触发器，语法如下：

w3codeboxdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

得到结果如下：

　tgname
-----------------
　example_trigger
(1　row)

删除触发器

删除触发器的基础语法如下：

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

删除本文上表 company 上的触发器 example_trigger 的指令为：

drop trigger example_trigger on company;