À quoi ça sert ???

Est-il possible de contourner le problème par un code plutôt que par une transaction ? Hélas non, car à chaque fois que nous testerons nous pouvons avoir un autre ordre SQL qui s'intercale. Par exemple l'idée de renverser la vapeur si nous avons généré des places négatives est bien tentante…

T1 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 5
   then
   begin
 
 
 

T3    UPDATE T_VOL
      SET        VOL_PLACES_LIBRES = 
        VOL_PLACES_LIBRES - 5
      WHERE  VOL_ID = 2
 
 
 

T5 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 

T7    then
      begin
         UPDATE T_VOL
         SET    VOL_PLACES_LIBRES = 
             VOL_PLACES_LIBRES + 5
         WHERE  VOL_ID = 2 
      return
      end 
 
 
 
 
 
 

T9    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
   end

T2 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) > 3
   then
   begin
 
 

T4    UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = 
              VOL_PLACES_LIBRES - 3
      WHERE  VOL_ID = 2
 
 

T6 if (SELECT VOL_PLACES_LIBRES
          FROM T_VOL WHERE VOL_ID = 2) < 0
 
 
 
 
 
 

T8 then
     begin
        UPDATE T_VOL
        SET        VOL_PLACES_LIBRES = 
            VOL_PLACES_LIBRES + 3
        WHERE  VOL_ID = 2 
       return
     end 
 

T10    INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
   end

Hélas exécutée dans un contexte de concurrence elle devient tout aussi inacceptable !
Voici le contenu des tables pour les temps T1 à T10 pour le vol 2 et les clients 77 et 88 :

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         1

2       AF 812         -2

2       AF 812         -2

2       AF 812         -2

2       AF 812         3

2       AF 812         6

Nous n'avons toujours pas résolu notre problème. Car aucun client n'a été servi ! C'est encore pire, vous allez entraîner la faillite de votre compagnie en continuant comme ça !

En fait, seule une transaction peut permettre de nous sauver de l'embarras.

Alors certains diront, « mais il suffit de poser un verrou sur la table … ou la ligne… ».
D'accord, sauf que cela n'existe pas en SQL et que les verrous sont des mécanismes internes en principe inaccessibles. Le verrou est en fait l'un des moyens d'assurer la concurrence entre les utilisateurs, mais ses effets dans le cadre d'une utilisation directe peuvent être plus pervers que l'utilisation des transactions.
En effet qui n'a jamais entendu parler du « verrou mortel », « dealock » en anglais, aussi appelé étreinte fatale.

L'utilisation de verrous, même si elle est permise par le SGBDR est à proscrire totalement sans une maîtrise parfaite des phénomènes de concurrence qui suppose d'avoir fait un diagramme du parallélisme des tâches afin de débusquer les points de concurrence sujets à blocage. Cela c'était l'informatique de grand-papa à l'aide de fichiers que l'on ouvrait en mode exclusif pour effectuer ses modifications en COBOL…
Encore faut-il savoir quels types de verrous sont disponibles sur le SGBDR : optimistes, pessimistes, exclusifs, partagés, de table, de page, de ligne de colonne ??????????

En revanche, voici comment ce problème est résolu par une gestion de transaction :

BEGIN TRANSACTION PLACE_AVION

UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
WHERE  VOL_ID = 2

INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)

if (SELECT VOL_PLACES_LIBRES
    FROM T_VOL WHERE VOL_ID = 2) < 0
then
   ROLLBACK TRANSACTION PLACE_AVION
else
   COMMIT TRANSACTION PLACE_AVION

Étudions les effets de ce code en concurrence :

T1 BEGIN TRANSACTION PLACE_AVION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 5
   WHERE  VOL_ID = 2
T4 INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T5 if (SELECT VOL_PLACES_LIBRES
       FROM T_VOL WHERE VOL_ID = 2) < 0
T6 then
       ROLLBACK TRANSACTION PLACE_AVION 1
T7 else
       COMMIT TRANSACTION PLACE_AVION 1

T2 BEGIN TRANSACTION PLACE_AVION 2

-- la transaction attend la libération
-- des ressources de l'autre transaction
 
 
 
 
 
 
 
 

T8 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 2
T9 INSERT INTO T_CLIENT_VOL VALUES (88, 2, 3)
T10 if (SELECT VOL_PLACES_LIBRES
        FROM T_VOL WHERE VOL_ID = 2) < 0
T11 then
      ROLLBACK TRANSACTION PLACE_AVION 2
T12 else
      COMMIT TRANSACTION PLACE_AVION 2

Comme nous allons le voir, tout a bien fonctionné :

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         1

2       AF 812         1

77      2       5

2       AF 812         1

77      2       5

2       AF 812         1

77      2       5

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         -2

77      2       5
88      2       3

2       AF 812         1

77      2       5

T6 n'est pas exécuté comme T12, l'utilisateur 1 voit sa transaction validée et le 2 la voit annulée. C'est-à-dire que le contenu des tables revient aux mêmes données qu'il y avait au début de la transaction T2.

Nous venons de voir comment démarrer une transaction et comment la valider ou l'annuler. Il faut toujours qu'une transaction possède un ordre COMMIT ou ROLLBACK, sans quoi elle perdure jusqu'à plus soif… en principe jusqu'à ce que l'utilisateur se déconnecte.

ATTENTION : la norme prévoit que toute connexion à un SGBDR entame une transaction. C'est vrai sur certains serveurs, faux sur d'autres comme SQL Server qui travaillent en AUTOCOMMIT, c'est-à-dire que chaque ordre SQL constitue une transaction en soi, immédiatement autovalidée ou autoannulée. Dans ce cas, il faut obligatoirement commencer une transaction par l'ordre SQL BEGIN TRANSACTION.

BEGIN TRANSACTION [nom_transaction]
...
ROLLBACK | COMMIT TRANSACTION [nom_transaction]
...
COMMIT | ROLLBACK TRANSACTION [nom_transaction]

...
COMMIT | ROLLBACK
...
ROLLBACK | COMMIT

Parlons maintenant du niveau d'isolation, la fameuse « perméabilité » dont je vous ai parlé au début. C'est un concept un peu difficile à retenir, car il doit s'apprécier uniquement en pensant à deux transactions s'exécutant en concurrence. N'oublions pas que le SGBDR compte tenu des volumes de données à traiter doit pouvoir servir tout un chacun avec la même chance d'accès au serveur et donc les processus sont parallélisés.
Nous pourrions comparer l'isolation d'une transaction à la perméabilité d'un tuyau. Par exemple deux robinets (l'un de vin rouge, l'autre de blanc) situés côte à côte peuvent être dotés en sortie :

