Gestion de nouvelles données

hiérarchiques.
· Afficher les détails concernant Robert et tous ses subordonnés à  tous les niveaux.

· Quel est la somme des salaires de Robert et de tous ses subordonnés à  tous les
niveaux ?
· Afficher les détails concernant l’ensemble des employés du bas de l’échelle
sous la responsabilité de Janet.
· Afficher les détails concernant l’ensemble des employés deux niveaux en dessous
de Janet.
· Afficher la chaîne hiérarchique menant à  James.

Pour répondre à  de telles requêtes à  l’aide de la structure existante de la table
Employees, il faut utiliser des curseurs ou des tables temporaires. On peut utiliser
des procédures cataloguées ou une application cliente pour mettre en oeuvre les
curseurs ou les tables temporaires (pour avoir des exemples de ces solutions,
reportez-vous à  SQL Server Books Online (BOL), section « Expanding Hierarchies ».)
Une alternative consiste à  ajouter des informations concernant la structure hiérarchique
à  la table, puis à  utiliser des instructions T-SQL pour répondre à  ces questions.
Le problème de la modification de la structure de la table (l’ajout d’un nouveau
membre par exemple, ou la mise à  jour d’un membre existant) est que les informations
supplémentaires peuvent ne plus être synchronisées avec la réalité et ainsi perdre
leur valeur. Pour éviter cet inconvénient, il faut généralement imposer aux utilisateurs
de modifier les données uniquement par le biais des procédures cataloguées.

La solution utilise des déclencheurs, et non des procédures cataloguées
spéciales pour mettre à  jour les données hiérarchiques

Cet article présente une solution qui nécessite d’ajouter des informations à  la
table Employees. Le listing 1 présente le script créant une nouvelle table Employees
avec deux colonnes supplémentaires. La colonne lvl contient le niveau de chaque
employé dans la hiérarchie, de zéro au niveau le plus haut. Le niveau lvl de James,
par exemple, est 14. La colonne hierarchy contient la chaîne hiérarchique de chaque
employé, en commençant par l’ID du directeur le plus haut et en terminant par
l’ID de l’employé. Les points séparent les ID des employés dans la chaîne. Par
exemple, la hiérarchie de James est ‘.1.3.7.11.14’.
La maintenance automatique de cette solution en est un aspect important : elle
utilise des déclencheurs pour mettre à  jour automatiquement les valeurs lvl et
hierarchy dès qu’un utilisateur de la base de données met à  jour les informations
concernant un employé. La solution utilise des déclencheurs, et non des procédures
cataloguées spéciales pour mettre à  jour les données hiérarchiques car les déclencheurs
permettent un contrôle par programmation des mises à  jour et se lancent automatiquement
après une modification.
Le listing 2 présente le déclencheur sur insertion permettant de maintenir les
colonnes lvl et hierarchy. Notez qu’après avoir créé le déclencheur, il faut réinsérer
la ligne de chaque employé dans la nouvelle table Employees pour y insérer les
données. Notez également que ce déclencheur permet de n’insérer qu’une ligne à 
la fois. On peut ajouter un algorithme pour prendre en charge des insertions de
sous-arbres entiers (les sous-hiérarchies de la hiérarchie complète) mais je ne
l’ai pas fait pour éviter que cette solution ne devienne trop compliquée.
Voyons ce qui se passe à  l’intérieur du déclencheur sur insertion. Supposons que
l’on souhaite ajouter un employé appelé Sean, sous la responsabilité de Ron (ID
employé 12). Il faut exécuter l’instruction d’insertion suivante :

INSERT INTO employees(empid, mgrid, empname,
salary) VALUES(15, 12, ‘Sean’, $1500.00)

La figure 2 illustre ce qui se passe de manière logique dans le déclencheur. Tout
d’abord, le déclencheur fait une jointure entre la table Employees et la table
insérée (ne contenant que la nouvelle ligne concernant Sean) et renvoie la ligne
de Sean. Cette jointure est possible car le déclencheur se met en route uniquement
après l’ajout des informations concernant Sean dans la table Employees. Cependant,
les colonnes lvl et hierarchy de Sean ne contiennent encore que des valeurs NULL.
Le déclencheur prend alors le résultat de l’étape 1 (ligne de Sean) et fait une
jointure externe gauche avec la table Employees pour trouver la ligne du chef
de Sean. Le déclencheur utilise ici une jointure externe gauche car le nouvel
employé peut ne pas avoir de supérieur hiérarchique. Si la colonne mgrid de l’employé
contient NULL, aucune correspondance de responsable n’existe et la ligne que l’on
tente de modifier disparaîtra du résultat de la jointure.
A présent que toutes les informations employé/manager requises sont disponibles,
le déclencheur peut définir et mettre à  jour les colonnes lvl et hierarchy. L’instruction
ci-dessous calcule la valeur de la colonne lvl :

lvl = CASE
WHEN E.mgrid IS NULL THEN 0
ELSE M.lvl + 1
END

Si E.mgrid est NULL, cela signifie que l’on a ajouté un employé sans supérieur
hiérarchique et, par conséquent, le niveau de l’employé est zéro. Dans tous les
autres cas, le niveau de l’employé est un niveau en dessous de son manager.
L’instruction ci-dessous calcule ensuite la colonne hierarchy :

hierarchy = CASE
WHEN E.mgrid IS NULL THEN ‘.’
ELSE M.hierarchy
END + CAST(E.empid AS varchar(10)) + ‘.’

Si l’employé n’a pas de manager, la colonne hierarchy est ‘.empid.’ Dans tous
les autres cas, la valeur de la colonne correspond à  la valeur hiérarchique du
directeur de l’employé concaténé à  l’ID employé de l’employé.