dasini.net – Journal d'un expert MySQL

(<- précédent)

Déclencheurs

Les déclencheurs (triggers) sont des ordres de déclenchement d’opérations quand un évènement survient sur une table.

Des déclencheurs pour maintenir la cohérence des données

Ils sont souvent utilisés pour assurer la cohérence des données dans la base, en réalisant des contraintes qui doivent porter sur plusieurs tables.

Les déclencheurs combinés aux transactions permettent de créer tous les mécanismes d’intégrité référentielle. La norme SQL 3 a d’ailleurs imposé l’utilisation des déclencheurs.

Initialement prévu pour la version 5.1, l’équipe de développement à finalement profité d’une avance sur le calendrier de développement pour proposer une version simplifié des déclencheurs dans la version 5.0.

Syntaxe de base

CREATE TRIGGER trigger_nom
[DEFINER = { user | CURRENT_USER }]
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON table name
FOR EACH ROW
triggered SQL statement

Le premier élément entrant en compte est le nom (trigger_nom). L’action qui est déclenchée l’est à la suite d’un évènement (ex : insertion d’un nouvel enregistrement dans une table). Le second paramètre (BEFORE ou AFTER) indique si le déclencheur doit être lancé avant ou après l’évènement.

Les déclencheurs peuvent être activé durant l’appel à un INSERT, un UPDATE ou un DELETE.

CREATE TRIGGER trig_livre
BEFORE INSERT

Le déclencheur est lié à une table que nous définissons avec le mot clef ON :

CREATE TRIGGER trig_livre
BEFORE INSERT
ON livre

On définit alors les instructions à effectuer une fois le déclencheur activé.

CREATE TRIGGER trig_livre
  BEFORE INSERT
  ON livre
  FOR EACH ROW
  BEGIN
     INST1;
     INST2;
  END;

Prenons un cas pratique :

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Limitation des déclencheurs liées à la version 5.0

Comme nous l’avons indiqué plus amont le support des déclencheurs est un plus dans la version 5.0 de MySQL : C’était prévu pour la version 5.1. Il n’en reste pas moins que c’est une version light qui souffre à ce jour de quelques limitations :

• On ne peut associer un déclencheur à une vue ou à une table temporaire,

• Les déclencheurs ne peuvent pas non plus faire appel à une procédure stockée ou à des fonctions.

INFORMATION_SCHEMA

La base INFORMATION_SCHEMA, standard du SQL:2003, fournit un accès aux métadonnées du serveur MySQL.

Les métadonnées sont les informations sur les données, telles que le nom des bases de données, le nom des tables, le type des données, d’index, les droits d’accès, etc.

INFORMATION_SCHEMA est une base de données virtuelle, vous ne pouvez y accéder qu’en lecture. En effet, les tables qui la composent, ne sont en fait que des vues. Vous ne verrez donc pas sur le disque dur de fichiers associés.

Par exemple, pour récupérer le code
des procédures stockées, de la base de données FRESHDAZ,  il faut
pour cela, sélectionner les colonnes ROUTINE_NAME et
ROUTINE_DEFINITION de la table  information_schema.ROUTINES:

mysql> SELECT R.ROUTINE_NAME, R.ROUTINE_DEFINITION
-> FROM information_schema.ROUTINES R
-> WHERE R.ROUTINE_TYPE = ‘PROCEDURE’ AND R.ROUTINE_SCHEMA = ‘FRESHDAZ’;

Goodies and co

Amélioration des traitements mathématiques

Avec MySQL 4.1 le résultat de la commande SELECT .01 * .01 renvoyait 0. MySQL 5 utilise une bibliothèque permettant des calculs plus précis. Ainsi 0.0001 est traité comme une valeur exacte et non plus comme une valeur approximative.

Nouveau moteur de stockage

Un nouveau moteur de stockage a été ajouté depuis MySQL 5.0.3 : FEDERATED Storage Engine. Il permet d’accéder à des données présentes sur des bases de données distantes.

Conclusion

MySQL 5 apporte un réel confort en terme de fonctionnalités par rapport à ses versions antérieures. Les déclencheurs, les vues et les procédures stockées propulsent MySQL dans le monde des SGBD matures et permet de soutenir la comparaison avec Oracle, PostgreSQL et autres.

Avec cette nouvelle version MySQL élargit son public en ne s’adressent plus principalement aux architectes d’applications Web mais à tous les développeurs.

Filed under:
MySQL by Olivier DASINI