• d'aucun tuyau (d'où des éclaboussures et le mélange des liquides pour faire du rosé !) ;
• d'un tuyau en tissu qui s'humidifie, et peut polluer l'autre liquide s’il entre en contact ;
• d'un tuyau en verre qui nous permet de voir les liquides couler et donc d'ajuster le débit ;
• ou enfin d'un tuyau en métal opaque ne permettant ni vision ni fuite…

Le pilotage du niveau d'isolation est assuré par l'ordre SQL :

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITED | READ COMMITED | REPEATABLE READ | SERIALIZABLE}

Ou les anomalies « transactionnelles »…

Pour comprendre l'utilité de ces différents niveaux, nous allons nous intéresser aux trois types d'anomalies possibles qui peuvent survenir lors de l'exécution concurrente d'ordres SQL.

• L'anomalie la plus grave est la lecture impropre (lecture sale ou dirty read) : elle se produit lorsqu'une transaction lit des données qui n'ont pas encore été validées.
• Suit, l'anomalie de lecture non répétable (non repeatable read) : deux lectures successives des mêmes données ne produisent pas le même résultat dans la même ligne.
• Enfin la lecture fantôme (phantom read) est une anomalie qui se produit lorsque des données nouvelles apparaissent ou disparaissent dans des lectures successives.

Voici un exemple de lecture impropre : l'utilisateur 1 ajoute 10 places et annule sa transaction, tandis que l'utilisateur 2 veut 7 places si elles sont disponibles…

T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 10
   WHERE  VOL_ID = 2
 
 
 

T5 ROLLBACK TRANSACTION 1

T2 BEGIN TRANSACTION 2
 
 
 

T4 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 7
   then

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 7
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2, 5)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         16

2       AF 812         16

2       AF 812         6

2       AF 812         -1

2       AF 812         -1

77      2       5

2       AF 812         -1

77      2       5

Le temps d'un update avorté, la transaction 2 a lu des informations qu'elle n'aurait jamais dû voir et en a tiré la conclusion qu'elle pouvait servir les places… Conclusion surbooking !

Voici maintenant un exemple de lecture non répétable : nous allons considérer le cas ou notre opérateur désire toutes les places d'un vol s’il y en a plus de 4…

T3 BEGIN TRANSACTION 1
T4 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 4
   WHERE  VOL_ID = 2
T5 COMMIT TRANSACTION 1

T1 BEGIN TRANSACTION 2
T2 if (SELECT VOL_PLACES_LIBRES 
       FROM T_VOL WHERE VOL_ID = 2) >= 4
   then
 
 
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES -
             (SELECT VOL_PLACES_LIBRES
              FROM T_VOL WHERE VOL_ID = 2) 
      WHERE  VOL_ID = 2
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 2,
              (SELECT VOL_PLACES_LIBRES
               FROM T_VOL WHERE VOL_ID = 2))
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

2       AF 812         6

2       AF 812         6

2       AF 812         16

2       AF 812         2

2       AF 812         2

2       AF 812         0

2       AF 812         0

77      2       2

2       AF 812         0

77      2       2

Notre utilisateur 2 voulait au moins 4 places et en a reçu 2… Conclusion, vous avez perdu un client !

Dernier cas, la lecture fantôme : notre utilisateur 2, désire n'importe quel vol pas cher pour emmener son équipe de foot (soit 11 personnes) à une destination quelconque.

T3 BEGIN TRANSACTION 1
T4 INSERT INTO T_VOL
   VALUES (5, 'AF 111', 125)
T5 COMMIT TRANSACTION 1

T1 BEGIN TRANSACTION 2
T2 if EXISTS (SELECT *
              FROM T_VOL
       WHERE VOL_PLACE_LIBRE >= 11)   then
 
 
 

T6 begin
      UPDATE T_VOL
      SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 11
      WHERE  VOL_PLACES_LIBRES >= 11
T7    INSERT INTO T_CLIENT_VOL VALUES (77, 4, 11)
T8    COMMIT TRANSACTION
   end
T9 else 
       ROLLBACK TRANSACTION 2

Et les données qui sont manipulées :

T_VOL :

VOL_ID  VOL_REFERENCE  VOL_PLACES_LIBRES
------- -------------- -----------------

T_CLIENT_VOL :

CLI_ID  VOL_ID  VOL_PLACES_PRISES
------- ------- -----------------

4       AF 325         258

4       AF 325         258

4       AF 325         258

4       AF 325         258
5       AF 111         125

4       AF 325         258
5       AF 111         125

4       AF 325         247
5       AF 111         114

4    AF 325         247
5       AF 111         114

77      4       11

4       AF 325         247
5       AF 111         114

77      2       11

(1)11 places ont été volatilisées du vol AF 111 et c'est comme cela qu'un certain été, des avions d'Air France volaient à vide avec toutes les places réservées !!!

C'est pour se préserver de telles anomalies que la norme SQL 2 a mis en place le niveau d'isolation des transactions.

Le tableau ci-dessous résume les différents niveaux d'isolations praticables et les anomalies qu'elles doivent impérativement éviter :

En principe, la norme fixe le niveau d'isolation par défaut du SGBDR à SERIALIZABLE !

En pratique, c'est rarement le cas… Par exemple SQL Server de Microsoft fonctionne par défaut, au niveau d'isolation 1 (READ COMMITED) ce qui explique sa relative grande rapidité, mais de possibles anomalies transactionnelles si l'on n’y prend pas garde.

Mais me direz, vous… Pourquoi accepter de descendre en dessous du niveau SERIALIZABLE ?
Simplement parce que ce niveau – le plus contraignant – entraîne une pénalisation certaine du serveur en termes de performance ! Or, nous n'avons pas toujours besoin de ce niveau d'extrême isolation… La norme SQL laisse le choix de piloter les transactions comme le développeur le veut, à condition qu'il ait ce choix et qu'il en mesure toutes les conséquences… En cette matière hélas, nombre de développeurs laissent faire le server sans se poser les bonnes questions….

Il est conseillé de se situer au moins au niveau correspondant aux utilisations suivantes :

• UPDATE avec mise à jour de clé (primaire ou étrangères), INSERT : SERIALIZABLE
• UPDATE sur valeurs courantes, DELETE filtrés sur clés : REPEATABLE READ
• SELECT intègres, DELETE sans filtre : READ COMMITED
• SELECT non intègres : READ UNCOMMITED

