Introduction :

Il s'agit de surveiller une ou des tables d'une base de données, et de garder la trace des différentes évolutions des données.

Mise en œuvre :

Il faut d'abord activer la surveillance au niveau de la base de données :

    -- Mise en oeuvre du suivi des modifications

    ALTER DATABASE DemoSuiviModifications

    SET CHANGE_TRACKING = ON

    (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Ensuite, pour chacune des tables à surveiller, on fait aussi une activation :

    -- Création d'une table à suivre

    create table Personnel (IdPersonnel int identity (1,1) not null primary key,

    Nom nvarchar(50) not null,

    Salaire money not null)

    go

 
    -- Quelques lignes

    insert into Personnel (Nom,Salaire) values ('Dominique',50000),('Isabelle',40000),('David',30000)

    go

    -- Et allons y pour le suivi :

    ALTER TABLE Personnel ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

    go

Vous noterez que j'ai utilisé l'insert multiple de 2008.

Quelles sont les tables ainsi surveillées ?

    -- Qui est surveillé ?

    select OBJECT_NAME(object_id) as TableSurveillée from sys.change_tracking_tables

    go

La fonction indispensable pour cette surveillance s'appelle CHANGETABLE, en voici la syntaxe :

--CHANGETABLE
--  ( {
--       NomTableEspionnee , DepuisNoVersion
--        | VERSION table , { ( NomColonnePk [ , ...n ] ) , ( ValeurColonnePk [ , ...n ] ) }
--  --} )
--        --[ AS ] AliasDeTable [ ( AliasDeColonne [ ,...n ] ) ]
--
--

Il y a donc deux modes d'exécution :

• Récupérer les modifications depuis une version donnée
• Récupérer les valeurs actuelles des versions de lignes

Voici ce que donne la 1ère :

    select * from CHANGETABLE (CHANGES Personnel,0) as Ev

Normal : je n'ai fait aucune mise à jour sur la table, un petit update va arranger cela :

    update Personnel set salaire = 35000 where IdPersonnel = 3

    go

Bon on apprend que l'on a modifié une ligne : elle a le NoPersonnel = 3, et les colonnes 0x0000000003000000 ont été modifiées.

On peut heureusement décrypter ce masque par :

    -- Obtenir l'ID unique de la table

    select OBJECT_ID('Personnel')

    -- puis l'ID d'une colonne

    select COLUMNPROPERTY(OBJECT_ID('Personnel'), 'Salaire', 'ColumnId')

    -- Et un masque des colonnes modifiées

    select CHANGE_TRACKING_IS_COLUMN_IN_MASK ( COLUMNPROPERTY(OBJECT_ID('Personnel'), 'Salaire', 'ColumnId') ,SYS_CHANGE_COLUMNS ),*

    from CHANGETABLE (CHANGES Personnel,1) as Ev

    -- A-t-on modifié les salaires ?

    select * from CHANGETABLE (CHANGES Personnel,NULL) as Ev

    where CHANGE_TRACKING_IS_COLUMN_IN_MASK ( COLUMNPROPERTY(OBJECT_ID('Personnel'), 'Salaire', 'ColumnId') ,SYS_CHANGE_COLUMNS ) = 1

Ce qui nous permet de chercher si on a modifié les salaires depuis la version la plus petite (null = en fait celle disponible dans la durée de rétention paramétrée au début de ce post).

Bon, mais où sont les valeurs des colonnes modifiées ?
Hélas ! 3 fois hélas ! Elles ne sont stockées nulle part, et la seule chose que l'on puisse savoir est que cette ligne a été insérée, supprimée ou mise à jour....

Ce choix a très certainement été fait pour des raisons de performances, ce qui part d'un bon sentiment, mais ne permettra que d'envoyer ce qui a été modifié depuis (avec la dernière version) , par exemple pour faire une interface d'export.


Où sont stockées les évolutions ?

Dans la même base de données, ainsi que le montre :

    -- Où sont les données de suivi ?

    select OBJECT_NAME(parent_object_id),* from sys.internal_tables

    where internal_type = 209

C'est dans une table interne (donc non visible) que cela se passe.


Voici la deuxième écriture de la fonction :

    -- Lignes avec leur version

    SELECT Personnel.IdPersonnel, Personnel.Nom, Personnel.Salaire,

        Evolutions.SYS_CHANGE_VERSION, Evolutions.SYS_CHANGE_CONTEXT

    FROM Personnel

    CROSS APPLY CHANGETABLE

        (VERSION Personnel, (IdPersonnel), (Personnel.IdPersonnel)) AS Evolutions;

Mais, comme dit plus haut, on sait que cela a bougé, qu'est-ce qui a bougé, mais pas la valeur qui a bougé.

Enfin, une autre fonction qui peut être utile :

    -- La dernière version utilisée

    select CHANGE_TRACKING_CURRENT_VERSION();

    -- Ce numéro est-il global à la base ?

    create table Tva (IdTva int primary key not null,Taux decimal(6,3))

    go

    insert into Tva (IdTva,Taux) values (1,18.6),(2,33.3),(3,0)

    go

    ALTER TABLE Tva ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

    go

    update Tva set Taux = 22.6 where IdTva = 1;

    select CHANGE_TRACKING_CURRENT_VERSION();

Effectivement, le tracking est global à la base de données.



Enfin, il est facile de le désactiver par :

    -- On peut désactiver le suivi

    ALTER TABLE Personnel DISABLE CHANGE_TRACKING;

Conclusion :

Cette fonctionnalité peut être utile dans le cas d'un progiciel, où je me vois assez mal ajouter des colonnes d'estampillage, style rowversion.

Mais, dans le cas d'un développement spécifique, l'apport est trop faible pour justifier son emploi...

Mais ma boule de cristal me dit que Microsoft ne s'arrêtera pas là.... à suivre donc.....