Le besoin d’historiser l’évolution des données est de plus en plus fort. Dans certains domaines, comme dans l’informatique médicale et tout ce qui touche à la santé, le demande de traçabilité de l’information, tant dans ses évolutions que dans son origine est important.
Il existe différentes méthodes dont j’ai parlé à travers une série d’article de SQL Server Magazine. Voici une méthode mise en place pour Santé Service dans la cadre de la refont de l’ERP interne.
Bien entendu, la solution retenue pour Santé Service est à la fois plus fine et plus profonde, car spécifique à SQL Server.
Cet article montre une synthèse que la plupart des SGBDR sont capable de mettre en œuvre facilement.
Les exemples sont donnés pour MS SQL Server, mais peuvent être reproduit sous n’importe quel SGBDR acceptant de produire des résultats de requête sous forme XML
1 – LE PRINCIPE
Notre mécanisme d’historisation des données (aussi appelé data tracking, change data capture ou encore audit trailing…) est constitué de deux parties :
1.1 – l’historisation en ligne (IN LINE) :
Elle est constituée de colonnes rajoutées à la table afin de connaître deux groupes d’informations :
– la date/heure et le nom d’utilisateur SQL de celui qui à inséré la ligne;
– la date/heure et le nom d’utilisateur SQL de celui qui à modifié la ligne en dernier.
Et de déclencheurs associés pour mettre à jour ces données (triggers INSERT et UPDATE).
1.2 – l’historisation hors ligne (OFF LINE) :
Elle est constitué de trois tables permettant d’obtenir toutes les changements d’informations intervenus lors des modifications (UPDATE) et de la suppression (DELETE) :
– T_TRACKED_TABLES_TBL : la table listant les tables dont on désire suivre l’évolution;
– T_TRACKED_DATATYPES_TDT : la table listant les seuls types de données dont on veut suivre l’évolution;
РT_TRACKED_DATACHANGES_TKC : la table de suivi du changement des donn̩es.
Et de déclencheurs associés pour insérer les données de l’évolution dans la table de suivi (triggers UPDATE et DELETE).
La suite de cet article est constitués essentiellement de code commenté, dont une partie constitue un jeu de test.
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
TEST : 1ere partie
Création de tables pour s’amuser avec l’historisation des données (une table client et une table commande).
CREATE TABLE T_CLIENT_CLI
(
CLI_ID INT NOT NULL IDENTITY PRIMARY KEY,
CLI_NOM VARCHAR(16),
CLI_OBSERVATIONS VARCHAR(max)
);
CREATE TABLE T_COMMANDE_CMD
(
CMD_ID INT NOT NULL IDENTITY PRIMARY KEY,
CLI_ID INT NOT NULL FOREIGN KEY REFERENCES T_CLIENT_CLI (CLI_ID),
CMD_DATE DATE,
CMD_OBSERVATIONS XML
)
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
2 – LE MÉCANISME
Voici maintenant une série de 7 scripts SQL permettant de mettre en Å“uvre ce dispositif…
Veuillez noter que si vous être sur MS SQL Server, vous avez la possibilité d’exécuter le script n°0 en fin de document pour création de la base conformément aux besoin de test de cette solution.
2.1 – script de création des tables d’historisation
--===========================================================================--
-- 1 : script de création des tables d'historisation --
--===========================================================================--
-- isoler les tables de tracking dans un schema particulier de la base est une bonne idée.
-- on ne mélange pas les torchons et les serviettes !
CREATE SCHEMA S_TRK;
GO
-- cette table comporte la liste des tables dont on veut suivre l'évolution :
CREATE TABLE S_TRK.T_TRACKED_TABLES_TBL
(
TBL_ID INT NOT NULL IDENTITY
CONSTRAINT PK_TBL PRIMARY KEY,
TBL_SCHEMA NVARCHAR(128) NOT NULL,
TBL_NAME NVARCHAR(128) NOT NULL,
CONSTRAINT UK_TBL UNIQUE (TBL_SCHEMA, TBL_NAME)
);
GO
-- cette table contient les évolutions des données entreprise à chaque UPDATE ou DELETE dans les tables suivies :
CREATE TABLE S_TRK.T_TRACKED_DATACHANGES_TKC
(
TKC_ID BIGINT NOT NULL IDENTITY
CONSTRAINT PK_TKC PRIMARY KEY,
TBL_ID INT NOT NULL
CONSTRAINT FK_TKC_TBL FOREIGN KEY
REFERENCES S_TRK.T_TRACKED_TABLES_TBL (TBL_ID),
TKC_LOGIN_NAME NVARCHAR(128) NOT NULL
CONSTRAINT DK_TKC_LOG DEFAULT SYSTEM_USER,
TKC_USER_NAME NVARCHAR(128) NOT NULL
CONSTRAINT DK_TKC_USR DEFAULT USER,
TKC_DATETIME DATETIME NOT NULL
CONSTRAINT DK_TKC_DHC DEFAULT GETDATE() ,
TKC_DELETE BIT NOT NULL
CONSTRAINT DK_TKC_DEL DEFAULT 0,
-- partie spécifique à MS SQL Server :
TKC_CLIENT_NET_ADDRESS VARCHAR(48),
TKC_LOCAL_NET_ADDRESS VARCHAR(48),
TKC_HOST_NAME NVARCHAR(128),
TKC_PROGRAM_NAME NVARCHAR(128),
TKC_LOG_NAME NVARCHAR(128),
TKC_ORIGINAL_LOGIN_NAME NVARCHAR(128),
TKC_NT_DOMAIN NVARCHAR(128),
TKC_NT_USER NVARCHAR(128),
-- fin de la partie spécifique à MS SQL Server
TKC_XML_DATA XML NOT NULL
);
GO
-- cette table comportera la liste des types de données autorisés à être tracés.
CREATE TABLE S_TRK.T_TRACKED_DATATYPES_TDT
(
TDT_ID INT NOT NULL IDENTITY PRIMARY KEY,
TDT_DATATYPE NVARCHAR(128)
);
GO
2.2 Рalimentation de la liste des tables et des types de donn̩es historis̩s
--===========================================================================--
-- 2 : alimentation de la liste des tables et des types de données historisés
--===========================================================================--
-- voici comment ou alimente la table des tables à suivre :
INSERT INTO S_TRK.T_TRACKED_TABLES_TBL (TBL_SCHEMA, TBL_NAME)
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- on évitera d'auditer les tables d'audit !
AND TABLE_SCHEMA + '.' + TABLE_NAME
NOT IN ('S_TRK.T_TRACKED_TABLES_TBL',
'S_TRK.T_TRACKED_DATACHANGES_TKC',
'S_TRK.T_TRACKED_DATATYPES_TDT');
-- vous pouvez exclure certaines autres tables de l'historisation
-- AND TABLE_NAME NOT IN (...)
-- AND TABLE_SCHEMA NOT IN (...)
-- on évitera de spécifier les types BLOBS ou des types complexes (XML, UDT, types RO...).
INSERT INTO S_TRK.T_TRACKED_DATATYPES_TDT
VALUES ('char'), ('varchar'), ('nchar'), ('nvarchar'),
('decimal'), ('numeric'), ('float'), ('real'), ('int'), ('smallint'), ('bigint'),
('date'), ('time'),
-- types sépcifiques SQL Server :
('datetime'), ('datetime2'), ('smalldatetime'), ('datetimeoffset'),
('money'), ('smallmoney'),
('tinyint'),
('bit');
GO
2.3 Рscript g̩n̩rant les requ̻tes DDL de cr̩ation des nouvelles colonnes :
--===========================================================================--
-- 3 : script générant les requêtes DDL de création des nouvelles colonnes --
--===========================================================================--
-- Ã l'aide d'une CTE on scripte les ordres SQL DDL pour rajouter les colonnes
WITH
T0 AS
(
SELECT TBL_SCHEMA, TBL_NAME
FROM S_TRK.T_TRACKED_TABLES_TBL
)
SELECT 'ALTER TABLE ' + TBL_SCHEMA + '.' + TBL_NAME + ' ADD ' +
'TRK_DH_INSERT DATETIME DEFAULT GETDATE();'
FROM T0
UNION ALL
SELECT 'ALTER TABLE ' + TBL_SCHEMA + '.' + TBL_NAME + ' ADD ' +
'TRK_DH_UPDATE DATETIME;'
FROM T0
UNION ALL
SELECT 'ALTER TABLE ' + TBL_SCHEMA + '.' + TBL_NAME + ' ADD ' +
'TRK_ID_INSERT NVARCHAR(128);'
FROM T0
UNION ALL
SELECT 'ALTER TABLE ' + TBL_SCHEMA + '.' + TBL_NAME + ' ADD ' +
'TRK_ID_UPDATE NVARCHAR(128);'
FROM T0;
GO
-- le résultat de ce script doit être exécuté.
2.4 – création d’une fonction de calcul d’auto jointure :
--===========================================================================--
-- 4 : création d'une fonction de calcul d'auto jointure
--===========================================================================--
CREATE FUNCTION dbo.F_SCRIPT_SELF_JOIN
(@SHEMA SYSNAME, @TABLE SYSNAME, @ALIAS_LEFT SYSNAME, @ALIAS_RIGHT SYSNAME)
RETURNS VARCHAR(max)
AS
/******************************************************************************
* fonction de calcul d'auto jointure *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-04-10 *
*******************************************************************************
* Cette fonction calcule la jointure naturelle de deux tables, *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(max);
SET @OUT = '';
SELECT @OUT = @OUT + @ALIAS_LEFT +'.' + COLUMN_NAME +' = '
+ @ALIAS_RIGHT + '.' + COLUMN_NAME + ' AND '
FROM (SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE TC.TABLE_SCHEMA = @SHEMA
AND TC.TABLE_NAME = @TABLE
AND CONSTRAINT_TYPE = 'PRIMARY KEY') AS T;
IF LEN(@OUT) > 4
SET @OUT = SUBSTRING(@OUT, 1, LEN(@OUT) - 4);
RETURN @OUT;
END
GO
Cette fonction est rendue nécessaire par le fait que le script suivante met en place des jointures de la table sur son double dans le trigger (pseudo table inserted ou deleted). Pour ce faire on utilise cette fonction qui calcule automatiquement la jointure en se basant sur les colonnes composant la clef primaire.
2.5 Рscript g̩n̩rant les d̩clencheurs alimentant les donn̩es en ligne :
--===========================================================================--
-- 5 : script générant les déclencheurs alimentant les données en ligne --
--===========================================================================--
-- pour indenter l'écriture du déclencheur, nous utilisons la variable @CRLF
-- qui contient un Carriage Return et un Line Feed
DECLARE @CRLF NCHAR(2);
SET @CRLF = CHAR(13) + CHAR(10);
-- la requête générant les ordres SQL de création des triggers IN LINE :
WITH
T0 AS
(
SELECT TBL_SCHEMA AS S, TBL_NAME AS T,
CASE
WHEN LEN(TBL_SCHEMA) <= 32 THEN TBL_SCHEMA
ELSE SUBSTRING(TBL_SCHEMA, 1, 32)
END + '_' +
CASE
WHEN LEN(TBL_NAME) <= 64 THEN TBL_NAME
ELSE SUBSTRING(TBL_NAME, 1, 64)
END AS TAG
FROM S_TRK.T_TRACKED_TABLES_TBL
)
SELECT 'CREATE TRIGGER E_I_' + TAG +'_TRACK_INLINE ' + @CRLF
+ 'ON ' + S + '.' + T + @CRLF
+ 'FOR INSERT' + @CRLF
+ 'AS' + @CRLF
+ '/******************************************************************************' + @CRLF
+ '* Trigger de gestion des méta données d''insertion (utilisateur + dateheure) *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Déclencheur FOR INSERT créé automatiquement *' + @CRLF
+ '******************************************************************************/' + @CRLF
+ 'UPDATE ' + S + '.' + T + @CRLF
+ 'SET ' + 'TRK_ID_INSERT = USER,' + @CRLF
+ ' ' + 'TRK_DH_INSERT = CURRENT_TIMESTAMP' + @CRLF
+ 'FROM ' + S + '.' + T + ' AS T ' + @CRLF
+ ' INNER JOIN inserted as I ' + @CRLF
+ ' ON ' + dbo.F_SCRIPT_SELF_JOIN (S, T, 'T', 'I') +';' + @CRLF
FROM T0
UNION ALL
SELECT 'CREATE TRIGGER E_U_' + TAG +'_TRACK_INLINE ' + @CRLF
+ 'ON ' + S + '.' + T + @CRLF
+ 'FOR UPDATE' + @CRLF
+ 'AS' + @CRLF
+ '/******************************************************************************' + @CRLF
+ '* Trigger de gestion des méta données de modification (utilisateur+dateheure) *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Déclencheur FOR UPDATE créé automatiquement *' + @CRLF
+ '******************************************************************************/' + @CRLF
+ 'UPDATE ' + S + '.' + T + @CRLF
+ 'SET ' + 'TRK_ID_UPDATE = USER,' + @CRLF
+ ' ' + 'TRK_DH_UPDATE = CURRENT_TIMESTAMP' + @CRLF
+ 'FROM ' + S + '.' + T + ' AS T ' + @CRLF
+ ' INNER JOIN inserted as I ' + @CRLF
+ ' ON ' + dbo.F_SCRIPT_SELF_JOIN (S, T, 'T', 'I') +';' + @CRLF
FROM T0
GO
-- le résultat de ce script doit être exécuté.
2.6 – création d’une fonction de calcul d’auto jointure :
--===========================================================================--
-- 6 : création d'une fonction de calcul d'auto jointure
--===========================================================================--
CREATE FUNCTION dbo.F_GET_ALL_COLUMNS (@SCH NVARCHAR(128),
@TAB NVARCHAR(128),
@NOBLOB BIT = 0,
@NOBIN BIT = 0,
@EXCLUDE_PATTERN NVARCHAR(128) = '',
@ESCAPE_PATTERN NCHAR(1) = '',
@ALIAS NVARCHAR(128))
RETURNS NVARCHAR(max)
AS
/******************************************************************************
* fonction de listage des colonnes d'une table *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-10 *
*******************************************************************************
* Cette fonction calcule la lisre des colonnes d'une table *
******************************************************************************/
BEGIN
DECLARE @SQL VARCHAR(MAX);
SELECT @SQL = '';
SELECT @SQL = @SQL + COALESCE(@ALIAS +'.', '') + COLUMN_NAME +', '
FROM
(
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TAB
AND TABLE_SCHEMA = COALESCE(@SCH, 'dbo')
AND DATA_TYPE =
CASE
WHEN @NOBLOB = 1
AND DATA_TYPE IN ('text', 'ntext', 'image',
'varchar(max)', 'nvarchar(max)', 'varbinary(max)',
'hierarchyid', 'geometry', 'geography',
'sql_variant', 'xml', 'timestamp', 'rowversion')
THEN ''
WHEN @NOBIN = 1
AND DATA_TYPE IN ('binary', 'varbinary', 'varbinary(max)', 'image')
THEN ''
ELSE DATA_TYPE
END
INTERSECT
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TAB
AND TABLE_SCHEMA = COALESCE(@SCH, 'dbo')
AND DATA_TYPE IN (SELECT TDT_DATATYPE
FROM S_TRK.T_TRACKED_DATATYPES_TDT)
INTERSECT
SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TAB
AND TABLE_SCHEMA = COALESCE(@SCH, 'dbo')
AND COLUMN_NAME NOT LIKE @EXCLUDE_PATTERN ESCAPE @ESCAPE_PATTERN
) AS T
ORDER BY ORDINAL_POSITION; ;
IF @SQL = ''
RETURN ''
RETURN SUBSTRING(@SQL, 1, LEN(@SQL) -1);
END
GO
Cette fonction est rendue nécessaire par le fait que le script suivante met en place des listes de colonnes de la table tracée dans le trigger. Pour ce faire on utilise cette fonction qui calcule automatiquement la liste des colonnes de la table en se basant sur la table INFORMATION_SCHEMA.COLUMNS et ne ne prenant que les colonnes dont le type SQL est dans la table S_TRK.T_TRACKED_DATATYPES_TDT.
2.7 Рscript g̩n̩rant les d̩clencheurs alimentant les donn̩es hors ligne
--===========================================================================--
-- 7 : script générant les déclencheurs alimentant les données hors ligne
--===========================================================================--
-- pour indenter l'écriture du déclencheur, nous utilisons la variable @CRLF
-- qui contient un "Carriage Return" et un "Line Feed"
DECLARE @CRLF NCHAR(2);
SET @CRLF = CHAR(13) + CHAR(10);
-- la requête générant les ordres SQL de création des triggers OFF LINE :
WITH
T0 AS
(
SELECT TBL_SCHEMA AS S, TBL_NAME AS T
FROM S_TRK.T_TRACKED_TABLES_TBL
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE DATA_TYPE NOT IN (SELECT TDT_DATATYPE
FROM S_TRK.T_TRACKED_DATATYPES_TDT)
AND SUBSTRING(COLUMN_NAME, 1, 4) = 'TRK_'
),
T1 AS
(
SELECT DISTINCT S, T,
CASE
WHEN LEN(S) <= 32 THEN S
ELSE SUBSTRING(S, 1, 32)
END + '_' +
CASE
WHEN LEN(T) <= 64 THEN T
ELSE SUBSTRING(T, 1, 64)
END AS TAG
FROM T0)
SELECT 'CREATE TRIGGER E_U_' + TAG +'_TRACK_OFFLINE ' + @CRLF
+ 'ON ' + S + '.' + T + @CRLF
+ 'FOR UPDATE ' + @CRLF
+ 'AS ' + @CRLF
+ '/******************************************************************************' + @CRLF
+ '* Trigger d''historisation des données à l''insertion. (métadonnées + XML) *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Déclencheur FOR DELETE créé automatiquement *' + @CRLF
+ '******************************************************************************/' + @CRLF
+ 'INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC ' + @CRLF
+ ' (TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE, ' + @CRLF
+ ' /* partie spécifique à MS SQL Server : */' + @CRLF
+ ' TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME, ' + @CRLF
+ ' TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME, ' + @CRLF
+ ' TKC_NT_DOMAIN, TKC_NT_USER, ' + @CRLF
+ ' /* fin de la partie spécifique à MS SQL Server */' + @CRLF
+ ' TKC_XML_DATA)' + @CRLF
+ 'SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 0, ' + @CRLF
+ ' client_net_address, local_net_address, host_name, ' + @CRLF
+ ' program_name, login_name, original_login_name, ' + @CRLF
+ ' nt_domain, nt_user_name, ' + @CRLF
+ ' CAST((SELECT *' + @CRLF
+ ' FROM (SELECT ''AVANT'' AS MOMENT, '
+ dbo.F_GET_ALL_COLUMNS (S, T, 1, 1, 'TRK?_%', '?', NULL) + @CRLF
+ ' FROM deleted' + @CRLF
+ ' UNION ALL' + @CRLF
+ ' SELECT ''APRES'' AS MOMENT, '
+ dbo.F_GET_ALL_COLUMNS (S, T, 1, 1, 'TRK?_%', '?', NULL) + @CRLF
+ ' FROM inserted) AS T' + @CRLF
+ ' FOR XML AUTO, ELEMENTS, ROOT(''SQL_DATASET'')) AS XML) ' + @CRLF
+ 'FROM S_TRK.T_TRACKED_TABLES_TBL ' + @CRLF
+ ' CROSS JOIN sys.dm_exec_connections AS EC ' + @CRLF
+ ' INNER JOIN sys.dm_exec_sessions AS ES ' + @CRLF
+ ' ON EC.session_id = ES.session_id ' + @CRLF
+ 'WHERE TBL_SCHEMA = ''' + S + ''' ' + @CRLF
+ ' AND TBL_NAME = ''' + T + ''' ' + @CRLF
+ ' AND EC.session_id = @@SPID; ' + @CRLF
FROM T1
UNION ALL
SELECT 'CREATE TRIGGER E_D_' + TAG +'_TRACK_OFFLINE ' + @CRLF
+ 'ON ' + S + '.' + T + @CRLF
+ 'FOR DELETE' + @CRLF
+ 'AS' + @CRLF
+ '/******************************************************************************' + @CRLF
+ '* Trigger d''historisation des données à la suppression (métadonnées + XML) *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *' + @CRLF
+ '*******************************************************************************' + @CRLF
+ '* Déclencheur FOR DELETE créé automatiquement *' + @CRLF
+ '******************************************************************************/' + @CRLF
+ 'INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC ' + @CRLF
+ ' (TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE, ' + @CRLF
+ ' /* partie spécifique à MS SQL Server : */' + @CRLF
+ ' TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME, ' + @CRLF
+ ' TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME, ' + @CRLF
+ ' TKC_NT_DOMAIN, TKC_NT_USER, ' + @CRLF
+ ' /* fin de la partie spécifique à MS SQL Server */' + @CRLF
+ ' TKC_XML_DATA)' + @CRLF
+ 'SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 1, ' + @CRLF
+ ' client_net_address, local_net_address, host_name, ' + @CRLF
+ ' program_name, login_name, original_login_name, ' + @CRLF
+ ' nt_domain, nt_user_name, ' + @CRLF
+ ' CAST((SELECT *' + @CRLF
+ ' FROM (SELECT ''AVANT'' AS MOMENT, '
+ dbo.F_GET_ALL_COLUMNS (S, T, 1, 1, 'TRK?_%', '?', NULL) + @CRLF
+ ' FROM deleted) AS T' + @CRLF
+ ' FOR XML AUTO, ELEMENTS, ROOT(''SQL_DATASET'')) AS XML)' + @CRLF
+ 'FROM S_TRK.T_TRACKED_TABLES_TBL ' + @CRLF
+ ' CROSS JOIN sys.dm_exec_connections AS EC ' + @CRLF
+ ' INNER JOIN sys.dm_exec_sessions AS ES ' + @CRLF
+ ' ON EC.session_id = ES.session_id ' + @CRLF
+ 'WHERE TBL_SCHEMA = ''' + S + ''' ' + @CRLF
+ ' AND TBL_NAME = ''' + T + ''' ' + @CRLF
+ ' AND EC.session_id = @@SPID; ' + @CRLF
FROM T1;
GO
-- le résultat de ce script doit être exécuté.
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
TEST : 2e partie
Quelques mises à jour pour s’amuser avec l’historisation des données (quelques insertions, une mise à jour et des suppressions, le tout sous différents utilisateurs SQL)
-- création d'utilisateurs SQL et leurs privilèges pour test :
CREATE LOGIN L_123 WITH PASSWORD = '123'
CREATE LOGIN L_456 WITH PASSWORD = '456'
CREATE USER U_123 FOR LOGIN L_123;
CREATE USER U_456 FOR LOGIN L_456;
EXEC sp_addsrvrolemember 'L_123', 'sysadmin';
EXEC sp_addsrvrolemember 'L_456', 'sysadmin';
EXEC sp_addrolemember 'db_owner', 'U_123';
EXEC sp_addrolemember 'db_owner', 'U_456';
-- <strong>SE CONNECTER AVEC LE COMPTE L_123</strong> :
INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_OBSERVATIONS)
VALUES ('DUPONT', 'peu aimable'),
('SCHMIDT', 'Fort potentiel'),
('JOHNSON', NULL);
INSERT INTO T_COMMANDE_CMD (CLI_ID, CMD_DATE, CMD_OBSERVATIONS)
VALUES (1, '2009-12-08', 'Prix serrés !'),
(1, '2009-12-12', 'Soldes à prévoir'),
(2, '2009-12-12', NULL);
UPDATE T_COMMANDE_CMD
SET CMD_DATE = '2009-12-10'
WHERE CMD_DATE = '2009-12-08';
-- SE CONNECTER AVEC LE COMPTE L_456 :
DELETE FROM T_COMMANDE_CMD
WHERE CMD_DATE = '2009-12-12';
Allons voir ce qui s’est passé :
-- dans les clients :
SELECT *
FROM T_CLIENT_CLI;
CLI_ID CLI_NOM CLI_OBSERVATIONS TRK_DH_INSERT TRK_DH_UPDATE TRK_ID_INSERT TRK_ID_UPDATE
----------- ---------------- ------------------ ----------------------- --------------- -------------- ----------------
1 DUPONT peu aimable 2009-12-13 12:18:53.543 NULL U_123 NULL
2 SCHMIDT Fort potentiel 2009-12-13 12:18:53.543 NULL U_123 NULL
3 JOHNSON NULL 2009-12-13 12:18:53.543 NULL U_123 NULL
--dans les commandes :
SELECT *
FROM T_COMMANDE_CMD
CMD_ID CLI_ID CMD_DATE CMD_OBSERVATIONS TRK_DH_INSERT TRK_DH_UPDATE TRK_ID_INSERT TRK_ID_UPDATE
----------- ----------- ---------- ------------------- ----------------------- ----------------------- --------------- ----------------
1 1 2009-12-10 Prix serrés ! 2009-12-13 12:18:53.560 2009-12-13 12:18:53.560 U_123 U_123
-- dans les changements (table S_TRK.T_TRACKED_DATACHANGES_TKC) :
SELECT *
FROM S_TRK.T_TRACKED_DATACHANGES_TKC
TKC_ID TBL_ID TKC_LOGIN_NAME TKC_USER_NAME TKC_DATETIME ... TKC_XML_DATA
--------- ----------- ---------------- ---------------- ----------------------- ... ------------------------------------------
1 1 L_123 U_123 2009-12-13 12:18:53.577 ... <SQL_DATASET>
<T>
<MOMENT>AVANT</MOMENT>
<CMD_ID>1</CMD_ID>
<CLI_ID>1</CLI_ID>
<CMD_DATE>2009-12-08</CMD_DATE>
</T>
<T>
<MOMENT>APRES</MOMENT>
<CMD_ID>1</CMD_ID>
<CLI_ID>1</CLI_ID>
<CMD_DATE>2009-12-10</CMD_DATE>
</T>
</SQL_DATASET>
2 1 L_456 U_456 2009-12-13 12:19:53.560 ... <SQL_DATASET>
<T>
<MOMENT>AVANT</MOMENT>
<CMD_ID>3</CMD_ID>
<CLI_ID>2</CLI_ID>
<CMD_DATE>2009-12-12</CMD_DATE>
</T>
<T>
<MOMENT>AVANT</MOMENT>
<CMD_ID>2</CMD_ID>
<CLI_ID>1</CLI_ID>
<CMD_DATE>2009-12-12</CMD_DATE>
</T>
</SQL_DATASET>
NOTA : nous n’avons pas ici reproduit les données des colonnes qpécifiques à SQL Server.
*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
3 – REQUÊTES GÉNÉRÉES PAR LES SCRIPTS
À titre d’exemple, voici l’ensemble des requêtes générées par ces scripts :
3.1 – Nouvelles colonnes :
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_DH_INSERT DATETIME DEFAULT GETDATE();
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_DH_INSERT DATETIME DEFAULT GETDATE();
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_DH_UPDATE DATETIME;
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_DH_UPDATE DATETIME;
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_ID_INSERT NVARCHAR(128);
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_ID_INSERT NVARCHAR(128);
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_ID_UPDATE NVARCHAR(128);
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_ID_UPDATE NVARCHAR(128);
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_DH_INSERT DATETIME DEFAULT GETDATE();
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_DH_UPDATE DATETIME;
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_DH_UPDATE DATETIME;
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_ID_INSERT NVARCHAR(128);
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_ID_INSERT NVARCHAR(128);
ALTER TABLE dbo.T_CLIENT_CLI ADD TRK_ID_UPDATE NVARCHAR(128);
ALTER TABLE dbo.T_COMMANDE_CMD ADD TRK_ID_UPDATE NVARCHAR(128);
3.2 – Déclencheurs « IN LINE » :
CREATE TRIGGER E_I_dbo_T_CLIENT_CLI_TRACK_INLINE
ON dbo.T_CLIENT_CLI
FOR INSERT
AS
/******************************************************************************
* Trigger de gestion des méta données d'insertion (utilisateur + dateheure) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR INSERT créé automatiquement *
******************************************************************************/
UPDATE dbo.T_CLIENT_CLI
SET TRK_ID_INSERT = USER,
TRK_DH_INSERT = CURRENT_TIMESTAMP
FROM dbo.T_CLIENT_CLI AS T
INNER JOIN inserted as I
ON T.CLI_ID = I.CLI_ID;
CREATE TRIGGER E_I_dbo_T_COMMANDE_CMD_TRACK_INLINE
ON dbo.T_COMMANDE_CMD
FOR INSERT
AS
/******************************************************************************
* Trigger de gestion des méta données d'insertion (utilisateur + dateheure) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR INSERT créé automatiquement *
******************************************************************************/
UPDATE dbo.T_COMMANDE_CMD
SET TRK_ID_INSERT = USER,
TRK_DH_INSERT = CURRENT_TIMESTAMP
FROM dbo.T_COMMANDE_CMD AS T
INNER JOIN inserted as I
ON T.CMD_ID = I.CMD_ID;
CREATE TRIGGER E_U_dbo_T_CLIENT_CLI_TRACK_INLINE
ON dbo.T_CLIENT_CLI
FOR UPDATE
AS
/******************************************************************************
* Trigger de gestion des méta données de modification (utilisateur+dateheure) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR UPDATE créé automatiquement *
******************************************************************************/
UPDATE dbo.T_CLIENT_CLI
SET TRK_ID_UPDATE = USER,
TRK_DH_UPDATE = CURRENT_TIMESTAMP
FROM dbo.T_CLIENT_CLI AS T
INNER JOIN inserted as I
ON T.CLI_ID = I.CLI_ID;
CREATE TRIGGER E_U_dbo_T_COMMANDE_CMD_TRACK_INLINE
ON dbo.T_COMMANDE_CMD
FOR UPDATE
AS
/******************************************************************************
* Trigger de gestion des méta données de modification (utilisateur+dateheure) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR UPDATE créé automatiquement *
******************************************************************************/
UPDATE dbo.T_COMMANDE_CMD
SET TRK_ID_UPDATE = USER,
TRK_DH_UPDATE = CURRENT_TIMESTAMP
FROM dbo.T_COMMANDE_CMD AS T
INNER JOIN inserted as I
ON T.CMD_ID = I.CMD_ID;
3.3 – Déclencheurs « OFF LINE » :
CREATE TRIGGER E_U_dbo_T_CLIENT_CLI_TRACK_OFFLINE
ON dbo.T_CLIENT_CLI
FOR UPDATE
AS
/******************************************************************************
* Trigger d'historisation des données à l'insertion. (métadonnées + XML) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR DELETE créé automatiquement *
******************************************************************************/
INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC
(TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE,
/* partie spécifique à MS SQL Server : */
TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME,
TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME,
TKC_NT_DOMAIN, TKC_NT_USER,
/* fin de la partie spécifique à MS SQL Server */
TKC_XML_DATA)
SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 0,
client_net_address, local_net_address, host_name,
program_name, login_name, original_login_name,
nt_domain, nt_user_name,
CAST((SELECT *
FROM (SELECT 'AVANT' AS MOMENT, CLI_ID, CLI_NOM, CLI_OBSERVATIONS
FROM deleted
UNION ALL
SELECT 'APRES' AS MOMENT, CLI_ID, CLI_NOM, CLI_OBSERVATIONS
FROM inserted) AS T
FOR XML AUTO, ELEMENTS, ROOT('SQL_DATASET')) AS XML)
FROM S_TRK.T_TRACKED_TABLES_TBL
CROSS JOIN sys.dm_exec_connections AS EC
INNER JOIN sys.dm_exec_sessions AS ES
ON EC.session_id = ES.session_id
WHERE TBL_SCHEMA = 'dbo'
AND TBL_NAME = 'T_CLIENT_CLI'
AND EC.session_id = @@SPID;
CREATE TRIGGER E_U_dbo_T_COMMANDE_CMD_TRACK_OFFLINE
ON dbo.T_COMMANDE_CMD
FOR UPDATE
AS
/******************************************************************************
* Trigger d'historisation des données à l'insertion. (métadonnées + XML) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR DELETE créé automatiquement *
******************************************************************************/
INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC
(TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE,
/* partie spécifique à MS SQL Server : */
TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME,
TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME,
TKC_NT_DOMAIN, TKC_NT_USER,
/* fin de la partie spécifique à MS SQL Server */
TKC_XML_DATA)
SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 0,
client_net_address, local_net_address, host_name,
program_name, login_name, original_login_name,
nt_domain, nt_user_name,
CAST((SELECT *
FROM (SELECT 'AVANT' AS MOMENT, CMD_ID, CLI_ID, CMD_DATE
FROM deleted
UNION ALL
SELECT 'APRES' AS MOMENT, CMD_ID, CLI_ID, CMD_DATE
FROM inserted) AS T
FOR XML AUTO, ELEMENTS, ROOT('SQL_DATASET')) AS XML)
FROM S_TRK.T_TRACKED_TABLES_TBL
CROSS JOIN sys.dm_exec_connections AS EC
INNER JOIN sys.dm_exec_sessions AS ES
ON EC.session_id = ES.session_id
WHERE TBL_SCHEMA = 'dbo'
AND TBL_NAME = 'T_COMMANDE_CMD'
AND EC.session_id = @@SPID;
CREATE TRIGGER E_D_dbo_T_CLIENT_CLI_TRACK_OFFLINE
ON dbo.T_CLIENT_CLI
FOR DELETE
AS
/******************************************************************************
* Trigger d'historisation des données à la suppression (métadonnées + XML) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR DELETE créé automatiquement *
******************************************************************************/
INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC
(TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE,
/* partie spécifique à MS SQL Server : */
TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME,
TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME,
TKC_NT_DOMAIN, TKC_NT_USER,
/* fin de la partie spécifique à MS SQL Server */
TKC_XML_DATA)
SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 1,
client_net_address, local_net_address, host_name,
program_name, login_name, original_login_name,
nt_domain, nt_user_name,
CAST((SELECT *
FROM (SELECT 'AVANT' AS MOMENT, CLI_ID, CLI_NOM, CLI_OBSERVATIONS
FROM deleted) AS T
FOR XML AUTO, ELEMENTS, ROOT('SQL_DATASET')) AS XML)
FROM S_TRK.T_TRACKED_TABLES_TBL
CROSS JOIN sys.dm_exec_connections AS EC
INNER JOIN sys.dm_exec_sessions AS ES
ON EC.session_id = ES.session_id
WHERE TBL_SCHEMA = 'dbo'
AND TBL_NAME = 'T_CLIENT_CLI'
AND EC.session_id = @@SPID;
CREATE TRIGGER E_D_dbo_T_COMMANDE_CMD_TRACK_OFFLINE
ON dbo.T_COMMANDE_CMD
FOR DELETE
AS
/******************************************************************************
* Trigger d'historisation des données à la suppression (métadonnées + XML) *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-12-12 *
*******************************************************************************
* Déclencheur FOR DELETE créé automatiquement *
******************************************************************************/
INSERT INTO S_TRK.T_TRACKED_DATACHANGES_TKC
(TBL_ID, TKC_LOGIN_NAME, TKC_USER_NAME, TKC_DATETIME, TKC_DELETE,
/* partie spécifique à MS SQL Server : */
TKC_CLIENT_NET_ADDRESS, TKC_LOCAL_NET_ADDRESS, TKC_HOST_NAME,
TKC_PROGRAM_NAME, TKC_LOG_NAME, TKC_ORIGINAL_LOGIN_NAME,
TKC_NT_DOMAIN, TKC_NT_USER,
/* fin de la partie spécifique à MS SQL Server */
TKC_XML_DATA)
SELECT TBL_ID, SYSTEM_USER, USER, CURRENT_TIMESTAMP, 1,
client_net_address, local_net_address, host_name,
program_name, login_name, original_login_name,
nt_domain, nt_user_name,
CAST((SELECT *
FROM (SELECT 'AVANT' AS MOMENT, CMD_ID, CLI_ID, CMD_DATE
FROM deleted) AS T
FOR XML AUTO, ELEMENTS, ROOT('SQL_DATASET')) AS XML)
FROM S_TRK.T_TRACKED_TABLES_TBL
CROSS JOIN sys.dm_exec_connections AS EC
INNER JOIN sys.dm_exec_sessions AS ES
ON EC.session_id = ES.session_id
WHERE TBL_SCHEMA = 'dbo'
AND TBL_NAME = 'T_COMMANDE_CMD'
AND EC.session_id = @@SPID;
4 – AMÉLIORATIONS DU SYSTÈME :
4.1 Р̩volution de la structure des tables :
Il faut penser que les tables peuvent évoluer, par exemple l’ajout de colonne ou le retrait est toujours possible dans la vie d’une base de données. C’est pourquoi, si votre SGBDR supporte les déclencheurs DDL comme SQL Server par exemple, alors il est facile d’implanter des triggers se déclenchant sur ALTER TABLE ou DROP TABLE, qui supprimment et replace les colonnes et déclencheur d’hitorisation.
4.2 – meilleure présentation des données de l’UPDATE :
Une meilleure présentation du XML montrant les différences dans la modification (UPDATE) serait de grouper les données avant et après en deux items à même niveau de l’arborescen XML.
Ceci peut être fait en considérant que chacune des requêtes sur les pseudo table INSERTED et DELETE produisent leur propre XML dont les racines serait AVANT et APRES, et que l’on peut concaténer comme suit :
@XMLAVANT.modify(' insert sql:variable("@XMLAPRES") into (/)[1]')
Mais il faut penser y rajouter la racine avec :
DECLARE @XMLROOT XML
SET @XMLROOT = '<DATASET></DATASET>'
SET @XMLROOT.modify(' insert sql:variable("@XMLAVANT") into (/DATASET)[1]')
SELECT @XMLROOT
SET @XMLROOT = '<DATASET></DATASET>'
SET @XMLROOT.modify(' insert sql:variable("@XMLAVANT") into (/DATASET)[1]')
SELECT @XMLROOT
5 – LIMITES DU SYSTÈME
Le tracking reposant sur l’utilisation des utilisateurs SQL, il faut créer un utilisateur SQL et sa connexion par personnes susceptible d’utiliser l’application.
Toutes les tables auditées doivent avoir une clef primaire.
6 – CORRECTIONS À APPORTER POUR DES BASES NON MS SQL Server
6.1 : les pseudos tables inserted et deleted sont spécifiques à MS SQL Server. Veuillez utiliser un alias spécifique, ou aliassez les pseudo tables des trigger « PER STATEMENT » inserted et delete.
6.2 : les SGBDR comme MySQL qui ne supportent pas les CTE doivent utiliser des vues ou des sous requêtes en tables dérivées.
6.3 : retirer les éléments de tracking de méta données spécifique à SQL server (colonnes de la table de suivi, jointure dans la requête produisant le trigger OFF LINE, filtre sur @SPID…).
7 – BONUS
--===========================================================================--
<strong>-- 0 : script de création des objets technique nécessaires à l'historisation --</strong>
--===========================================================================--
USE master;
GO
IF EXISTS(SELECT *
FROM sys.databases
WHERE name = 'DB_TEST_TRACK')
BEGIN
ALTER DATABASE DB_TEST_TRACK
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DB_TEST_TRACK;
END;
GO
CREATE DATABASE DB_TEST_TRACK;
GO
USE DB_TEST_TRACK;
GO
PS : Santé Service est la plus importante association à but non lucratif d’HAD (Hospitalisation À Domicile). Elle soigne en moyenne 1200 patients en ÃŽle de France. C’est aussi une des plus ancienne : elle a été fondée en 1958 sur l’initiative de médecins de l’Institut Gustave Roussy spécialisé dans les soins du cancer.
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
Bonjour,
Article très intéressant !
Que pensez vous de la fonction Track Data Changes incluse dans la version de SQLServer 2012 ?
Cordialement
En fait je m’aperçois que les étapes 2.5 / 2.7 ne génère pas de déclencheur ni sur mes tables ni sur la base….
Bonjour,
J’ai reproduit vos instructions dans une base de test sur SQL Serveur 2008, mais à la partie TEST après les insertions et les updates je n’ai aucune donnée dans T_TRACKED_DATACHANGES_TKC.
Faut il se connecter avec un utilisateur spécifique pour voir les données de cette table ?