[MsSQL2K5] Second champs autoincrémenté dans une table

Version imprimable

13/11/2008, 12h16
narutobaka

[MsSQL2K5] Second champs autoincrémenté dans une table

Bonjour,

Je suis en train de chercher une solution qui me permettrai de rendre indépendant l'identification d'un bon papier de son identification informatique dans la base de donnée.

Je voulais savoir si il est possible de créer un deuxième champs autoincrémenté dans une table. Mon objectif est de pouvoir numéroté mes bons et de réinitialiser cette numérotation tous les ans sans toucher à la clé primaire de ma table qui elle continuerai son incrémentation normallement.

++

Citation:

Je voulais savoir si il est possible de créer un deuxième champs autoincrémenté dans une table.

Non.

Citation:

Mon objectif est de pouvoir numéroté mes bons et de réinitialiser cette numérotation tous les ans sans toucher à la clé primaire de ma table qui elle continuerai son incrémentation normallement.

Vous pouvez faire cela dans une vue. C'est à cela que servent les vues ! Sinon vous aller faire de la redondance...

Exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE T_BON 
(BON_ID      INTEGER NOT NULL IDENTITY PRIMARY KEY,
 BON_DATE    DATETIME)
 
INSERT INTO T_BON VALUES ('20051118')
INSERT INTO T_BON VALUES ('20051230')
INSERT INTO T_BON VALUES ('20051231')
INSERT INTO T_BON VALUES ('20060102')
INSERT INTO T_BON VALUES ('20060112')
INSERT INTO T_BON VALUES ('20060220')
INSERT INTO T_BON VALUES ('20060606')
INSERT INTO T_BON VALUES ('20060817')
INSERT INTO T_BON VALUES ('20070103')

la requête suivant vous donne l'effet escompté !

Code:

1
2
3
4
5
6
7
8
SELECT BOUT.*, 
       BON_ID - (SELECT MIN(BON_ID)
                 FROM   T_BON
                 WHERE  BON_DATE = (SELECT MIN(BON_DATE)
                                    FROM   T_BON
                                    WHERE  YEAR(BON_DATE) 
                                        >= YEAR(BOUT.BON_DATE))) + 1 AS BON_NUM
FROM   T_BON AS BOUT

Résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
BON_ID      BON_DATE                                               BON_NUM     
----------- ------------------------------------------------------ ----------- 
1           2005-11-18 00:00:00.000                                1
2           2005-12-30 00:00:00.000                                2
3           2005-12-31 00:00:00.000                                3
4           2006-01-02 00:00:00.000                                1
5           2006-01-12 00:00:00.000                                2
6           2006-02-20 00:00:00.000                                3
7           2006-06-06 00:00:00.000                                4
8           2006-08-17 00:00:00.000                                5
9           2007-01-03 00:00:00.000                                1

Dès lors vous pouvez transformer ce SELECT en vue et n'utiliser jamais que cette vue dans vos applications.

A +

13/11/2008, 14h08
narutobaka

bonjour,

et merci pour ta réponse

Citation:

Vous pouvez faire cela dans une vue

Les vues correspondent bien à une "table" contenant le résultat d'une requête?

J'avoue honnêtement ne pas comprendre la requête.

C'est surtout l'élément de table BOUT qui me bloque dans la compréhension de cette requête. Je ne comprend pas trop à quelle élément il fait référence.

Peux-tu m'en dire plus sur cette élément BOUT ?

merci d'avance ;)
13/11/2008, 16h17
SQLpro
Code:

1 2 ... FROM T_BON AS BOUT
c'est le surnom, l'alias donné à la table externe (out) pour la distinguer de la table interne présente dans la sous requête. Cela permet de corréler les deux requêtes.

A +
13/11/2008, 16h57
narutobaka

Bonjour,

et merci pour ta réponse.

S'agit-il d'une copie de la table T_BON le temps de la requête pour faire le tri?

encore merci

++
13/11/2008, 17h25
SQLpro