Vous avez compris que dès que des mises à jour interviennent en concurrence il peut survenir des anomalies transactionnelles, ce qui, dans ce cas, fait perdre à la base l'intégrité de ses données…
Il est donc impossible de se passer de la logique transactionnelle, sauf à définir un seul utilisateur à même d'effectuer toutes les mises à jour (INSERT, UPDATE, DELETE) ce qui cantonne les bases de données qui en sont dépourvues à des utilisations du genre « bases de données documentaires » où l'essentiel de l'activité du serveur constitue de la lecture de données.

OUI, mais… on peut gérer des transactions soit dans des procédures stockées au sein du serveur, soit dans du code client… Qu'est-ce qui est préférable ?

L'idée de manipuler des transactions depuis un code client (VB, Delphi, Java, C++…) est séduisante, mais « casse-gueule » et peut entraîner le pire du pire : un blocage total du serveur. En effet dès que l'on entame une transaction, le SGBDR pose les verrous adéquats sur les ressources visées par la procédure. Si le client perd la main sur son code et ne provoque jamais de COMMIT ou ROLLBACK, les ressources ne sont pas libérées et entraînent l'impossibilité pour les autres utilisateurs d'accéder aux données verrouillées. C'est pourquoi une logique transactionnelle doit toujours être exécutée au plus près du serveur et non sur le poste client, à moins que vous ayez prévu l'artillerie lourde : poste sur onduleur on line ou réseau électrique sur alimentation secourue, OS hyper stable, antivirus, etc.
De plus, il convient que la procédure ne soit jamais en attente d'une manipulation de l'utilisateur (comme une demande de saisie ou de confirmation), car toute attente bloque les ressources un certain temps et met en attente d'autres utilisateurs. C'est alors le château de cartes, chaque utilisateur attend qu'un autre libère les ressources et cela peut entraîner le blocage total du SGBDR, par exemple un verrou mortel…

C'est pourquoi on veillera à placer les transactions, soit dans une procédure stockée (l'idéal en termes de sécurité et d'intégrité) soit dans des objets métier appelés par un serveur d'application ou d'objet aussi bien sécurisé que le serveur SGBDR et dans un réseau connectiquement proche.

Ce phénomène se produit lorsque deux (ou plus) utilisateurs veulent accéder aux mêmes ressources, mais dans une séquence de temps inverse. Nous avons dit que le SGBDR posait des verrous adéquats sur les ressources par rapport aux objets concernés par les transactions. Le type de verrou dépend d'ailleurs de l'ordre SQL exécuté et du niveau d'isolation demandé. Certains traitements nécessitent la pose simultanée de plusieurs verrous sur des tables différentes. Le verrou mortel s'appelle dans la littérature informatique « dead lock » en anglais, et encore étreinte fatale ou interblocage en français.
Regardons ce qui se passe dans deux transactions sujettes à un tel verrou mortel…

L'utilisateur 1 veut prendre 3 places d'avion sur le vol AF 714, tandis que l'utilisateur 2, qui vient d'être hospitalisé veut restituer 5 places sur ce même vol…

T1 BEGIN TRANSACTION 1

T3 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
   WHERE  VOL_ID = 1
 
 

T5 INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)
 
 

T7 COMMIT

T2 BEGIN TRANSACTION 2
 
 

T4 DELETE FROM T_CLIENT_VOL
   WHERE CLI_ID = 88
     AND VOL_ID = 1

T6 UPDATE T_VOL
   SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1

T8 COMMIT

Voyons ce qui se passe dans le détail, en matière de verrous, lors du déroulement concurrent de ces deux transactions :

BEGIN TRANSACTION 1

La transaction T1 démarre normalement

BEGIN TRANSACTION 2

La transaction T2 démarre normalement

UPDATE T_VOL
SET    VOL_PLACES_LIBRES = VOL_PLACES_LIBRES - 3
WHERE  VOL_ID = 1

AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_VOL pour l'utilisateur 1

DELETE FROM T_CLIENT_VOL
WHERE CLI_ID = 88
  AND VOL_ID = 1

AVANT d'exécuter la requête, le SGBDR pose
un verrou sur la table T_CLIENT_VOL pour 
l'utilisateur 2

INSERT INTO T_CLIENT_VOL VALUES (77, 1, 3)

Le SGBDR tente d'obtenir un verrou sur la table
T_CLIENT_VOL pour l'utilisateur T1,  mais il lui
est momentanément refusé parce que la table est
verrouillée par T2

UPDATE T_VOL
SET VOL_PLACES_LIBRES = VOL_PLACES_LIBRES + 5
   WHERE  VOL_ID = 1

Le SGBDR tente d'obtenir un verrou sur la table
T_VOL pour l'utilisateur T2,  mais il lui
est momentanément refusé parce que la table est
verrouillée par T1

À ce stade les deux transactions sont en attente de libération de ressources. Aucune ne va se terminer, car chacune d'elle attend que l'autre libère la ressource dont elle à besoin. C'est l'interblocage, l'étreinte fatale, le verrou mortel ou deadlock.

Oui et non. Cela dépend du SGBDR et du niveau d'isolation. Certains SGBDR comme Oracle ou InterBase sont dotés d'un algorithme qui empêche tout interblocage. Cet algorithme peut être un « time out », qui tue momentanément un processus au hasard lorsque plusieurs processus sont bloqués. Bien entendu, le niveau d'isolation joue aussi beaucoup sur ce phénomène. Un niveau sérializable indique au serveur de placer les transactions en série (ou de faire comme ci). Cela peut garantir qu'il n'y ait pas d'interblocage, mais pénalise le serveur puisqu'il devient impossible de paralléliser les processus. Une autre astuce est de toujours manipuler les tables, dans vos procédures stockées, comme dans votre code client, dans le même ordre (par exemple l'ordre alphabétique du nom de table)…
Notez que certains GSBDR, comme MS SQL Server sont assez sujets à l'interblocage et le seul remède est en général de « tuer » un utilisateur (enfin, sa connexion !).

Le mécanisme d'intégrité référentielle doit permettre d'assurer :

• que tout client référencé par une autre table ne soit pas supprimé, ou alors 
• que l'on supprime aussi toutes les lignes filles des tables qui référencent le client supprimé ;
• que la référence du client, si elle est modifiée, soit répercutée dans toutes les lignes des tables filles qui la référence, ou alors 
• que toute modification de cette référence soit interdite, si des lignes de tables filles l'utilise.

La norme SQL 2 a prévu les modes de gestion des intégrités référentielles suivants :

