Trigger pour calculer une clé

Version imprimable

04/06/2011, 17h04
mustargus

Trigger pour calculer une clé

Es-que il ya un exemple pour calculer une clé unique et composé a l'aide d'un trigger avant l'insertion des données

La structure de ma table ( tb_Rapport )
Rapport (IDRapport, Description)

Ma clé (IDRapport) est simple de 8 chiffres

4 chiffres pour l'année en cours + 4 chiffres Numéro auto incrémenté de 1 a 9999, et chaque année est mise a un.

Exemple clé générer

2010-9998
2010-9999
2011-0001
2011-0002
sans le '-'

Merci d'avance
04/06/2011, 19h01
tfc3146

Bonsoir,

Pourquoi ne pas utiliser comme clé un simple numéro auto incrément. Je pense que vous vous compliquez la vie.

Structure de la table : IdRapport,Année,Description
05/06/2011, 07h20
Invité

Citation:

Envoyé par tfc3146

Pourquoi ne pas utiliser comme clé un simple numéro auto incrément. Je pense que vous vous compliquez la vie.

Et histoire d'avoir l'année et plus, je rajouterais un timestamp avec la date de création de la ligne.
Structure de la table : IdRapport,Année,Description, ts datetime (valeur par défaut : getdate() )
05/06/2011, 08h12
mustargus

Oui j'ai déjà pensé à cette solution, mais le problème est que les rapports sont
Ré-incrémente chaque année donc ont commence à compter de 1 chaque nouvelle année et je ne sais pas comment faire ca 8O !!

Ok j'ai dû improviser un petit peut, l'idée que je fixe le compteur a 4 chiffres n'été pas idéal donc voici mon code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ShowInsert] on [dbo].[tb_Rapport]
INSTEAD OF INSERT
 
AS
 
Declare @IDNew bigint , @IDTemp bigint , @CurrentYear bigint
 
BEGIN
 
SET @CurrentYear   = Year (Getdate())
SET @IDTemp = (  SELECT ISNULL(MAX(CAST(RIGHT(CAST(IDRapport as varchar),LEN(CAST(IDRapport as varchar))- 4) as bigint)),0)  
		 FROM [tb_Rapport] 
		 WHERE ( CAST(SUBSTRING(CAST(IDRapport as varchar), 1, 4) as bigint) =  @CurrentYear ) ) + 1
 
SET @IDNew = CAST(@CurrentYear as varchar(04)) +  CAST(@IDTemp as varchar)
 
INSERT INTO [tb_Rapport] (Description,IDRapport) SELECT  Description,CAST(@IDNew as bigint) FROM INSERTED
 
END

S'il y a des erreurs faites moi savoir svp. Merci

Il suffit tout simplement d'implémenter une vue sur votre table comportant :
1) la colonne ANNEE en SMALLINT par exemple
2) la colonne incrémentée.
La vue devant présenter la renumérotation par année.

Exemple :

la table :

Code:

1
2
3
4
5
CREATE TABLE T_INCREMENT_RUPTURE_ANNNEE_IRA
(IRA_ID           INT IDENTITY NOT NULL,
 IRA_AN           SMALLINT NOT NULL DEFAULT YEAR(CURRENT_TIMESTAMP),
 IRA_DATA         VARCHAR(32),
 CONSTRAINT PK_IRA PRIMARY KEY (IRA_ID, IRA_AN));

Un jeu d'essai :

Code:

1
2
3
4
5
6
INSERT INTO T_INCREMENT_RUPTURE_ANNNEE_IRA
VALUES (2010, 'blablabla');
GO 10 
INSERT INTO T_INCREMENT_RUPTURE_ANNNEE_IRA
VALUES (2011, 'blablabla');
GO 3

La vue :

Code:

