Dans le cadre d'un projet web, j'ai eu à conserver un log des modifications effectuées sur une table d'une base de données SQL Server. La définition de la table contenait déjà des colonnes indiquant la date de création et la dernière modification relatives à l'enregistrement, comme c'est le cas lorsqu'on met à jour tous les champs à partir d'un formulaire web.
Dans mon cas, le besoin du client nécessitait plus de "granularité" pour pouvoir connaître à quel moment chaque champ a été modifié, de façon à être en mesure de comparer les données avant et après la mise à jour.
La façon la plus simple que j'ai trouvé a été de créer un trigger (déclencheur) au niveau de la mise à jour de la table et de comparer les valeurs à l'aide des tables spéciales inserted et deleted (automatiquement gérées par MSSQL). Le concept de ces tables est assez simple à comprendre :
- l'enregistrement de la table contient les données
- lorsque le trigger démarre, une copie des données sur le point d'être modifiées sont stockées dans la table "deleted". Ces données seront "supprimées" car elle seront remplacées par les nouvelles valeurs
- quant aux nouvelles données qui remplaceront les anciennes, elles se trouveront dans la table "inserted"
- pour ceux qui se posent la question, il n'existe pas de table spéciale "updated" ;-)
-- Attention
-- Cet exemple fonctionne pour un seul enregistrement.
-- Pour plusieurs, il faudra faire une boucle
-- sur chaque enregistrement de la table inserted.
CREATE TRIGGER trigger_log_de_comparaison_sur_nom_de_la_table
ON dbo.nom_de_la_table
AFTER UPDATE
AS
BEGIN
DECLARE
@pk_record INT,
-- pour chaque champ à comparer
@old_name VARCHAR(50),
@new_name VARCHAR(50)
-- sélectionner la nouvelle valeur
SELECT
@pk_record = pk_record,
@new_name = name
FROM inserted
-- sélectionner l'ancienne valeur
SELECT @old_name = name
FROM deleted
IF @old_name <> @new_name
BEGIN
-- insert in log table
INSERT INTO dbo.log_table (pk_record, old_name, new_name, dt_changed)
VALUES ( @pk_record, @old_name, @new_name, getdate() )
END
END
