Comment créer un déclencheur et une fonction MySQL en se connectant à un compte avec une clé SSH

Sommaire

Introduction

Il est parfois nécessaire de créer un déclencheur (trigger en anglais), une procédure (procedure en anglais) et une fonction (function en anglais) sur N0C en se connectant en SSH et en les codant en MySQL.

Note : n’utilisez pas phpMyAdmin. Les déclencheurs et les functions sont toujours liés à l’utilisateur. Dans phpMyAdmin, l’utilisateur est temporaire : dès lors, à partir du moment où il est automatiquement supprimé, les déclencheurs et les fonctions le sont aussi.

Note : Le glossaire contient des explications sur de multiples sujets et peut être consulté pour clarifier certains termes.

Références

Cet article a été écrit en se basant sur le Manuel de référence MySQL.

Prérequis

Saisissez dans votre navigateur web l’adresse suivante : https://mg.n0c.com/fr/.

Définitions

On appelle trigger (déclencheur) un mécanisme qui amène l’exécution automatique d’une procédure dans une table lorsqu’un événement particulier survient pour cette table, par exemple lorsqu’un utilisateur s’apprête à modifier le contenu d’une base de données. Nous utiliserons dans l’article le terme trigger, le terme déclencheur n’étant pas très répandu dans le jargon informatique.

Une procédure permet d’exécuter un ensemble d’instructions SQL.

On appelle fonction (function en anglais) un sous-programme constitué d’un ensemble d’instructions identifiées par un nom, dont l’exécution produit une valeur qui renvoie à l’endroit où le sous-programme a été appelé.

Comment se connecter en SSH et en MySQL

Connectez-vous à votre compte avec une clé SSH (comment).
Assurez-vous d’avoir un utilisateur MySQL en le récupérant en tant qu’utilisateur principal ou en créant un utilisateur qui a les privilèges sur la base de données (comment).
Connectez-vous en MySQL avec la commande ci-dessous :

mysql -u utilisateur nomdeladb -p

Où :

utilisateur doit être remplacé par votre nom d’utilisateur principal ou un utilisateur MySQL; et
nomdeladb doit être remplacé par le nom de la base de données.

Saisissez votre mot de passe.
Confirmez la connexion.

Marche à suivre pour créer un trigger

Nous expliquons ici comment on peut créer un trigger en MySQL en le codant à l’aide de l’instruction CREATE TRIGGER.

Connectez-vous en SSH et en MySQL comme expliqué précédemment (voir Comment se connecter en SSH et en MySQL ).
Dans l’interface en ligne de commande, écrivez votre code en respectant cette syntaxe :

CREATE TRIGGER trigger_name

{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }

ON tbl_name FOR EACH ROW

trigger_body;

Dans cette syntaxe :

Fournissez après CREATE TRIGGER le nom du trigger (trigger_name dans l’exemple) que vous désirez créer;
Spécifiez après BEFORE ou AFTER le temps d’action du trigger (trigger_time), qui indique si celui-ci est exécuté avant ou après que la rangée soit modifiée;
Spécifiez le type d’opération qui active le trigger (trigger_event). Les valeurs permises sont :
- INSERT : le trigger s’active dès qu’une nouvelle rangée est ajoutée à la table;
- UPDATE : le trigger s’active dès qu’une rangée est modifiée dans la table;
- DELETE : le trigger s’active dès qu’une rangée est effacée de la table.

Spécifiez après ON le nom de la table à laquelle le trigger appartient (tbl_name);
trigger_body est l’instruction à effectuer quand le trigger est activé.

L’opération a fonctionné si vous voyez le message « Query OK, 0 rows affected (0.xyz sec) » (0.xyz étant le nombre de secondes requises pour l’exécution du trigger).

Note : on remarquera que la commande DEFINER n’apparaît pas dans le code car, dans le cas qui nous occupe ici, c’est l’utilisateur qui, par défaut, exécute la déclaration CREATE TRIGGER.

Mots clés OLD et NEW

Il est possible d’utiliser les mots clés OLD et NEW, qui réfèrent respectivement à la valeur avant le trigger et à la nouvelle valeur.

Le trigger peut accéder aux valeurs de la colonne concernée par la déclaration. Les modificateurs OLD et NEW sont utilisés pour distinguer la valeur des colonnes AVANT et APRÈS le déclenchement de l’opération.

Par exemple, si vous mettez à jour la description de la colonne dans le trigger_body, vous pouvez accéder à la valeur de la description avant la mise à jour (OLD.description) et à la nouvelle valeur (NEW.description).

