fr en

Utiliser des champs d'audit autogérés dans les tables SQL

Bonne pratique :

SQL permet d’ajouter, aux données métiers des tables SQL, des champs d’audit permettant de recueuillir des informations relatives aux actions réalisées sur les données de ces tables.

Ces champs seront mis à jour automatiquement lors des créations ou mises à jour d’enregistrement.
Ils ne sont donc pas à gérer (alimenter) par les programmes.
C’est très pratique.

Les champs d’Audit les plus courament utilisés sont :

  • Le type de la dernière action réalisée sur l’enregistrement (Création/Mise à jour)
  • La date et heure de création de l’enregistrement
  • La date et heure de modification de l’enregistrement
  • Le profil utilisateur ayant créé l’enregistrement
  • Le profil utilisateur ayant modifié l’enregistrement
  • Le job ayant modifié l’enregistrement
  • …

Syntaxe de mise en place des champs d'audit :

Création de la table TESTAUDIT avec les champs d’audit :

CREATE TABLE ma_bibliotheque.TESTAUDIT
(
————————
— Champs metier 
————————
EMPLOYE_NOM      FOR COLUMN EMP_NOM        CHAR(20) NOT NULL,
EMPLOYE_PRENOM FOR COLUMN EMP_PRENOM CHAR(20) NOT NULL,

——————————————————————————–
— Champs Audit liés à la création de l’enregistrement
——————————————————————————–
— Utilisation de IMPLICITLY HIDDEN
— pour les masquer lors des SELECT *
——————————————————————————–
— Date de création de l’enregistrement
CREATION_TIMESTAMP  FOR COLUMN TS_CREA 
                                       TIMESTAMP      
                                       IMPLICITLY HIDDEN 
                                       NOT NULL    
                                       DEFAULT CURRENT TIMESTAMP,

— Utilisateur ayant créé l’enregistrement                           
CREATION_UTILISATEUR FOR COLUMN USER_CREA 
                                        VARCHAR(20) ALLOCATE(10) 
                                        IMPLICITLY HIDDEN
                                        NOT NULL 
                                        DEFAULT USER,               

———————————————————————
— Champs Audit liés à la mise à jour de l’enregistrement
———————————————————————
— Type de modification de l’enregistrement 
MISE_A_JOUR_TYPE       FOR COLUMN TYPE_MAJ 
                                      CHAR(1)
                                      GENERATED ALWAYS AS (DATA CHANGE OPERATION),

— Date de modification de l’enregistrement
MISE_A_JOUR_TIMESTAMP  FOR COLUMN TS_MAJ 
                                      TIMESTAMP      
                                      NOT NULL 
                                      FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,

— Utilisateur ayant effectué la modification de l’enregistrement
MAJ_UTILISATEUR        FOR COLUMN USER_MAJ 
                                     VARCHAR(128) ALLOCATE(10) 
                                     GENERATED ALWAYS AS (SESSION_USER),

— Job ayant effectué la modification de l’enregistrement
MISE_A_JOUR_JOB       FOR COLUMN MAJ_JOB VARCHAR(28)
                                     GENERATED ALWAYS AS (QSYS2.JOB_NAME)
);

 

Les colonnes d’Audit peuvent églement être ajoutées à une table déjà existante avec :

  • ALTER TABLE
  • ADD COLUMN 

ALTER TABLE ma_bibliotheque.TESTAUDIT
  ADD COLUMN DERNIERE_MAJ
  FOR COLUMN LAST_MAJ
  TIMESTAMP
  NOT NULL
  FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;

Exemples d'utilisation :

2 INSERT et 1 UPDATE sans alimentation des champs d’audit de la table :

INSERT INTO ma_bibliotheque.TESTAUDIT (EMPLOYE_NOM, EMPLOYE_PRENOM)
VALUES(‘CLEMENT’, ‘JEROME’);

INSERT INTO ma_bibliotheque.TESTAUDIT (EMPLOYE_NOM, EMPLOYE_PRENOM)
VALUES(‘AS’, ‘400’);

UPDATE ma_bibliotheque.TESTAUDIT
SET EMPLOYE_NOM = ‘IBM’, EMPLOYE_PRENOM = ‘i’
WHERE EMPLOYE_NOM = ‘AS’;

Un SELECT * sur la table n’affichera pas les colonnes d’audit relatives à la création de l’enregistrement puisqu’elles portent le paramètre IMPLICITLY HIDDEN.

SELECT * FROM ma_bibliotheque.TESTAUDIT;

On constate que les champs d’Audit liés à la mise à jour de l’enregistrement sont correctement alimentés sans qu’ils aient été mentionnés dans la requête d’insertion.

Par contre, les champs d’audit relatifs à la création seront bien affichés si le SELECT les mentionne explicitement:

SELECT
  EMPLOYE_NOM,
  EMPLOYE_PRENOM,
  CREATION_TIMESTAMP,
  CREATION_UTILISATEUR,
  MISE_A_JOUR_TYPE,
  MISE_A_JOUR_TIMESTAMP,
  MAJ_UTILISATEUR,
  MISE_A_JOUR_JOB
FROM ma_bibliotheque.TESTAUDIT;

A noter :

Les champs définis avec GENERATED ALWAS AS ne peuvente pas être mise à jour manuellement.

Exemple :
UPDATE ma_bibliotheque.TESTAUDIT SET MAJ_UTILISATEUR = ‘TOTO’ WHERE EMPLOYE_NOM = ‘CLEMENT’;
retournera l’erreur SQL -798 : 
Message : [SQL0798] La valeur ne peut pas être indiquée pour la colonne GENERATED ALWAYS USER_MAJ

Conclusion :

Très faciles à mettre en place et gérés automatiquement, les champs d’audit des tables SQL sont très utiles.
A utiliser sans modération…