1
2
3
4
5
6
7
8
9
CREATE VIEW V_INCREMENT_RUPTURE_ANNNEE_IRA
AS
SELECT CAST(IRA_AN AS CHAR(4)) * POWER(10, 5) + 
       1 + IRA_ID - (SELECT MIN(IRA_ID) 
                     FROM   T_INCREMENT_RUPTURE_ANNNEE_IRA AS Tin 
                     WHERE  Tin.IRA_AN = Tout.IRA_AN) AS CLEF,
       IRA_DATA
FROM   T_INCREMENT_RUPTURE_ANNNEE_IRA AS Tout;
GO

La requête :

Code:

1
2
SELECT *
FROM   V_INCREMENT_RUPTURE_ANNNEE_IRA

Le résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CLEF        IRA_DATA
----------- --------------------------------
201000001   blablabla
201000002   blablabla
201000003   blablabla
201000004   blablabla
201000005   blablabla
201000006   blablabla
201000007   blablabla
201000008   blablabla
201000009   blablabla
201000010   blablabla
201100001   blablabla
201100002   blablabla
201100003   blablabla

Dans la vue, POWER(10, 5) le 5 est le paramètre du nombre de position de la clef auto-incrémentée.
Si vous dépasser 5, pensez à caster la clef en BIGINT.

A +

Bonjour,

On peut aussi faire une table très simple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE annee_rapport
(
	annee smallint NOT NULL CONSTRAINT PKannee_rapport PRIMARY KEY
	, indice_rapport int NOT NULL
)
GO
 
ALTER TABLE annee_rapport
ADD CONSTRAINT CHK_annee_rapport__indice_apport CHECK(indice_rapport BETWEEN 0 AND 10000)
GO
 
ALTER TABLE annee_rapport
ADD CONSTRAINT DF_annee_rapport__indice_rapport DEFAULT 0 FOR indice_rapport
GO

Et écrire la procédure stockée suivante :

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
ALTER PROCEDURE ps_rapport_ajoute
	@indice int = NULL OUTPUT 
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @annee_courante smallint
	SELECT	@annee_courante = DATEPART(year, GETDATE())
 
	IF NOT EXISTS
	(
		SELECT	*
		FROM	dbo.annee_rapport
		WHERE	annee = @annee_courante
	)
	BEGIN
		INSERT INTO dbo.annee_rapport (annee)
		VALUES (@annee_courante)
	END
 
	UPDATE	dbo.annee_rapport
	SET	@indice = indice_rapport = indice_rapport + 1
	WHERE	annee = @annee_courante
 
	SELECT	@indice = CAST(CAST(@annee_courante AS char(4)) + REPLICATE('0', 5 - LEN(@indice)) AS int) + CAST(@indice AS varchar(5))
END
GO

Utilisation :

Code:

1
2
3
4
5
DECLARE @annee_indice int
 
EXEC	dbo.ps_rapport_ajoute @indice = @annee_indice OUTPUT
 
SELECT	@annee_indice

@++ ;)

06/06/2011, 08h40
mustargus

Merci beaucoup :ccool:

Aller une autre (si vous êtes en version 2005 ou plus) :

Code:

1
2
3
4
5
6
7
8
CREATE VIEW V_INCREMENT_RUPTURE_ANNNEE_IRA_2
AS
SELECT 
 CAST(IRA_AN AS CHAR(4)) + '-' 
 + LEFT('000', 4 - LEN(CAST(ROW_NUMBER() OVER (PARTITION BY IRA_AN ORDER BY IRA_ID) AS VARCHAR(4)))) 
 + CAST(ROW_NUMBER() OVER (PARTITION BY IRA_AN ORDER BY IRA_ID) AS VARCHAR(4)) AS CLEF,
 IRA_DATA
FROM T_INCREMENT_RUPTURE_ANNNEE_IRA

La même que mikedavem en plus court :
Code:

1 2 3 SELECT IRA_AN * 1e4 + ROW_NUMBER() OVER (PARTITION BY IRA_AN ORDER BY IRA_ID ASC) AS CLEF, IRA_DATA FROM dbo.T_INCREMENT_RUPTURE_ANNNEE_IRA
Edit : le '-' n'est pas à écrire dans la clef.