Le tableau suivant illustre la disponibilité de ces mots-clés :

Type d’opération (trigger_event)	OLD	NEW
INSERT	Non	Oui
UPDATE	Oui	Oui
DELETE	Oui	Non

Exemple

Input :

CREATE TRIGGER log_users AFTER UPDATE ON users FOR EACH ROW INSERT INTO logs(user_id, action) VALUES(NEW.id, 'action name’);

CREATE TRIGGER nom_du_trigger

   AFTER UPDATE ON nom_de_la_table FOR EACH ROW

   requete_sql_a_lancer;

Output :

> CREATE TRIGGER log_users AFTER UPDATE ON users FOR EACH ROW INSERT INTO logs(user_id, action) VALUES(NEW.id, 'action name');

Query OK, 0 rows affected (0.020 sec)

**Marche à suivre pour créer une procédure (procedure)**

Nous expliquons ici comment on peut créer une procédure en MySQL en le codant à l’aide de l’instruction CREATE PROCEDURE.

Connectez-vous en SSH et en MySQL comme expliqué précédemment (voir Comment se connecter en SSH et en MySQL ).
Dans l’interface en ligne de commande, écrivez votre code en respectant cette syntaxe :

DELIMITER //


CREATE PROCEDURE ProcedureName(
     IN varName VARCHAR(255)
)


BEGIN

     SELECT *
     FROM table1
     WHERE champ1 = varName;

END //


DELIMITER ;

Dans cette syntaxe :

Redéfinissez temporairement la délimitation en tapant DELIMETER //¹;
Fournissez après CREATE PROCEDURE le nom de la procédure (ProcedureName)que vous désirez créer;
Énoncez avant VARCHAR les chaînes de caractères de la procédure et spécifiez entre parenthèses le nombre d’octets (dans notre exemple, varName est la chaîne de caractères et 255, le nombre d’octets);
Écrivez BEGIN pour marquer le début du programme de la procédure;
Dans notre exemple, la procédure consiste à récupérer des lignes sélectionnées dans une table (nommée ici table1) au moyen de la commande SELECT * et dans laquelle le champ champ 1 est égal à la chaîne de caractères varName;
Écrivez END // pour marquer la fin de l’exécution de la procédure;
Revenez à la délimitation par défaut (;) en saisissant DELIMITER² ;.

¹ Une procédure stockée consiste en plusieurs déclarations séparées par un point-virgule (;). Si vous utilisez un programme client MySQL pour définir une procédure stockée qui contient des points-virgules, le programme MySQL ne traitera pas l’ensemble de la procédure stockée comme une seule déclaration, mais plusieurs déclarations.

² Cela permet de séparer les déclarations et d’exécuter chaque déclaration séparément.

**Marche à suivre pour créer une fonction (function)**

Nous expliquons ici comment on peut créer une fonction en MySQL en le codant à l’aide de l’instruction CREATE FUNCTION.

Connectez-vous en SSH et en MySQL comme expliqué précédemment (voir Comment se connecter en SSH et en MySQL ).
Dans l’interface en ligne de commande, écrivez votre code en respectant cette syntaxe :

DELIMITER $$
 

 CREATE FUNCTION FunctionName(
     param1 INT,
     param2 VARCHAR(255))
 RETURNS BOOLEAN
 

 BEGIN
  -- statements
  return true;
 END $$
 

 DELIMITER ;

Dans cette syntaxe :

Redéfinissez temporairement la délimitation en tapant DELIMETER $$;
Fournissez après CREATE FUNCTION le nom de la fonction (FunctionName)que vous désirez créer;
Énoncez avant INT les entiers (dans notre exemple, param1 est un entier);
Énoncez avant VARCHAR les chaînes de caractères et spécifiez entre parenthèses le nombre d’octets (dans notre exemple, param2 est la chaîne de caractères et 255, le nombre d’octets);
Écrivez RETURNS BOOLEAN de sorte que la fonction retourne une valeur vraie ou fausse;
Écrivez BEGIN pour marquer le début du programme de la fonction;
Écrivez les déclarations de votre fonction (– – statements dans notre exemple);
Écrivez return pour terminer l’exécution de la fonction et retourner une valeur (la valeur est “true” dans l’exemple);
Écrivez END $$ pour marquer la fin de l’exécution de la fonction;
Revenez à la délimitation par défaut (;) en saisissant DELIMITER ;.

Updated on février 14, 2024