PL/SQL – Les déclencheurs

Dans ce chapitre, nous allons aborder les déclencheurs en PL/SQL. Les déclencheurs sont des programmes stockés, qui sont automatiquement exécutés ou tirés lorsque certains événements se produisent. Les déclencheurs sont, en fait, écrits pour être exécutés en réponse à l’un des événements suivants –

• Une instruction de manipulation de base de données (DML) (DELETE, INSERT, ou UPDATE)

• Une instruction de définition de base de données (DDL) (CREATE, ALTER, ou DROP).

• Une opération de base de données (SERVERERROR, LOGON, LOGOFF, STARTUP ou SHUTDOWN).

Les déclencheurs peuvent être définis sur la table, la vue, le schéma ou la base de données à laquelle l’événement est associé.

Avantages des déclencheurs

Les déclencheurs peuvent être écrits aux fins suivantes –

• Générer automatiquement certaines valeurs de colonnes dérivées
• Forcer l’intégrité référentielle
• Consignation des événements et stockage des informations sur l’accès aux tables

.

• Audit
• Réplication synchrone des tables
• Imposer des autorisations de sécurité
• Prévenir les transactions invalides

Créer des déclencheurs

La syntaxe pour créer un déclencheur est –

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} ON table_name WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; 

Où,

• CREATE TRIGGER trigger_name – Crée ou remplace un déclencheur existant avec le trigger_name.

• {DEVANT | APRÈS | INSTEAD DE} – Cela spécifie quand le déclencheur sera exécuté. La clause INSTEAD OF est utilisée pour créer un déclencheur sur une vue.

• {INSERT | UPDATE | DELETE} – Ceci spécifie l’opération DML.

• – Ceci spécifie le nom de la colonne qui sera mise à jour.

• – Ceci spécifie le nom de la table associée au déclencheur.

• – Cela vous permet de référencer les nouvelles et anciennes valeurs pour diverses instructions DML, telles que INSERT, UPDATE et DELETE.

• – Cela spécifie un déclencheur au niveau de la ligne, c’est-à-dire, le déclencheur sera exécuté pour chaque ligne affectée. Sinon, le déclencheur s’exécutera une seule fois lorsque l’instruction SQL est exécutée, ce qui est appelé un déclencheur de niveau table.

• WHEN (condition) – Cela fournit une condition pour les lignes pour lesquelles le déclencheur se déclencherait. Cette clause n’est valable que pour les déclencheurs de niveau ligne.

Exemple

Pour commencer, nous allons utiliser la table CUSTOMERS que nous avions créée et utilisée dans les chapitres précédents –

Select * from customers; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +----+----------+-----+-----------+----------+ 

Le programme suivant crée un déclencheur de niveau ligne pour la table customers qui se déclencherait pour les opérations INSERT ou UPDATE ou DELETE effectuées sur la table CUSTOMERS. Ce déclencheur affichera la différence de salaire entre les anciennes valeurs et les nouvelles valeurs –

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff); END; / 

Lorsque le code ci-dessus est exécuté à l’invite SQL, il produit le résultat suivant –

Trigger created.

Les points suivants doivent être considérés ici –

• Les référencesOLD et NEW ne sont pas disponibles pour les déclencheurs de niveau table, vous pouvez plutôt les utiliser pour les déclencheurs de niveau enregistrement.

• Si vous voulez interroger la table dans le même déclencheur, alors vous devriez utiliser le mot-clé AFTER, parce que les déclencheurs peuvent interroger la table ou la modifier à nouveau seulement après que les changements initiaux soient appliqués et que la table soit de nouveau dans un état cohérent.

• Le déclencheur ci-dessus a été écrit de telle manière qu’il se déclenchera avant toute opération DELETE ou INSERT ou UPDATE sur la table, mais vous pouvez écrire votre déclencheur sur une seule ou plusieurs opérations, par exemple BEFORE DELETE, qui se déclenchera chaque fois qu’un enregistrement sera supprimé à l’aide de l’opération DELETE sur la table.

Déclenchement d’un déclencheur

Laissons nous effectuer quelques opérations DML sur la table CUSTOMERS. Voici une instruction INSERT, qui va créer un nouvel enregistrement dans la table –

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 ); 

Lorsqu’un enregistrement est créé dans la table CUSTOMERS, le déclencheur create ci-dessus, display_salary_changes sera déclenché et il affichera le résultat suivant –

Old salary: New salary: 7500 Salary difference:

Parce qu’il s’agit d’un nouvel enregistrement, l’ancien salaire n’est pas disponible et le résultat ci-dessus se présente comme nul. Effectuons maintenant une autre opération DML sur la table CUSTOMERS. L’instruction UPDATE va mettre à jour un enregistrement existant dans la table –

UPDATE customers SET salary = salary + 500 WHERE id = 2; 

Lorsqu’un enregistrement est mis à jour dans la table CUSTOMERS, le déclencheur create ci-dessus, display_salary_changes, sera déclenché et il affichera le résultat suivant –

Old salary: 1500 New salary: 2000 Salary difference: 500 

.