Discussion :

[SOPSOU]

Bonjour,
Soit la table suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE [dbo].[ENTREE](
	[IDEntree] [bigint] IDENTITY(1,1) NOT NULL,
	[RefEntree] [nchar](18) NOT NULL,
	[DateEntree] [date] NOT NULL,
	[ObjetEntree] [ntext] NOT NULL,
	[RefFournisseur] [int] NOT NULL,
 CONSTRAINT [PK_ENTREE] PRIMARY KEY CLUSTERED 
(
	[IDEntree] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

.
Cette table me permet de garder en mémoire l'entête d'une entrée en stock d'articles.
La colonne [RefEntree] doit se présenter suivant le format:
"AAAA-MM-CompteurMois-CompteurAnnee"
AAAA: Répresentera l'année en cours (Ex.: 2011)
MM: Représentera mois (Ex.: 01, 10, 12, .etc) de l'année en cours
CompteurMois: Représentera le nombre d'entrée faite dans le mois MM de l'annee AAAA
CompteurAnnee: Représentera le nombre d'entrée faite dans l'année AAAA.

CompteurMois & CompteurAnnee doit pouvoir s'incrémenter lors d'un nouveau ajout dans la table suivant le mois MM & l'année AAAA.
CompteurMois est un entier à 4 caractères CompteurAnnee à 5 caractères

Je reste à l'écoute pour des propositions, suggestions de triggers permettrant de prendre cela en compte.
NB: [RefEntree] est unique dans la table et:
AAAA et MM provient de la date que nous aurons à saisir dans la colonne: [DateEntree]

[J0r_x]

Pourquoi ne pas avoir un champ compteurmois et compteurannée dans cette table ?

[elsuket]

Bonjour,

Il y a 3 erreurs :

- toute valeur stockée dans une colonne doit être atomique (première forme normale), or ce n'est pas votre cas
- vous stockez des valeurs numériques dans une colonne dont le type permet de stocker des chaînes de caractère, ce qui n'a aucun sens
- vos stockez des valeurs qui ne contiennent pas de caractères non latins dans une colonne dont le type (nchar) est conçu pour cela.
L'embêtant c'est qu'au lieu de consommer un octet par caractère en char (qui utilise ASCII) vous en utilisez deux en nchar (qui utilise Unicode).

Résoudre la première erreur élimine les deux autres.
En fait, c'est une erreur de modélisation : il vaudrait mieux que vous ayez une table qui gère votre séquence, et référencer celle-ci par une contrainte d'intégrité dans la table ENTREE.
En faisant cela, vous évitez le trigger, qui rallonge la durée de la transaction, donc diminue la concurrence d'accès à la table ENTREE.
D'autre part la valeur du compteur par an n'a pas d'utilité (à priori), puisque c'est la somme des compteurs des mois d'une année particulière.

Voici donc pour la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE dbo.COMPTEUR_ENTREE
(
	COMPTEUR_ENTREE int identity NOT NULL CONSTRAINT PK_COMPTEUR_ENTREE PRIMARY KEY NONCLUSTERED
	, ANNEE smallint NOT NULL CONSTRAINT CHK_COMPTEUR_ENTREE__ANNEE CHECK (ANNEE >= 0)
	, MOIS tinyint NOT NULL CONSTRAINT CHK_COMPTEUR_ENTREE__MOIS CHECK (MOIS BETWEEN 1 AND 12)
	, COMPTEUR int NOT NULL CONSTRAINT DF_COMPTEUR_ENTREE__COMPTEUR DEFAULT (0)
	, CONSTRAINT UQ_COMPTEUR_ENTREE__ANNEE__COMPTEUR UNIQUE CLUSTERED (ANNEE, MOIS)
)
GO

S'il est possible que votre compteur par mois dépasse 2.147.483.647, alors changez la colonne COMPTEUR en bigint

Et la procédure stockée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE PROCEDURE dbo.usp_COMPTEUR_ENTREE_get
	@COMPTEUR int OUTPUT
	, @COMPTEUR_ENTREE int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
 
	DECLARE @ANNEE smallint
		, @MOIS tinyint
 
	SELECT	@COMPTEUR_ENTREE = COMPTEUR_ENTREE
		, @ANNEE = ANNEE
		, @MOIS = MOIS
	FROM	dbo.COMPTEUR_ENTREE
	WHERE	ANNEE = DATEPART(year, GETDATE())
	AND	MOIS = DATEPART(month, GETDATE())
 
	-- Si le mois et/ou l'année n'existent pas, on les ajoute
	IF
	(
		@ANNEE IS NULL
		OR @MOIS IS NULL
	)
	BEGIN
		INSERT	 INTO dbo.COMPTEUR_ENTREE
		(
			ANNEE
			, MOIS
			, COMPTEUR
		)
		SELECT	DATEPART(year, GETDATE())
			, DATEPART(month, GETDATE())
			, 1
 
		SELECT	@COMPTEUR = 1
			, @COMPTEUR_ENTREE = SCOPE_IDENTITY()
	END
	ELSE
	BEGIN
		-- Si cela existe, on ne fait qu'incrémenter le compteur
		UPDATE	dbo.COMPTEUR_ENTREE
		SET	@COMPTEUR = COMPTEUR = COMPTEUR + 1
		WHERE	ANNEE = @ANNEE
		AND	MOIS = @MOIS
	END
END
GO

Un exemple d'utilisation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
DECLARE @c int
	, @ce int
 
EXEC dbo.usp_COMPTEUR_ENTREE_get
	@COMPTEUR = @c OUTPUT
	, @COMPTEUR_ENTREE = @ce OUTPUT

Avant chaque modification de la table ENTREE, vous appelez la procédure stockée : elle vous la valeur pour la contrainte d'intégrité (la colonne RefEntree), et la valeur du compteur.

Au final, il vous faut changer le type de la colonne RefEntree en int :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE dbo.ENTREE
ALTER COLUMN RefEntree int

Faire l'éventuel UPDATE pour mettre cette colonne à la bonne valeur de la table dbo.COMPTEUR_ENTREE.COMPTEUR_ENTREE.

Ensuite mettre la colonne non-NULLable et mettre la référence d'intégrité :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER TABLE dbo.ENTREE
ALTER COLUMN RefEntree int NOT NULL
CONSTRAINT FK_ENTREE_RefEntree FOREIGN KEY REFERENCES dbo.COMPTEUR_ENTREE

@++

[iberserk]

toute valeur stockée dans une colonne doit être atomique (première forme normale), or ce n'est pas votre cas

Pas sur que fsmrel serait d'accord sur la forme :-), la valeur est atomique puisque ce n'est pas une séquence (sont type nchar en est la preuve).

Mais ça n’empêche pas que je suis 100% d'accord avec toi sur la dangerosité de ces pratiques fourre tout.

[SOPSOU]

Bonjour à tous,
Merci pour votre critique, cela m'a fait du bien.

[elsuket]

Pas sur que fsmrel serait d'accord sur la forme :-), la valeur est atomique puisque ce n'est pas une séquence (sont type nchar en est la preuve).

Attention : je n'ai rien contre le stockage d'une liste de valeurs dans une colonne si celle-ci dépend d'un identifiant, et ici ce n'est pas le cas.
En ce sens je me suis trompé de forme normale : c'est la seconde

Mais bon, je sais que je prêche un convaincu

@++