Dans le monde de l’informatique d’aujourd’hui, les données sont essentielles. Cependant, fournir un accès aux données à de nombreux utilisateurs présente des risques potentiels. Pour atténuer ces risques, les gestionnaires de données doivent appliquer des outils pour suivre les modifications et les mises à jour des données. Dans cet article, nous discuterons du concept de suivi des données dans SQL Server et aborderons les sujets suivants :

• Mise en place des tables de base de données et des déclencheurs
• Déclencheurs génériques et personnalisés
• Mise en œuvre des déclencheurs
• Test et lecture des données capturées par les déclencheurs
• Meilleures pratiques pour la mise en œuvre du suivi des données

Outils de suivi des données

Il existe plusieurs façons de capturer l’activité des données dans SQL Server. Une méthode consiste à capturer les modifications des données à partir du code de l’application. Cependant, cette méthode ne capture pas les modifications apportées directement à la base de données. Une autre méthode consiste à utiliser des déclencheurs SQL pour capturer les modifications des données. Les déclencheurs peuvent capturer soit l’ensemble de la ligne de données modifiée, soit un seul champ/élément de données modifié ou mis à jour. Dans cet article, nous nous concentrerons sur l’utilisation de déclencheurs pour capturer des champs de données spécifiques.

Types de déclencheurs de suivi des données

Il existe deux types de déclencheurs pouvant être utilisés pour le suivi des données : les déclencheurs génériques simples et les déclencheurs personnalisés. Les déclencheurs génériques simples capturent toutes les modifications des données effectuées par les utilisateurs, y compris les valeurs exactes de la base de données. En revanche, les déclencheurs personnalisés fournissent des informations plus détaillées aux utilisateurs métier, telles que les noms de champ et les valeurs de données conviviales au lieu des identifiants.

Mise en place des tables de base de données et des déclencheurs

Pour commencer le suivi des données, nous devons mettre en place des tables d’audit pour capturer les activités des données. Dans cet article, nous créerons une table d’audit générique et une table d’audit personnalisée. La table d’audit générique stockera toutes les modifications des données effectuées dans la base de données, tandis que la table d’audit personnalisée fournira des informations plus spécifiques aux utilisateurs métier.

Voici un exemple de création de la table d’audit générique :

CREATE TABLE [Audit].[GenericDataTracking](
[GenericDataTrackingID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
[Type] [char](1) NOT NULL,
[TableSchema] [nchar](5) NULL,
[TableName] [nvarchar](128) NOT NULL,
[PK] [varchar](128) NOT NULL,
[FieldName] [nvarchar](128) NOT NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UpdateDate] [datetime2](7) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[Application] [nvarchar](150) NOT NULL
)

Et voici un exemple de création de la table d’audit personnalisée :

CREATE TABLE [Audit].[DataTrackingCustom](
[DataTrackingCustomID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
[ClientID] [int] NOT NULL,
[Type] [char](1) NULL,
[TableSchema] [nchar](15) NULL,
[TableName] [nvarchar](128) NULL,
[PK] [varchar](128) NULL,
[FieldName] [nvarchar](128) NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[UpdateDate] [datetime2](7) NULL,
[UserName] [nvarchar](128) NULL,
[Application] [nvarchar](150) NULL,
[tabledisplayname] [nvarchar](128) NULL,
[fielddisplayname] [nvarchar](128) NULL,
[isviewable] [bit] NOT NULL
)

Mise en œuvre des déclencheurs

Une fois les tables d’audit configurées, nous pouvons créer des déclencheurs sur les tables souhaitées pour capturer les modifications des données. Dans cet article, nous créerons un déclencheur générique simple et un déclencheur personnalisé.

Voici un exemple de déclencheur générique simple :

CREATE TRIGGER [lu].[tr_gender] ON [lu].[Gender] FOR INSERT, UPDATE, DELETE
AS
BEGIN
  -- La logique du déclencheur va ici
END

Et voici un exemple de déclencheur personnalisé :

CREATE TRIGGER [client].[tr_ClientProfile] ON [client].[ClientProfile] FOR INSERT, UPDATE, DELETE
AS
BEGIN
  -- La logique du déclencheur va ici
END

Test et lecture des données capturées par les déclencheurs

Après avoir créé les déclencheurs, nous pouvons exécuter des scénarios de test pour voir les résultats. En mettant à jour ou en supprimant des données dans les tables suivies, nous pouvons vérifier que les déclencheurs capturent correctement les modifications. Les données capturées peuvent être lues à partir des tables d’audit pour suivre l’historique des modifications des données.

Meilleures pratiques pour la mise en œuvre du suivi des données

Lors de la mise en œuvre du suivi des données dans SQL Server, il est important de suivre certaines meilleures pratiques :

• Utilisez des déclencheurs génériques simples lorsque le suivi des données doit rester interne et ne doit pas être fourni quotidiennement aux utilisateurs métier.
• Utilisez des déclencheurs personnalisés lorsque le suivi des données doit être partagé quotidiennement avec les utilisateurs métier, car ils fournissent des informations plus détaillées.
• Assurez-vous que toutes les tables ont une clé primaire définie, car les déclencheurs reposent sur les clés primaires pour suivre les modifications des données.
• Remplissez les champs nécessaires dans les tables d’audit, tels que le nom d’utilisateur de l’utilisateur effectuant la modification et le nom de l’application.
• Maintenez régulièrement les déclencheurs personnalisés pour les maintenir à jour avec les éventuelles modifications apportées aux tables.

En suivant ces meilleures pratiques, vous pouvez mettre en œuvre efficacement le suivi des données dans SQL Server et garantir la sécurité et l’intégrité de vos données.

Conclusion

Le suivi des données est un aspect essentiel de la gestion des données dans SQL Server. En mettant en place des déclencheurs et des tables d’audit, vous pouvez capturer et suivre les modifications des données effectuées par les utilisateurs. Cela vous permet de maintenir l’intégrité des données, d’identifier les risques potentiels et de fournir des informations précieuses aux utilisateurs métier. En suivant les meilleures pratiques, vous pouvez garantir l’efficacité de votre système de suivi des données.