Aucune idée. C'est le rôle de l'optimiseur SQL de choisir la stratégie optimale (d'où son nom) pour résoudre ce problème.

A +

bonjour

et merci pour ta réponse.

Apparemment ta solution est à utiliser lorsque des données sont déjà dans la base. Ai-je bien compris la démarche?

Car dans mon cas il faut que je le fasse à l'insertion, ma base n'étant pas encore en production. Et surtout que je récupère cette valeur pour l'utiliser dans mon code php.

Je suis en train de travailler sur une idée. J'ai créé une table qui me fait office de compteur par année avec trois champs :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 USE [pieces_peintes_db] GO /****** Objet*: Table [dbo].[DEV_COMPTEUR_BON] Date de génération du script*: 11/14/2008 08:22:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DEV_COMPTEUR_BON]( [id_compteur_bon] [int] IDENTITY(1,1) NOT NULL, [annee_compteur_bon] [varchar](50) COLLATE French_CI_AS NULL, [nombre_bon] [int] NULL, CONSTRAINT [PK_COMPTEUR_BON] PRIMARY KEY CLUSTERED ( [id_compteur_bon] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Mes bons étant datés à l'insertion dans la base je vérifie si l'année en cours existe dans ma table DEV_COMPTEUR_BON si c le cas j'incrémente nombre_bon sinon si on a changé d'année je crée la nouvelle entrée dans la table et fixe nombre_bon à 1. Je construirai ensuite le nom de mon bon en concaténant annee_compteur_bon et nombre_bon avec un séparateur.

Je ne l'ai pas encore finit, je la posterai plus tard. Mais sinon est-ce qu'il y aurait un élément incorrecte dans mon résonnement?

merci pour votre aide

Bonjour,

Je viens de terminer ma procédure :

Code:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Narutobaka>
-- Create date: <13/11/2008>
-- Description:	<fonction test d'incrémentation de la valeur du nombre de bon pour une année donnée>
-- =============================================
ALTER PROCEDURE [dbo].[DEV_PS_Incremente_nbre_bon_fct_annee]
	-- Add the parameters for the stored procedure here
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
DECLARE @annee varchar (50)
DECLARE @date_demandeur datetime
DECLARE @nombre_bon int
DECLARE @nom_bon varchar (50)
 
SET @date_demandeur = GETDATE();
SET @annee = YEAR (@date_demandeur);	
 
    -- Insert statements for procedure here
	IF 
	-- Si il existe déjà un compteur de bon pour l'année en cours
	(SELECT COUNT(annee_compteur_bon) 
	 FROM DEV_COMPTEUR_BON 
	 WHERE annee_compteur_bon=@annee)=1
	-- Il faut mettre à jour le nombre de bon
	BEGIN
		SET @nombre_bon =(SELECT nombre_bon
		FROM DEV_COMPTEUR_BON
		WHERE annee_compteur_bon=@annee)+1
		--PRINT @nombre_bon
		--Mise à jour du compteur
		UPDATE DEV_COMPTEUR_BON
		SET nombre_bon=@nombre_bon
		WHERE annee_compteur_bon=@annee
 
	END
 
	--Sinon on créé l'année en cours et il s'agit du premier bon de l'année
	ELSE
	BEGIN	
		SET @nombre_bon=1
		INSERT INTO DEV_COMPTEUR_BON
		(
		annee_compteur_bon,
		nombre_bon
		)
		values
		(
		@annee,
		@nombre_bon
		)
	END
	-- concatènation pour création du nom du bon
	SET @nom_bon=CAST(@nombre_bon AS VARCHAR)+'-'+CAST(@annee AS VARCHAR);
	PRINT @nom_bon
 
END

Me reste juste à l'intégrer à la procédure stockée d'insertion du bon complet.

++

14/11/2008, 10h58
SQLpro

Votre procédure est susceptible de dérailler et de fournit deux n° identiques à deux factures différentes. De plus au fur et à mesure de l'augmentation de la volumétrie elle va devenir épouvantablement lente.

A +

Bonjour

et merci pour votre réponse.

Citation:

Votre procédure est susceptible de dérailler

Comment se prémunir d'un déraillement. Ou plutôt me prémunir de l'écriture d'une valeur doublon ?
Est-ce la méthodologie qui n'est pas correcte?

Citation:

De plus au fur et à mesure de l'augmentation de la volumétrie elle va devenir épouvantablement lente

Par contre la je ne vois pas à quel volumétrie vous faites référence ?

Je ne fait pourtant que récupéré une date et incrémenté une valeur de 1 que je concatène.

Je ne reparcours pas de table.

Je suis preneur de toute explication

Le code d'insertion complet :

Code:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Narutobaka>
-- Create date: <18/08/2008>
-- Description:	<Insertion d'une nouvelle demande partie demandeur>
-- =============================================
ALTER PROCEDURE [dbo].[DEV_PS_Insert_DEMANDEUR]
	-- Add the parameters for the stored procedure here
 
@id_fournisseur int,
@id_donnee int,
@id_type_bon int,
@id_etape int,
--@date_demandeur datetime,
@codeR3 varchar(20),
@nombre int,
@nombre_pieces_urgentes int,
@numBB varchar(20),
@teinte varchar(200),
@reference varchar(200),
@delais varchar(200),
@designation_complete varchar(200),
--@observation text,
@observation varchar(5000),
@tache int,
@precision_TACHE varchar(200),
@id_UTILISATEUR varchar(50),
-- ajout le 27/10/2008
@Maj_bon_peinture int
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
-- @date_demandeur = stock la date et l'heure serveur au moment de la requete
DECLARE @date_demandeur datetime
DECLARE @annee varchar (50)
DECLARE @nombre_bon int
DECLARE @nom_bon varchar (50)
 
SET @date_demandeur = GETDATE();
SET @annee = YEAR (@date_demandeur);
 
 
-- =============================================
-- Génération du nom du bon peinture
-- 14/11/2008
-- Insert statements for procedure here
	IF 
	-- Si il existe déjà un compteur de bon pour l'année en cours
	(SELECT COUNT(annee_compteur_bon) 
	 FROM DEV_COMPTEUR_BON 
	 WHERE annee_compteur_bon=@annee)=1
	-- Il faut mettre à jour le nombre de bon
	BEGIN
		SET @nombre_bon =(SELECT nombre_bon
		FROM DEV_COMPTEUR_BON
		WHERE annee_compteur_bon=@annee)+1
		--PRINT @nombre_bon
		--Mise à jour du compteur
		UPDATE DEV_COMPTEUR_BON
		SET nombre_bon=@nombre_bon
		WHERE annee_compteur_bon=@annee
 
	END
 
	--Sinon on créé l'année en cours et il s'agit du premier bon de l'année
	ELSE
	BEGIN	
		SET @nombre_bon=1
		INSERT INTO DEV_COMPTEUR_BON
		(
		annee_compteur_bon,
		nombre_bon
		)
		values
		(
		@annee,
		@nombre_bon
		)
	END
 
	SET @nom_bon=CAST(@nombre_bon AS VARCHAR)+'-'+CAST(@annee AS VARCHAR);
-- =============================================
 
    -- Insert statements for procedure here
 
INSERT INTO DEV_BON_PEINTURE
(
id_fournisseur,
id_donnee,
id_type_bon,
id_etape,
nom_bon_peinture,
date_demandeur,
codeR3,
nombre,
nombre_pieces_urgentes,
numBB,
teinte,
reference,
delais,
designation_complete,
observation_demandeur,
tache,
precision_TACHE,
id_DEMANDEUR,
Maj_bon_peinture
)
Values
(
@id_fournisseur,
@id_donnee,
@id_type_bon,
@id_etape,
@nom_bon,
@date_demandeur,
@codeR3,
@nombre,
@nombre_pieces_urgentes,
@numBB,
@teinte,
@reference,
@delais,
@designation_complete,
@observation,
@tache,
@precision_TACHE,
@id_UTILISATEUR,
@Maj_bon_peinture
)
 
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
 
END

merci d'avance

++

14/11/2008, 13h13
SQLpro

Citation:

Comment se prémunir d'un déraillement. Ou plutôt me prémunir de l'écriture d'une valeur doublon ?
Est-ce la méthodologie qui n'est pas correcte?

Oui, il faudrait au moins encapsuler tout votre traitement dans une transaction dont le niveau d'isolation est SERIALIZABLE.

Citation:

SELECT COUNT(annee_compteur_bon)
FROM DEV_COMPTEUR_BON
WHERE annee_compteur_bon=@annee

Lit toute la table !

Citation:

SET @nombre_bon =(SELECT nombre_bon
FROM DEV_COMPTEUR_BON
WHERE annee_compteur_bon=@annee)+1

Lit une bonne partie de la table.

Et tout cela ne sert à rien puisque la méthode que je vous ais donné fait cela sans procédure qui par nature sont lente alors que les requêtes sont rapides.

mais si vous tenez absolument à instancier physiquement cette information, alors lisez l'article que j'ai écrit à ce sujet et implémentez une table de compteur relatif à l'année.
http://sqlpro.developpez.com/cours/clefs/

A +
17/11/2008, 09h00
narutobaka

Bonjour,

et merci pour ta réponse.

Citation:

encapsuler tout votre traitement dans une transaction dont le niveau d'isolation est SERIALIZABLE.

Il y a un exemple dans le lien que tu m'a donner et je vais m'en inspirer.

Citation:

la méthode que je vous ais donné fait cela sans procédure qui par nature sont lente alors que les requêtes sont rapides.

Dans la méthode que tu m'as donnée l'affectation de la valeur est bien calculé en fonction des éléments stocké dans la table.

Est-ce que je me trompe sur ce point?

Encore merci pour ton aide

cordialement