ON DELETE { NO ACTION | CASCADE }

ON UPDATE { NO ACTION | CASCADE }

Qui signifie que :

ON DELETE NO ACTION

ON DELETE CASCADE

ON UPDATE NO ACTION

ON UPDATE CASCADE

Bien entendu on peut parfaitement supprimer un client qui ne possède pas de commande ou modifier la valeur de sa clé même lorsque le mode NO ACTION est actif.

Dans tous les cas, le mode NO ACTION est à préférer. En effet le mode CASCADE peut entraîner une avalanche de suppressions ou de mises à jour du plus mauvais effet sur les performances du SGBDR !

Ce mécanisme simple et robuste est complété par d'autres possibilités qu'offre la norme SQL et qui sont : SET DEFAULT et SET NULL.

ON DELETE SET DEFAULT

ON DELETE SET NULL

ON UPDATE SET DEFAULT

ON UPDATE SET NULL

Ces deux nouvelles règles proposent une alternative afin de gérer de manière intelligente une pseudo cascade…

Quel intérêt me direz vous d'avoir des commandes dont la référence du client est NULL ou bien 0 (un client générique dont l'existence physique est fausse, par exemple moi-même…) ?
L'intérêt est simple : pouvoir faire le ménage dans les tables de manière ultérieure… Par exemple on pourra prévoir de supprimer toutes les commandes dont la référence client est NULL ou 0 la nuit, lorsque le trafic du serveur est très faible dans un batch planifié déclenché automatiquement. Bien entendu, dans ce cas il faut faire attention à ne pas comptabiliser dans les requêtes les lignes dont la référence du client est NULL ou 0. Ainsi un cumul du montant des commandes pour connaitre le chiffre d'affaires généré mensuellement ne doit pas prendre en compte les lignes avec une référence de client 0 ou NULL…

Tous les SGBDR ne sont pas aussi performants que la norme l'impose en matière de gestion de l'intégrité référentielle. En effet, certains comme ORACLE ou InterBase ont implémenté la plupart de ces règles de gestion. D'autres comme MS SQL Server ne proposent que le NO ACTION. Dans ce dernier cas, l'utilisation de triggers permet de simuler les autres règles.

La gestion de l'intégrité référentielle est de loin le point le plus important pour décider si un SGBD est relationnel ou non. Dépourvu de ce mécanisme il agit comme au bon vieux temps des fichiers COBOL. Muni de ce mécanisme il agit en responsable de l'intégrité des données. Autrement dit un SGBD qui n'est pas doté d'un mécanisme de gestion des intégrités référentielles ne mérite tout simplement pas le nom de système de gestion de bases de données « relationnelles ».
C'est malheureusement le cas de MySQL, qui représente une régression certaine par rapport à des systèmes de fichiers plats comme Paradox qui la possède !

