Toute application informatique utilisant une base de données est appelée à travailler avec des tables de références. Ces tables contiennent des informations quasi statiques nécessaire au paramétrage du fonctionnement de l’application ou à l’enrichissement des données. Voici un article qui explique comment utilise de manière génériques de telle tables.
Code Fr̩d̩ric Brouard Рhttp://sqlpro.developpez.com Рwww.sqlspot.com Р2009-06-03
Une table de référence, c’est par exemple la table des civilités, celles des villes et de leur code postal, une table de noms de mois, de jours de semaine… Bref des listes de données quasi statiques dont on identifie les valeurs par des clefs.
Bien que les exemples de cet article soient écrits sous forme de code propre à SQL Server, il est facile de les adapter à tout bon serveur de bases de données relationnel à condition qu’il soit capable d’utiliser des vues « misajourbales » au travers de déclencheurs INSTEAD OF.
Dans notre cas, afin de rendre générique le codes des procédures, nous devons faire en sorte que toutes les tables de références soient crées strictement de la même manière. Pour cela nous utilisant une norme interne de développement (visible sur mon site d’entreprise SQL spot) qui indique que les tables de références doivent être codifiées comme suit :
1) leur nom doit commencer par « T_R_ » suivi d’un libellé explicite, et doit se terminer par un trigramme (3 lettres) précédé d’un caractère blanc souligné. Le trigramme doit être unique dans la base de données, c’est à dire qu’aucune autre table ne doit porter le même trigramme. Bien entendu les caractères utilisés doivent être ceux de la norme SQL (voir à ce propos l’article que j’ai écrit) et en particulier le trigramme ne doit pas comporter de blanc souligné.
2) la table de référence doit comporter en tout et pour tout 5 colonnes, chacune commençant par le trigramme en suffixe du nom de table. Voici la description de ces colonnes :
XXX_ID INT NOT NULL PRIMARY KEY, -- auto incrément si possible
XXX_CODE CHAR(16) NOT NULL, -- par exemple, servira de clef subrogée
XXX_LIBELLE VARCHAR(128) NOT NULL, -- par exemple, pour une description
XXX_BASE BOOLEAN NOT NULL DEFAULT 0, -- sert à interdire toute modification de la ligne
XXX_ORDRE INT -- sert à définir un ordre pré établi
Exemple :
CREATE TABLE T_R_CIVILITE_CVT
(CVT_ID INT NOT NULL IDENTITY PRIMARY KEY,
CVT_CODE CHAR(16) NOT NULL,
CVT_LIBELLE VARCHAR(128) NOT NULL,
CVT_BASE BIT NOT NULL DEFAULT 0,
CVT_ORDRE INT)
Nous vous conseillons de placer toutes vos tables de référence dans un seul et même schéma SQL distinct des autres objets de la base, par exemple un schéma SQL de nom S_REF, à l’aide de l’ordre SQL :
CREATE SCHEMA S_REF;
La colonne CVT_BASE si elle vaut 1 lors de l’insertion, indique que cette donnée ne peut plus être modifiée ni supprimée. Imaginez ce qui se passerait si vos lignes de code client attendent une telle valeur et qu’un utilisateur peu averti la supprime. Pour assurer cette protection, il faut réaliser un trigger dans chacune des tables. Travail fastidieux… sauf s’il est entrepris par SQL lui même. En fait nous allons réaliser une procédure stockée qui va dynamiquement créer ce trigger sur chacune des tables de référence. Vous commencez maintenant à comprendre pourquoi le nommage des tables et des colonnes doit être si précis !
De la même façon pour faire en sorte que la colonne XXX_CODE soit une clef subrogée, donc dotée d’une contrainte d’unicité, nous allons utiliser une procédure stockée qui réalise l’implantation de cette contraintes sur toutes les tables en une seule passe, à l’aide d’un code SQL dynamique.
Enfin, pour travailler de manière ensembliste toutes nos tables de référence d’une seul coup, nous allons créer une troisième procédure stockée qui va créer une vue en concaténant toutes les tables de référence à l’aide de l’opérateur SQL UNION. Mais pour distinguer dans la vue les lignes d’une table et celle d’une autre, nous allons rajouter à cette vue une colonne contenant le trigramme propre à chaque table. Ainsi les lignes d’une table de référence ou d’une autre seront, par ce biais, facilement distinguées dans la vue.
Pour manipuler cette vue, c’est à dire réaliser des INSERT, UPDATE et DELETE directement dans la vue, nous allons coder 3 déclencheurs INSTAED OF dont la particularité est de faire croire à la vue qu’elle peut être mise à jour. Dans ces triggers, nous dérouterons le code vers des procédures spécialisées l’une pour l’insertion ou la modification, l’autre pour la suppression. Mais comme par nature le code des déclencheurs est ensembliste dans SQL Server, nous devrons utiliser des curseurs… Mais rassurez vous ce ne sont pas des curseurs sur des tables de production, mais sur les pseudo tables contenant les images avant et après des données manipulées dans l’ordre SQL qui à déclenché le trigger… Bref, des curseurs indolores car portant sur des tables locales ayant en général peu de lignes !
Enfin, pour couronner ,le tout, nous y ajouterons une procédure de vidage de table de référence, forçant le déclencheur qui interdit de toucher aux lignes dont la colonne XXX_BASE vaut 1, et quelques fonctions pour accéder aux valeurs, tantôt en passant le code ou l’id…
Récapitulons la liste des outils qu’il nous faut créer :
– Une procédure « one shot » pour créer les triggers interdisant la modification ou la suppression des lignes avec BASE = 1
– Une procédure « one shot » pour créer les contraintes d’unicité sur la colonne CODE
РUne proc̩dure cr̩ant la vue de concat̩nation de toutes les tables de r̩f̩rence
РUne proc̩dure INSERT/UPDATE pour les r̩f̩rences
РUne proc̩dure DELETE pour les r̩f̩rences
РUn d̩clencheur INSTEAD OF INSERT sur la vue des r̩f̩rences reroutant vers la proc̩dure INSERT/UPDATE
РUn d̩clencheur INSTEAD OF UPDATE sur la vue des r̩f̩rences reroutant vers la proc̩dure INSERT/UPDATE
РUn d̩clencheur INSTEAD OF DELETE sur la vue des r̩f̩rences reroutant vers la proc̩dure DELETE
– Une procédure de vidage d’une table de référence
РUne fonction de r̩cup̩ration de libelle ref par ID + trigramme
РUne fonction de r̩cup̩ration de code ref par ID + trigramme
РUne fonction de r̩cup̩ration de libelle ref par code + trigramme
– Une fonction de récupération de l’id ref par code + trigramme
C’est partit !
Procédure « one shot » pour créer les triggers interdisant la modification ou la suppression des lignes avec BASE = 1 :
CREATE PROCEDURE dbo.P_DDL_REF_CREATE_TRIGGER
AS
/******************************************************************************
* Procédure de création automatique des trigger des tables de référence pour *
* la gestion de l'interdiction de modification ou suppression de ligne *
* lorsque xxx_BASE vaut 1 *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max), @TABLE_NAME NVARCHAR(261), @TRG NCHAR(3);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT '[' + TABLE_SCHEMA +'].[' + TABLE_NAME +']', SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
OPEN C;
FETCH C INTO @TABLE_NAME, @TRG
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'CREATE TRIGGER E_IU_REF_' + @TRG + CHAR(13) + CHAR(10)
+ 'ON ' + @TABLE_NAME + CHAR(13) + CHAR(10)
+ 'FOR UPDATE, DELETE ' + CHAR(13) + CHAR(10)
+ 'AS ' + CHAR(13) + CHAR(10)
+ '/****************************************************************************** ' + CHAR(13) + CHAR(10)
+ '* Déclencheur créé automatiquement par procédure P_DDL_REF_CREATE_TRIGGER * ' + CHAR(13) + CHAR(10)
+ '* pour la gestion de l''interdiction de modification/suppression des lignes * ' + CHAR(13) + CHAR(10)
+ '* marquées BASE = 1 dans les tables de référence * ' + CHAR(13) + CHAR(10)
+ '******************************************************************************* ' + CHAR(13) + CHAR(10)
+ '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 * ' + CHAR(13) + CHAR(10)
+ '******************************************************************************/ ' + CHAR(13) + CHAR(10)
+ 'SET NOCOUNT ON;'+ CHAR(13) + CHAR(10)
+ 'BEGIN ' + CHAR(13) + CHAR(10)
+ 'IF EXISTS(SELECT * ' + CHAR(13) + CHAR(10)
+ ' FROM INSERTED ' + CHAR(13) + CHAR(10)
+ ' WHERE ' + @TRG +'_BASE = 1) ' + CHAR(13) + CHAR(10)
+ ' BEGIN ' + CHAR(13) + CHAR(10)
+ ' ROLLBACK; ' + CHAR(13) + CHAR(10)
+ ' RAISERROR(''Table de référence ('+ @TABLE_NAME+') : La ligne que vous avez tenté de mettre à jour ne peut pas être modifié ni supprimée.'', 16, 1); ' + CHAR(13) + CHAR(10)
+ ' END ' + CHAR(13) + CHAR(10)
+ 'END ' + CHAR(13) + CHAR(10)
PRINT @SQL; --> pour information
EXEC (@SQL);
FETCH C INTO @TABLE_NAME, @TRG
END;
CLOSE C;
DEALLOCATE C;
GO
Exécution de ladite procédure :
EXEC dbo.P_DDL_REF_CREATE_TRIGGER;
GO
Procédure « one shot » pour créer les contraintes d’unicité sur la colonne CODE :
CREATE PROCEDURE dbo.P_DDL_REF_CREATE_UNIQUE
AS
/******************************************************************************
* Procédure de création automatique des contrainte d'unicité des tables de *
* référence pour la colonne xxx_CODE *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max), @TABLE_NAME NVARCHAR(261), @TRG NCHAR(3);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT '[' + TABLE_SCHEMA +'].[' + TABLE_NAME +']', SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
OPEN C;
FETCH C INTO @TABLE_NAME, @TRG
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER TABLE ' + @TABLE_NAME + ' ADD CONSTRAINT UK_' + @TRG + '_CODE UNIQUE (' + @TRG + '_CODE);'
PRINT @SQL; --> pour information
EXEC (@SQL);
FETCH C INTO @TABLE_NAME, @TRG
END;
CLOSE C;
DEALLOCATE C;
GO
Exécution de ladite procédure
GO
Procédure créant la vue de concaténation de toutes les tables de référence :
CREATE PROCEDURE dbo.P_DDL_REF_CREATE_VIEW
AS
/******************************************************************************
* Procédure de création automatique de la vue concaténant toutes les tables *
* de référence de la base *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
SET @SQL = 'CREATE VIEW S_REF.V_REF AS '+ CHAR(13) + CHAR(10);
SELECT @SQL = @SQL + 'SELECT ''' + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +''' AS REF, ' + CHAR(13) + CHAR(10)
+ SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_ID AS REF_ID, ' + CHAR(13) + CHAR(10)
+ SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_CODE AS REF_CODE, ' + CHAR(13) + CHAR(10)
+ SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_LIBELLE AS REF_LIBELLE, ' + CHAR(13) + CHAR(10)
+ SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_BASE AS REF_BASE, ' + CHAR(13) + CHAR(10)
+ SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_ORDRE AS REF_ORDRE ' + CHAR(13) + CHAR(10)
+ 'FROM [' + TABLE_SCHEMA +'].[' + TABLE_NAME +']' + CHAR(13) + CHAR(10)
+ 'UNION ALL ' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -12);
PRINT @SQL; --> pour information
EXEC (@SQL);
GO
Exécution de ladite procédure
GO
Procédure de gestion des insertions et modifications dans la vue S_REF.V_REF :
CREATE PROCEDURE S_REF.P_IU_REF @REF CHAR(3),
@ID INT,
@CODE CHAR(16),
@LIBELLE VARCHAR(128)
@BASE bit,
@ORDRE int
AS
/******************************************************************************
* Procédure de gestion de la mise à jour INSERT / UPDATE des tables de ref. *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
DECLARE @TBL NVARCHAR(261);
SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
IF @ID IS NULL
-- insertion
SET @SQL = 'INSERT INTO ' + @TBL + ' VALUES ('''
+ REPLACE(@CODE, '''', '''''') + ''', '''
+ REPLACE(@LIBELLE, '''', '''''') + ''', '
+ CAST(@BASE AS NVARCHAR(32)) + ', '
+ CAST(@ORDRE AS NVARCHAR(32)) + ');'
ELSE
-- modification
SET @SQL = 'UPDATE ' + @TBL + ' SET '
+ @REF + '_CODE = ''' + REPLACE(@CODE, '''', '''''') + ''', '
+ @REF + '_LIBELLE = ''' + REPLACE(@LIBELLE, '''', '''''') + ''', '
+ @REF + '_BASE = ' + CAST(@BASE AS NVARCHAR(32)) + ', '
+ @REF + '_ORDRE = ' +CAST(@ORDRE AS NVARCHAR(32)) + ') '
+ 'WHERE ' + @REF + '_ID = ' + CAST(@ID AS NVARCHAR(32)) +';'
EXEC (@SQL);
GO
Procédures de gestion des suppressions à travers la vue S_REF.V_REF :
CREATE PROCEDURE S_REF.P_D_REF @REF CHAR(3),
@ID INT
AS
/******************************************************************************
* Procédure de gestion de la suppression des lignes des tables de reference *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
DECLARE @TBL NVARCHAR(261);
SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
SET @SQL = 'DELETE FROM ' + @TBL + ' '
+ 'WHERE ' + @REF + '_ID = ' + CAST(@ID AS NVARCHAR(32)) +';'
EXEC (@SQL);
GO
Déclencheur INSTEAD OF INSERT sur la vue S_REF.V_REF :
CREATE TRIGGER E_REF_I
ON S_REF.V_REF
INSTEAD OF INSERT
AS
/******************************************************************************
* Déclencheur INSTEAD OF INSERT sur la vue des références *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3),@CODE CHAR(16),
@LIBELLE VARCHAR(128), @BASE bit, @ORDRE int;
DECLARE CEI CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT REF, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE
FROM INSERTED;
OPEN CEI;
FETCH CEI INTO @REF, @CODE, @LIBELLE, @BASE, @ORDRE;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC S_REF.P_IU_REF @REF, NULL, @CODE, @LIBELLE, @BASE, @ORDRE;
FETCH CEI INTO @REF, @CODE, @LIBELLE, @BASE, @ORDRE;
END;
CLOSE CEI
DEALLOCATE CEI;
END;
GO
Déclencheur INSTEAD OF UPDATE sur la vue S_REF.V_REF :
CREATE TRIGGER E_REF_U
ON S_REF.V_REF
INSTEAD OF UPDATE
AS
/******************************************************************************
* Déclencheur INSTEAD OF UPDATE sur la vue des références *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3), @ID INT, @CODE CHAR(16),
@LIBELLE VARCHAR(128), @BASE bit, @ORDRE int;
DECLARE CEU CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT REF, REF_ID, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE
FROM INSERTED;
OPEN CEU;
FETCH CEU INTO @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC S_REF.P_IU_REF @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
FETCH CEU INTO @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
END;
CLOSE CEU
DEALLOCATE CEU;
END;
GO
Déclencheur INSTEAD OF DELETE sur la vue S_REF.V_REF :
CREATE TRIGGER E_REF_D
ON S_REF.V_REF
INSTEAD OF DELETE
AS
/******************************************************************************
* Déclencheur INSTEAD OF UPDATE sur la vue des références *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3), @ID INT;
DECLARE CED CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT REF, REF_ID
FROM INSERTED;
OPEN CED;
FETCH CED INTO @REF, @ID;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC S_REF.P_D_REF @REF, @ID;
FETCH CED INTO @REF, @ID;
END;
CLOSE CED
DEALLOCATE CED;
END;
GO
Procédure de vidage d’une table outrepassant les lignes avec XXX_BASE = 1 :
CREATE PROCEDURE S_REF.P_TRUNCATE_REF @REF CHAR(3)
AS
/******************************************************************************
* Procédure de vidage d'une table de références *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
DECLARE @TBL NVARCHAR(261);
SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
AND TABLE_TYPE = 'BASE TABLE';
SET @SQL = 'ALTER TABLE ' + @TBL + ' DISABLE TRIGGER ALL; '
+'DELETE FROM ' + @TBL + '; '
+'ALTER TABLE ' + @TBL + ' ENABLE TRIGGER ALL; ';
EXEC (@SQL);
GO
Fonction récupérant un libellé à partir du trigramme + ID :
CREATE FUNCTION S_REF.F_A_PARAM_GET_VAL_BY_ID (@TRG CHAR(3), @ID INT)
RETURNS VARCHAR(128)
AS
/******************************************************************************
* Fonction de récupération d'un libellé de référence par son ID *
* et son trigramme *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(128);
IF @TRG IS NULL OR @ID IS NULL RETURN NULL;
SELECT @OUT = REF_LIBELLE
FROM S_REF.V_REF
WHERE REF = @TRG
AND REF_ID = @ID;
RETURN (@OUT);
END;
GO
Fonction récupérant un code à partir du trigramme + ID :
CREATE FUNCTION S_REF.F_A_PARAM_GET_COD_BY_ID (@TRG CHAR(3), @ID INT)
RETURNS CHAR(16)
AS
/******************************************************************************
* Fonction de récupération d'un code de référence par son ID *
* et son trigramme *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
DECLARE @OUT CHAR(16);
IF @TRG IS NULL OR @ID IS NULL RETURN NULL;
SELECT @OUT = REF_CODE
FROM S_REF.V_REF
WHERE REF = @TRG
AND REF_ID = @ID;
RETURN (@OUT);
END;
GO
Fonction récupérant un libellé à partir du trigramme + code :
CREATE FUNCTION S_REF.F_A_PARAM_GET_VAL_BY_CODE (@TRG CHAR(3), @CODE CHAR(16))
RETURNS VARCHAR(128)
AS
/******************************************************************************
* Fonction de récupération d'un libellé de référence par son code *
* et son trigramme *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
DECLARE @OUT VARCHAR(128)
IF @TRG IS NULL OR @CODE IS NULL RETURN NULL;
SELECT @OUT = REF_LIBELLE
FROM S_REF.V_REF
WHERE REF = @TRG
AND REF_CODE = @CODE;
RETURN (@OUT);
END;
GO
Fonction récupérant un ID à partir du trigramme + code :
CREATE FUNCTION S_REF.F_A_PARAM_GET_ID_BY_CODE (@TRG CHAR(3), @CODE CHAR(16))
RETURNS INT
AS
/******************************************************************************
* Fonction de récupération d'un id de référence par son code *
* et son trigramme *
*******************************************************************************
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *
******************************************************************************/
BEGIN
DECLARE @OUT INT;
IF @TRG IS NULL OR @CODE IS NULL RETURN NULL;
SELECT @OUT = REF_ID
FROM S_REF.V_REF
WHERE REF = @TRG
AND REF_CODE = @CODE;
RETURN (@OUT);
END;
GO
Maintenant à vous de jouer.
Voici par exemple comment insérer des lignes dans la table de référence des civilités à travers la vue :
INSERT INTO S_REF.V_REF ( REF, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE)
VALUES ('CVT', 'M.', 'Monsieur', 0, 1 );
INSERT INTO S_REF.V_REF ( REF, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE)
VALUES ('CVT', 'Mme.', 'Madame', 0, 2 );
INSERT INTO S_REF.V_REF ( REF, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE)
VALUES ('CVT', 'Mlle.', 'Mademoiselle', 0, 3 );
Et comment constater qu’elle y sont bien :
FROM S_REF.V_REF;
Conclusion :
Au final dans votre code client, vous n’avez plus besoin que d’une seule interface pour gérer toutes vos tables de références grâce à la généricité de cette structuration de votre base !
Le code est concis et s’exécute directement sur le serveur. Il y a peu d’aller retour, et c’est en gain important car un réseau est très lent en comparaison des processeurs d’un serveur.
C’est donc par nature déjà optimisé… Contrairement aux solutions purement clientes…
Cela réduit aussi drastiquement le nombre de lignes de code, c’est donc par nature plus fiable (moins il y a de lignes, moins il y a de bugs… c’est mathématique !).
La maintenance de l’ensemble est grandement facilité… puisqu’il n’y a à intervenir qu’en un seul point du code pour tout problème…
Y verriez-vous un seul inconvénient ?
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Bonjour,
Dans le cas de la civilité (M.,Mme) on pourrait aussi utiliser un check avec la liste des possibilités.
Ma question est donc la suivante :
Quand faut-il utiliser un check ou quand faut-il utiliser une table référence ?
C’est bluffant, j’ai implémenter la solution… en plus avec les scripts c’est un gain de temps énorme!
Merci pour ce retour
Sur le fait de faire l’inverse, c’est à dire une seule table du référentiel « divisée » en sous tables, cela revient au même, mais pose un autre problème. Pour des raisons de performances, il est intéressant de stocker les tables de référence les moins mise à jour (par exemple civilité, codes postaux…) sur un espace de stockage qui soit en lecture seule. Cela évite toute mise en place de verrous sur ces tables lors des requêtes (rappelons que même un simple SELECT pose des verrous). De plus, pour des besoins fonctionnels, certaines tables de références peuvent figurer dans des schémas SQL Différents. C’est la raison pour laquelle je préfère de multiples tables physique et une seule vue logique.
Quant au chargement préalable en mémoire, je l’ai indiqué dans mon document sur la norme de nommage http://www.sqlspot.com/Norme-de-developpement.html.
Sur les suppressions logiques, le fait de rajouter une telle colonne pose plus de problème qu’il n’en résout :
il faut dans la vue éliminer les lignes ayant été supprimées logiquement
il faut dans les requêtes avec la vue étudier si l’on doit faire des jointures internes ou externes
Pour ma part, je considère qu’il serait préférable de mettre une date d’obsolescence et de gérer dans l’IHM le fait que ce qui est obsolète ne plus être saisi.
Quand à la colonne ordre, avec un peu d’astuce elle peut servir à différentes choses. Par exemple, dans une applications, nous avions besoin de prédéfinir des granularité de temps, telles que :
VALUES -- les pas de temps de 5 minutes à 1 an (REF_ORDRE est en minute) <br />
--> année grégorienne = 365,2425 j (soit = 365 j, 5h 49m) <br />
--> que nous avons arrondis 365 j, 5h 50m pour les calculs. <br />
--> Soit encore : 543120 + 300 + 50 = 543470 <br />
--> Si bien qu'un mois fait : 45289,166666666666666666666666667 minutes <br />
('PDT', '5 MN', '5 minutes', 1, 5 ), <br />
('PDT', '6 MN', '6 minutes', 1, 6 ), <br />
('PDT', '10 MN', '10 minutes', 1, 10 ), <br />
('PDT', '12 MN', '12 minutes', 1, 12 ), <br />
('PDT', '15 MN', '15 minutes', 1, 15 ), <br />
('PDT', '20 MN', '20 minutes', 1, 20 ), <br />
('PDT', '30 MN', '30 minutes', 1, 30 ), <br />
('PDT', '1 HR', '1 heure', 1, 60 ), <br />
('PDT', '2 HR', '2 heures', 1, 120 ), <br />
('PDT', '4 HR', '4 heures', 1, 240 ), <br />
('PDT', '6 HR', '6 heures', 1, 360 ), <br />
('PDT', '12 HR', '12 heures', 1, 720 ), <br />
('PDT', '1 JR', '1 jour', 1, 1440 ), <br />
('PDT', '2 JR', '2 jours', 1, 2880 ), <br />
('PDT', '4 JR', '4 jours', 1, 5760 ), <br />
('PDT', '1 SM', '1 semaine', 1, 10080 ), <br />
('PDT', '8 JR', '8 jours', 1, 11520 ), <br />
('PDT', '15 JR', '15 jours', 1, 21600 ), <br />
('PDT', '1 MS', '1 mois', 1, 45289 ), <br />
('PDT', '2 MS', '2 mois', 1, 90578 ), <br />
('PDT', '3 MS', '3 mois', 1, 135868 ), <br />
('PDT', '6 MS', '6 mois', 1, 271735 ), <br />
('PDT', '1 AN', '1 an', 1, 543470 );
Dans cette saisie, la colonne ordre sert à stocker les multiples de minutes pour la granularité de temps choisit.
Pour gérer la disponibilité des valeurs, tu peux ne pas ajouter un champ mais utiliser l’ordre et ne pas afficher les valeurs avec un ordre = 0
Intéressant.
Tout à fait d’accord sur la nécessité des normes de nommage. En les respectant, on n’a généralement plus à chercher quel peut être le nom de tel champ.
J’ai juste pris l’habitude de suffixer au lieu de prefixer
Une petite remarque : il arrive que dans ces tables on ait à faire des suppressions logiques : « on ne va plus gérer tel type de xxx »…
Bien entendu on souhaite dans ce cas conserver l’intégrité de l’historique, mais ne plus lister l’élément désactivé.
On pourrait faire ça en ajoutant une colonne XXX_ACTIF
J’utilise une solution sensiblement identique mais directement avec une seule table, sans vue ou trigger et avec des codes internes en plus (pour pouvoir coder en dur dans l’application des constantes sans s’occuper des ids).
La table est de ce type :
CREATE TABLE REFERENTIAL
(REF_ID INT NOT NULL IDENTITY PRIMARY KEY,
REF_CODE CHAR(16) NULL,
REF_LIBELLE VARCHAR(128) NOT NULL,
REF_TYPE VARCHAR(20) NOT NULL, — Peut être un entier référençant une autre table des types de données
REF_INTERN VARCHAR(50) NOT NULL,
REF_BASE BIT NOT NULL DEFAULT 0,
REF_ORDRE INT)
INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
VALUES (‘CVT’, ‘M.’, ‘Monsieur’, 0, ‘CVT_MR’, 1 );
INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
VALUES (‘CVT’, ‘Mme.’, ‘Madame’, 0, ‘CVT_MRS’, 2 );
INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
VALUES (‘CVT’, ‘Mlle.’, ‘Mademoiselle’, 0, ‘CVT_MS’, 3 );
Après, il faut une procédure pour insérer, une pour supprimer et une pour la sélection. L’application charge la table en mémoire, au chargement (si besoin un refresh en cas de modification) et se charge de filtrer les combobox par REF_TYPE. L’application se charge de toutes les requêtes de recherche/conversion en mémoire, il n’y a plus de transfert réseau de ces données.
Commentaire un peu tardif…
S’il faut ajouter une date d’obsolescence, il faut donc déroger au « dogme » de n’avoir que 5 colonnes dans une table de référence.
De plus, peut-être qu’il faudrait également ajouter une date de début de validité d’où encore une entorse à la règle.
Je ne suis pas sûr d’avoir un éclaircissement tant de temps après l’écriture de votre article, mais quoiqu’il en soit je vous remercie de toute la doc que vous avez mise en ligne et qui est une source précieuse.
Aujourd’hui la norme SQL a statué sur l’historisation automatique des données…. À lire :
Les tables temporelles avec SQL Server