Dans la réalité c'est impossible ! La tentation de ne pas utiliser l'intégrité référentielle (ou d'avoir un SGBD qui ne le supporte pas) et faire cela dans du code client est séduisante… Mais casse-gueule : l'idée fallacieuse qui consiste à dire, je supprime d'abord les factures ensuite les clients, est inacceptable si elle n'est pas conduite dans une transaction. En effet rien n'empêche le code client de s'arrêter (panne disque, coupure de courant, réseau HS, pause café…) entre les deux requêtes ce qui supprime les commandes sans avoir pu supprimer le client…

À lire sur le sujet :
La gestion de l'intégrité référentielle

Mais, lisez la suite, car on peut effectivement se passer de l'intégrité référentielle à condition que la base de données supporte les triggers…

Le terme français est « déclencheur ». Un trigger est donc un élément de code qui se déclenche sur un événement précis, se rapportant à un objet de la base de données. C'est exactement le même concept que la programmation événementielle dans le cadre d'interfaces graphiques. Je ne serais d'ailleurs pas étonné que les OnClick et autre OnMouseDown n'aient été inspirés par la notion de triggers des SGBDR, car il ne faut pas oublier que les SGBDR existaient bien avant les premiers langages faisant référence à des événements graphiques. Mais ceci n'est pas le débat.

Au sens de la norme SQL 3, un trigger se définit uniquement sur les objets de type TABLE, concerne les événements suivants : INSERT, UPDATE, DELETE et peut être déclenché BEFORE (c'est-à-dire avant survenance de l'événement) ou AFTER (c'est-à-dire après survenance de l'événement).
La grande différence entre le code événementiel graphique des langages comme Delphi ou Visual Basic, c'est que l'on peut placer du code, avant ou après le survenance de l'événement, alors que dans les langages graphiques, ce paramètre n'existe pas (enfin, j'allais oublier l'extraordinaire langage ObjectPal de Paradox qui proposait non seulement le pilotage avant après dans le même code, mais encore le « bouillonnement » de l'événement, c'est-à-dire sa remontée depuis le plus grand conteneur jusqu'à l'objet cible ! - au cas où vous l'auriez oublié, celui qui a créé ObjectPal a ensuite créé Delphi puis C# maintenant chez Microsoft !!!).

La syntaxe normative SQL 3 d'un trigger est la suivante :

CREATE TRIGGER nom_trigger
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON nom_table
code

Elle est plus conséquente dans son étendue, mais seule cette partie nous intéresse, car elle en synthétise toutes les possibilités.

La chose la plus attractive dans un trigger, sont les pseudotables OLD et NEW qui contiennent les données en cours d'insertion, de mise à jour ou de suppression…

Examinons ce qui se passe dans un cas concret.
Soit les tables :

CREATE TABLE T_CLIENT
(CLI_ID INTEGER NOT NULL PRIMARY KEY,
 CLI_NOM CHAR(32) NOT NULL,
 CLI_PRENOM VARCHAR(32),
 CLI_STATUT CHAR(1))

INSERT INTO T_CLIENT VALUES (1, 'DUPONT', 'Marcel', 'C')
INSERT INTO T_CLIENT VALUES (4, 'DUFOUR', 'Martin', 'C')
INSERT INTO T_CLIENT VALUES (17, 'DUHAMEL', 'Manuel', 'C')
INSERT INTO T_CLIENT VALUES (161, 'DUBOIS', 'Marie', 'C')

CREATE TABLE T_PROSPECT
(PRP_ID INTEGER NOT NULL PRIMARY KEY,
 PRP_NOM CHAR(32) NOT NULL,
 PRP_PRENOM VARCHAR(32))

INSERT INTO T_PROSPECT VALUES (14, 'Laporte', 'Eric')
INSERT INTO T_PROSPECT VALUES (17, 'Lambert', 'Ernest')
INSERT INTO T_PROSPECT VALUES (161, 'Laurent', 'Emeric')
INSERT INTO T_PROSPECT VALUES (4874, 'Lautier', 'Etienne')

Notre utilisateur veut faire passer tous les prospects dans la table T_CLIENT avec le statut 'P'. Oui, mais, au passage il veut leur donner une clé compatible avec le CLI_ID et reformater le nom en majuscules…

Or un simple ordre d'insertion basique du SQL va violer la contrainte d'unicité de la clé et rejeter en bloc l'insertion…

INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT

Violation de la contrainte PRIMARY KEY 'PK__T_CLIENT__251C81ED'.
Impossible d'insérer une clé en double dans l'objet 'T_CLIENT'.
L'instruction a été arrêtée.

Mais nous pouvons pallier ce problème, sans rien toucher de notre ordre d'insertion en ajoutant un trigger qui va rectifier les données en cours d'insertion AVANT qu'elles ne soient réellement déposées dans la table :

CREATE TRIGGER TRG_INS_BEF_CLIENT
BEFORE INSERT 
ON T_CLIENT

UPDATE NEW /* cette pseudo table contient les prospects avant leur insertion ! */
SET PRP_ID = PRP_ID + (SELECT MAX(CLI_ID) FROM T_CLIENT), 
    PRP_NOM = UPPER(PRP_NOM) 
/* on modifie les données de la pseudo table avant de les insérer définitivement */

Dès lors notre insertion va bien se passer :

INSERT INTO T_CLIENT
SELECT PRP_ID, PRP_NOM, PRP_PRENOM, 'P'
FROM T_PROSPECT

(4) lignes ajoutées

Pour confirmation de ce qui s'est passé, nous pouvons relire la table client :

SELECT * 
FROM T_CLIENT

CLI_ID      CLI_NOM                          CLI_PRENOM                       CLI_STATUT 
----------- -------------------------------- -------------------------------- ---------- 
1           DUPONT                           Marcel                           C
4           DUFOUR                           Martin                           C
17          DUHAMEL                          Manuel                           C
161         DUBOIS                           Marie                            C
175         LAPORTE                          Eric                             P
178         LAMBERT                          Ernest                           P
322         LAURENT                          Emeric                           P
5035        LAUTIER                          Etienne                          P

Mais, me direz-vous, cela nous aurions pu le faire directement dans l'ordre d'insertion… Par exemple avec la requête suivante :

INSERT INTO T_CLIENT
SELECT PRP_ID + (SELECT MAX(CLI_ID) FROM T_CLIENT), UPPER(PRP_NOM), PRP_PRENOM, 'P'
FROM T_PROSPECT

C'est tout à fait vrai, mais si vous aviez des données provenant de toute autre table, alors il aurait fallu adpater votre code et le modifier pour chaque cas, alors que dans le cas d'un trigger, ce code est générique quelle que soit l'insertion que vous voulez faire et d'où qu'elle provienne !

Les cas d'utilisation des triggers sont assez larges :

• le formatage de données ;
• l'auto incrémentation de clés ;
• la suppression en cascade ;
• l'abandon d'une suppression si elle entraîne des lignes orphelines

et plus généralement le traitement d'associations provenant de modèles complexes comme

• les relations 1:n où n est une limite fixe (par exemple 3) ;
• les antirelations (par exemple les mots noirs, voir Indexation textuelle) ;
• les modèles à héritage (par exemple une entité générique VEHICULE et des entités spécialisées comme VOITURE, AVION et BATEAU…) ;
• les arbres modélisés par intervalles (voir Gestion d'arbres par représentation intervallaire ) ;
• …

Essayez donc de modéliser une relation dans laquelle le lien entre table mère et table fille ne doit pas dépasser un maximum de trois occurrences… Par exemple le prêt de livres dans une bibliothèque municipale ?

Dans un tel cas, pour empêcher une insertion surnuméraire, il suffit de compter les lignes déjà insérées, et dès que le nombre de lignes dépasse 3, alors l'invocation d'un ordre ROLLBACK empêche la pseudo table NEW d'attendre la table cible. Les données surnuméraires ne sont alors pas insérées.

Voici l'exemple d'un tel trigger :

CREATE TRIGGER TRG_INS_BEF_PRET
BEFORE INSERT 
ON T_CLIENT
FOR EACH ROW

IF EXISTS(SELECT 1
          FROM   T_PRET P
                 JOIN NEW N
                      ON P.EMPRUNTEUR_ID = N.EMPRUNTEUR_ID 
          HAVING COUNT(*) = 3)
THEN
   ROLLBACK

Que fait-on dans ce code ? D'abord notez l'expression FOR EACH ROW, qui signifie que le déclencheur va s'activer autant de fois qu'il y a de lignes dans la table NEW, en substituant la table NEW et ses n lignes en n déclenchements d'une table NEW ne contenant qu'une seule ligne.
Ensuite, notez qu'on lie la table NEW à la table des prêts sur l'identifiant de l'emprunteur. Puis on compte le nombre de lignes de cette jointure et on invoque un ROLLBACK si ce comptage est égal à 3, afin de ne pas rajouter de nouvelles lignes d'emprunts.
Simple non ? Et tellement élégant !

Si l'on n'utilise pas de modèles complexes et que l'intégrité référentielle est en place, alors il est parfaitement possible de se passer des triggers. Mais vu leur souplesse, la sécurité qu'ils apportent, et la concision du code qu'ils offrent, il serait folie de s'en priver !

Une sous-requête est une requête située à l'intérieur d'une autre requête. La plupart du temps il s'agit d'ordres SELECT imbriqués.
Pour un cours sur le sujet, voir :
les sous-requêtes

Un simple exemple, donné par Peter GULUTZAN montre qu'il est impossible de s'en passer :
Soit la table :

CREATE TABLE TAB1 (COL1 INT) 
CREATE TABLE TAB2 (COL2 INT)

INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (1) 
INSERT INTO TAB1 VALUES (2) 
INSERT INTO TAB2 VALUES (1) 
INSERT INTO TAB2 VALUES (2) 
INSERT INTO TAB2 VALUES (2)

Tentez donc de reproduire la requête si dessous et son résultat sans sous-requête…

SELECT TAB1.COL1 AS COL 
FROM   TAB1 
WHERE  TAB1.COL1 IN (SELECT TAB2.COL2 
                     FROM   TAB2)

COL 
---
1
1 
2

Bien entendu dans 80 % des requêtes que je vois passer devant mes yeux, il est possible de transformer la sous-requête en jointure à l'aide de contorsions assez délicates. Mais pour 20 % d'entre elles, il n'y a pas de solution. Tel est par exemple le cas des prédicats MATCH et UNIQUE. Tel est aussi le cas de la plupart des sous-requêtes implantées dans la clause from et contenant un opérateur d'agrégat statistique.

Les sous-requêtes facilitent la vie du développeur, s'écrivent plus facilement que des jointures dans bien des cas. Elles ont un revers, elles sont souvent moins performantes que des jointures…

L'idée fallacieuse qui consiste à dire que l'on peut s'en passer si l'on dispose de tables temporaires est fausse. En effet, entre l'instanciation d'une table temporaire et la requête suivante, les données peuvent changer et fausser le résultat. Il en résulterait des erreurs, à moins d'effectuer son code dans une transaction !
En effet il ne faut pas oublier qu'une requête est une transaction implicite…

C'est aussi assez difficile de traduire en code des sous-requêtes corrélées, car dans ce cas, l'exécution de la sous-requête est renouvelée pour chaque ligne de la requête principale… Le travail devient vite complexe et le temps d'exécution sur le poste client devient incommensurablement long !

Si vous vous souvenez des patates ou diagrammes de Venn, alors vous pouvez les matérialiser facilement.
Dans le cas contraire, vous pouvez lire à ce sujet :
Les opérateurs ensemblistes

Je vais vous surprendre… La réponse est indubitablement OUI !

L'intersection, comme la différence peuvent être réalisées par des constructions équivalentes à base de jointures ou de sous-requêtes. Voir :
Considérations diverses sur les opérations ensemblistes du SQL

Mais qu'en est-il pour l'union ?
Il est possible de la réaliser à deux conditions :

• que le SGBDR possède une fonction ou structure de substitution des marqueurs NULL (COALESCE, CASE… ou équivalent) ;
• que le SGBDR implémente le FULL OUTER JOIN.

C'est assez rare qu'il y ait ces deux éléments sans l'opérateur UNION. De plus, le cout de calcul de cet équivalent est assez élevé du fait de la présence des fonctions de transformation.

Voici une correspondance entre une union classique de deux tables (les données sont celles du jeu suivant :
Ensembles_exemples ) :

SELECT OBJ_NOM AS NOM, OBJ_PRIX AS PRIX
FROM   T_OBJET
UNION 
SELECT MAC_NOM AS NOM, MAC_PRIX AS PRIX 
FROM   T_MACHINE
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

-- union à base de jointure avec utilisation de COALESCE
SELECT COALESCE(OBJ_NOM, MAC_NOM) AS NOM,
       COALESCE(OBJ_PRIX, MAC_PRIX) AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

-- union à base de jointure avec utilisation de CASE
SELECT CASE 
          WHEN OBJ_NOM IS NULL
               THEN MAC_NOM
               ELSE OBJ_NOM
       END AS NOM,
       CASE
          WHEN OBJ_PRIX IS NULL
               THEN MAC_PRIX
               ELSE OBJ_PRIX
       END AS PRIX
FROM   T_OBJET O
       FULL OUTER JOIN T_MACHINE M
            ON O.OBJ_NOM = M.MAC_NOM
               AND O.OBJ_PRIX = M.MAC_PRIX
ORDER  BY NOM, PRIX

NOM                  PRIX 
-------------------- ------------ 
ASSIETTE             26.5
AVION                NULL
LIVRE                128.0
MOTO                 43528.0
PERCEUSE             259.98999
PERCEUSE             260.0
RÉVEIL               128.0
TABLE                5600.0
VENTILATEUR          250.0

Sans ces éléments, point de salut pour faire l'union…

L'intérêt des UDF réside dans la possibilité d'ajout de fonctions puissantes ou non encore réalisées dans celles fournies par l'éditeur de votre SGBDR. Autrement dit, ajouter les manques du dialecte SQL de votre SGBDR.
Il y a un autre intérêt moins évident. Si vous devez réaliser des applications multiserveurs, c'est-à-dire des applications susceptibles de tourner sur différents SGBDR, et si les SGBDR cibles implémentent les UDF, alors il est possible de définir dans chaque serveur une bibliothèque d'UDF qui permettra d'uniformiser toutes les requêtes qui pourront s'adresser indifféremment à l'un ou l'autre des SGBDR cibles !

Pour la peine je vous en communique trois !

Le premier exemple donne la constante PI utilisable dans n'importe quelle requête :

CREATE FUNCTION PI()
LANGUAGE SQL
RETURNS DECIMAL(16,15)
CONTAINS SQL
RETURN 3.1415926535 89793

SELECT DIAMETRE * PI()
FROM   T_CERCLE

Le second exemple montre une fonction de conversion des francs en euros :

CREATE function franc_euros (VALEUR FLOAT)
LANGUAGE SQL
RETURNS DECIMAL (16, 2)
RETURNS NULL ON NULL 
DECLARE VALEUROS DECIMAL (16, 2)
SET VALEUROS = CAST(VALEUR as decimal(16,2) ) / 6.55957
RETURN CAST(VALEUROS as FLOAT)

SELECT franc_euros(MONTANT_TTC)
FROM   T_FACTURE

Le troisième montre comment on peut utiliser un langage externe pour réaliser une UDF. L'exemple de routine externe est en C et compte les mots d'un CLOB (Character Large OBject) :

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN wordcount(
   SQLUDF_CLOB     *in1,
   SQLUDF_INTEGER  *out,
   SQLUDF_NULLIND  *in1null,
   SQLUDF_NULLIND  *outnull,
   SQLUDF_TRAIL_ARGS) {

   SQLUDF_INTEGER count = 0;
   SQLUDF_INTEGER ind;
   SQLUDF_SMALLINT blank = 0;

   for (ind = 0; ind < in1->length; ind++) {
      if (blank == 0 && in1->data[ind] != ' ') {
         blank = 1;
         count++;
      } else if (blank == 1 && in1->data[ind] == ' ') {
         blank = 0;
      } /* endif */
   } /* endfor */

   *out = count;
   *outnull = 0;
}

CREATE FUNCTION WORD_COUNT (MyText CLOB)
LANGUAGE C
PARAMETER STYLE SQL
DETERMINISTIC
NO SQL
EXTERNAL NAME "UDFbibC/Wordcount"
RETURNS INTEGER
DECLARE N_WORDS INTEGER
SET N_WORDS = Wordcount(MyText)
RETURN (N_WORDS)

À ce stade certaines parties de la déclaration de la fonction sont spécifiques au SGBDR, en particulier la déclaration EXTERNAL NAME et l'appel de la fonction.

Certains SGBDR comme InterBase implémentent depuis longtemps des UDF notamment sous forme de modules externes que l'on peut écrire dans le langage de son choix, comme C ou Delphi, à condition que ces fonctions soient placées dans une bibliothèque sous forme de DLL ou équivalent (dépend de l'OS).

Exemple d'UDF externes pour InterBase réalisées sous Delphi :

//fichier projet delphi "UdfIBsqlPro.dpr"

library UDFibSQLpro;

uses
  SysUtils,
 Classes,
 UudfIB in 'UudfIB.pas';

{$R *.res}

// function flip (s : pchar; pivot : integer) : pchar;
// function reverse ( s : pchar) : pchar;

begin

end.

// fichier d'unité delphi "UudfIB.pas"

unit UudfIB;

interface

Uses
  IBexternals, IBheader, IBIntf;

// intervertit le début et la fin d'une chaine 
// par rapport au caractère pivot
function flip (s : pchar; pivot : integer) : pchar; cdecl;
// inverse les lettres d'un mot
function reverse ( s : pchar) : pchar; cdecl;

Exports
  flip, reverse;

implementation

function flip (s : pchar; pivot : integer) : pchar; cdecl;
var
  sd : string;
  sout : string;
begin
if length(s) = 0
then
begin
  flip := s;
  exit;
end;
sd := string(s);
if pivot >= length(sd)
then
begin
  flip := s;
  exit;
end;
sout := copy(sd, 1, pivot) + copy(sd, pivot+1, length(sd) - pivot);
flip := PChar(sd);
end;

function reverse ( s : pchar) : pchar; cdecl;
var
  sd : string;
  sout : string;
  i : integer;
begin
sd := string(s);
if length(sd) = 0
then
begin
  sout := s;
  exit;
end;
sout := '';
for i :=1 to length(sd)
do
  sout := sout + sd[i];
reverse := PChar(sout);
end;

Initialization
  IsMultithread := true;
  CheckIBloaded;

end.

/-- liaison entre les UDF et les modules externes

DECLARE EXTERNAL FUNCTION FLIP
   CSTRING(256), DOUBLE PRECISION
   RETURNS CSTRING(256)
   ENTRY_POINT "flip"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"
DECLARE EXTERNAL FUNCTION REVERSE
   CSTRING(256)
   RETURNS CSTRING(256)
   ENTRY_POINT "reverse"
   MODULE_NAME "C:\IB\UDF\UdfIBsqlPro.dll"

COMMIT

-- exemple d'utilisation

SELECT FLIP(COL1, 3), REVERSE(COL2)
FROM TEST

Exemple d'UDF pour MS SQL Server 2000 :

-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement les chiffres sans aucun espace

CREATE  FUNCTION FN_FORMATE_CHIFFRE_SEUL (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   WHILE @I <= LEN(@VALUE)
   BEGIN
      IF SUBSTRING(@VALUE, @I, 1) BETWEEN '0' AND '9'
      SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
      SET @I = @I + 1
   END 
   RETURN @NEWVALUE
END

-- Fred BROUARD - 2002-10-11 
-- extrait d'une chaine de caractères uniquement des lettres majuscules non diacritiques,
-- des chiffres et des espaces non doublonnés (conversion des caractères diacritiques)

CREATE  FUNCTION FN_FORMATE_MAJ_SANS_DIAC (@VALUE VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
-- effets de bord : NULL
   IF @VALUE IS NULL
   RETURN @VALUE
-- effet de bord : chaine vide
   IF @VALUE = ''
   RETURN @VALUE
-- cas courant
   DECLARE @NEWVALUE VARCHAR(8000)
   SET @NEWVALUE = ''
   DECLARE @I INTEGER
   SET @I = 1
   DECLARE @C CHAR(1)
   DECLARE @LETTRE BIT
   SET @VALUE = UPPER(@VALUE)
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @LETTRE = 0
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) BETWEEN CAST('0' AS VARBINARY(32)) 
                                        AND CAST('9' AS VARBINARY(32))
       OR CAST(@C AS VARBINARY(32)) BETWEEN CAST('A' AS VARBINARY(32))
                                        AND CAST('Z' AS VARBINARY(32))
      BEGIN 
          SET @NEWVALUE = @NEWVALUE + SUBSTRING(@VALUE, @I, 1)
          SET @LETTRE = 1
      END
      ELSE
      BEGIN
         IF @C='à' OR @C='À' OR @C='â' OR @C='Â' OR @C='ä' OR @C='Ä' OR @C='Á' 
         OR @C='á' OR @C='Ã' OR @C='ã' OR @C='å' OR @C='Å'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'A'
            SET @LETTRE = 1
         END
         IF @C='æ' OR @C='Æ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'AE'
            SET @LETTRE = 1
         END
         IF @C='Ç' OR @C='ç'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'C'
            SET @LETTRE = 1
         END
         IF @C='é' OR @C='É' OR @C='è' OR @C='È' OR @C='ê' OR @C='Ê' OR @C='ë' OR @C='Ë'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'E'
            SET @LETTRE = 1
         END
         IF @C='î' OR @C='Î' OR @C='ï' OR @C='Ï' OR @C='ì' OR @C='Ì' OR @C='í' OR @C='Í'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'I'
            SET @LETTRE = 1
         END
         IF @C='ñ' OR @C='Ñ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'N'
            SET @LETTRE = 1
         END
         IF @C='ô' OR @C='Ô' OR @C='ö' OR @C='Ö' OR @C='ò' OR @C='ó'
         OR @C='Ò' OR @C='Ó' OR @C='Õ' OR @C='õ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'O'
            SET @LETTRE = 1
         END
         IF @C='œ' OR @C='Œ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'OE'
            SET @LETTRE = 1
         END
         IF @C='ß'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'SS'
            SET @LETTRE = 1
         END
         IF @C='Ù' OR @C='Ú' OR @C='Û' OR @C='Ü' OR @C='ù' OR @C='ú' OR @C='û' OR @C='ü'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'U'
            SET @LETTRE = 1
         END
         IF @C='Ý' OR @C='ý' OR @C='ÿ'
         BEGIN
            SET @NEWVALUE = @NEWVALUE + 'Y'
            SET @LETTRE = 1
         END
         IF @LETTRE = 0
            SET @NEWVALUE = @NEWVALUE + ' '
      END
      SET @I = @I + 1
   END 
-- dédoublonnage des espaces parasites
   SET @VALUE = @NEWVALUE
   SET @NEWVALUE = ''
   SET @I = 1
   SET @LETTRE = 0
   WHILE @I <= LEN(@VALUE)
   BEGIN
      SET @C = SUBSTRING(@VALUE, @I, 1)
      IF  CAST(@C AS VARBINARY(32)) = CAST(' ' AS VARBINARY(32))
      BEGIN
         IF @LETTRE = 1
            SET @NEWVALUE = @NEWVALUE + @C
         SET @LETTRE = 0
      END
      ELSE
      BEGIN
         SET @NEWVALUE = @NEWVALUE + @C 
         SET @LETTRE = 1 
      END
      SET @I = @I + 1 
   END
   RETURN LTRIM(@NEWVALUE)
END

/* obtention de la liste des colonnes d'une table */

-- F. BROUARD - 2002-10-10

CREATE FUNCTION FN_LIST_COLUMNS (@TABLE_NAME VARCHAR(128)) 
RETURNS VARCHAR (8000)
AS
BEGIN
   DECLARE @LIST_COLS VARCHAR(8000)
   SET @LIST_COLS ='('
   SELECT @LIST_COLS = @LIST_COLS + COLUMN_NAME + ', '
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = @TABLE_NAME
   SET @LIST_COLS = SUBSTRING(@LIST_COLS, 1, LEN(@LIST_COLS)-1) + ')'
   RETURN(@LIST_COLS)
END

Bien entendu… On peut réaliser quelques-unes des UDF dans des vues par exemple ou des procédures stockées. On peut encore faire un traitement des données reçues sur le poste client. Mais dans ce dernier cas, les performances ne sont pas très intéressantes.
Autrement dit les UDF ne sont pas absolument nécessaires, mais bien utiles !

Cela sert à enregistrer toutes les manipulations de données qui sont lancées par des requêtes SQL sur le serveur, afin de les exécuter de la manière la plus intègre possible afin que tout problème survenant lors d'une transaction n'induise pas une « désintégration » de la base…
Le journal capte toutes les demandes et assure un mécanisme de reprise automatique dans le cas d'une panne, mais aussi gère les ROLLBACK et COMMIT.
Aucune base de données relationnelle gérant des transactions ne peut se passer de ce type de mécanisme.

Attention : une erreur fréquente est de croire qu'à l'aide du journal, on peut suivre ce que tel ou tel utilisateur a pu faire sur la base, ou bien quelles ont été les modifications survenues dans telle ou telle table. La journalisation est en principe un mécanisme strictement interne au SGBDR et donc réservé à son seul usage. Il n'est donc pas lisible pour un utilisateur, fut-il administrateur de la base de données ou du serveur.

Je n'entrerai pas dans les détails de l'implémentation de la journalisation de tel ou tel éditeur. Je vais simplement vous expliquer comment cela marche de manière générique…

Lorsqu'une requête du DML (Data Manipulation Language) c'est-à-dire un ordre SELECT, INSERT, UPDATE ou DELETE, est envoyée au serveur, le journal écrit dans son fichier les éléments relatifs à quel utilisateur se rapporte cette demande ainsi que quelques paramètres comme la date et l'heure de la demande. Le journal inscrit la requête telle qu'il la trouve ainsi qu'un marqueur pour signaler le début de la transaction (n'oubliez pas que toute requête, la plus simple soit-elle est une transaction à part entière).
Ces informations pourraient se présenter comme suit :

REQUEST FOR :
USER       = SQLpro
CONNECTION = 0x001252
SERVER     = SRV_PROD
DATABASE   = DB_CLIENT
QUERY IS :
   UPDATE T_PRIX
   SET PRX_PRIX = PRX_PRIX * 1.1
END QUERY
START      = 2002-09-11 13:24:55.235
BEGIN TRANSACTION 056512300651324568456521

Le SGBDR signale au client que la requête a été prise en compte et lui demande d'attendre le retour d'exécution.

Nous supposons que la table T_PRIX contienne les données suivantes :

CREATE TABLE T_PRIX
(PRX_ID INTEGER NOT NULL PRIMARY KEY,
 PRX_PRIX FLOAT NOT NULL)

INSERT INTO T_PRIX VALUES (1, 254.15)
INSERT INTO T_PRIX VALUES (2, 98541.24)
INSERT INTO T_PRIX VALUES (3, 8741.99)

Nous allons maintenant interrompre le courant électrique de notre serveur à tout instant pour voir comment se comporte le SGBDR…

BEGIN TRANSACTION 056512300651324568456521

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Il interdit à tout utilisateur de lui envoyer des requêtes tant qu'il n'a pas retrouvé son point d'équilibre (intégrité des données)…

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
2, 98541.24

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes écrites, il supprime celles déjà présentes et recommence son travail…

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes calculées et inscrites, il supprime celles déjà présentes et recommence son travail…

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION

Le journal est relu « à l'envers » et le SGBDR essaye de retrouver la dernière marque de transaction validée ou rollbacké, puis reprend la lecture du journal et redémarre le travail sur les transactions inachevées. Si les données concernées par la modification n'ont pas été toutes répercutées dans la table, il recommence à la première valeur…

PREVIOUS INFORMATIONS :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 254.15
1, 98541.24
1, 8741.99
END PREVIOUS INFORMATIONS
FUTURE INFORMATION :
OBJECT  = TABLE(T_PRIX)
DATA =: KEY (PRX_ID), COLS(PRX_PRIX)
ARE:
1, 279.4
2, 108395,10
3, 9615,10
END FUTURE INFORMATION
TRANSACTION COMPLETE
WORK COMMITED

Et voilà ! Rien de plus simple en apparence… Rajoutez-y la gestion des verrous et une concurrence de traitement… et donnez m'en des nouvelles !!!

On peut, bien entendu s'amuser à réinventer la roue… Implémenter une journalisation sur une base qui en est dépourvue n'a rien d'impossible. En revanche la complexité d'un tel programme en multiutilisateur est telle qu'il y a fort à parier que vous mettrez quelques années à en réaliser un performant… D'ailleurs, sachez que la gestion des transactions est en cours de développement pour le SGBD MySQL, depuis… combien de temps déjà ? Oui, oui, je sais, ce sera dans la future version…
Mais que je vous rassure, la plupart des véritables SGBDR font cela depuis… combien de temps déjà ? Ah oui, deux décennies !