Discussion :

[abderraouf90]

bonjour tout le monde
je suis entrain d'apprendre le langage SQL sous SQL serveur, j'ai crée une table étudiant composé de 5 colonne (matricule, nom, prénom, adresse et spécialité).
j'ai besoin d'une procédure stocké ou un trigger qui permet lors de l'insertion d'un nouveau étudiant de générer automatiquement le matricule composé de (lettre/ année actuelle/ numéro séquentiel)
exemple :
matricule de l'étudiant 1(etud/2016/001).
matricule de l'étudiant 2(etud/2016/002).
matricule de l'étudiant 3(etud/2016/003).
matricule de l'étudiant 4(etud/2017/001).
matricule de l'étudiant 5(etud/2017/002).
matricule de l'étudiant 6(etud/2018/001).
merci pour votre aide.

[SQLpro]

Cela se fait très bien avec les fonctions :
LEFT => extraction des n premières lettres du nom
YEAR => obtention de l'année courante
ROW_NUMBER() OVER(...) => numérotation de lignes
FORMAT => pour formater un nombre en chaine en y ajoutant, par exemple, des 0
CONCAT => pour concaténer les trois parties.

Le mieux serait un déclencheur. Quelque chose du genre :

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
CREATE TRIGGER E_IU_ETUDIANT
   ON dbo.etudiant
   FOR INSERT
AS
SET NOCOUNT ON;
WITH T AS
(SELECT FORMAT(YEAR(GETDATE()), ...) AS AN,
        MAX(CAST(RIGHT(MATRICULE, ...) AS INT)) AS LAST_ID
 FROM   dbo.etudiant
 WHERE  SUBSTRING(MATRICULE, ...) = YEAR(GETDATE())
UPDATE  dbo.etudiant
SET     MATRICULE = CONCAT(LEFT(nom, ...), AN, FORMAT(ROW_NUMBER() OVER(...) + LAST_ID, ...))
FROM    dbo.etudiant AS T
        JOIN inserted AS i
             ON T.??? = i.???;

A +

[elsuket]

Bonjour,

Vous-est il obligatoire de coder une procédure stockée ou un trigger ?
Si non, vous pouvez utiliser une colonne calculée, éventuellement persistée, ou une vue.

@++

[hmira]

Bonjour,

- Le trigger fait partie intégrante de la transaction que celle-ci soit implicite ou explicite.
Aussi, la méthode proposée par SQLPro mettant en ouvre un Tigger After Insert avec des CTE enrôlées dans la transaction, permet effectivement d'attribuer des numéros de matricules uniques par étudiant et par année. J'ai déjà testé et validé à maintes reprises cette méthode en simulant par exemple 100 threads simultanés d'Insert réitérés 20 fois chacun soit au total 2000 Insert simultanés sous des connexions séparées avec ou sans Pooling.
Cette méthode est donc robuste et sûr, c.à.d. aucune erreur et aucune clé matricule attribuée en double.

- Attention, en revanche si pour une raison quelconque, vous optez pour une procédure stockée pour l'attribution des numéros uniques de matricules par étudiant et par année.
L'attribution de numéro de matricule unique par étudiant et par année au travers une procédure stockée devient rapidement une affaire très délicate et subtile !
Il s'agit pourtant d'une demande fonctionnelle récurrente (Compteur relatif), mais dont la solution s'avère souvent très délicate.
En effet, il vous faudra définir une table des compteurs, par exemple une ligne compteur par année civile puis jouer avec les niveaux d'isolation des transactions (repeatable read, serializable), seul moyen permettant d'obtenir des numéros uniques par étudiant et par année civile, tout en incrémentant le compteur de l'année considérée. Dans le cas contraire, si vous ne prenez pas ces précautions, vous aurez fatalement des violations de la contraintes de clé unique établie sur le numéro matricule.
SQLPro avait publié un article très intéressant sur le sujet, article ci-dessous que je vous invite à lire.
Réaliser des compteurs relatifs, avec SQL Server
https://blog.developpez.com/sqlpro/p...atifs_avec_sql

- Quant à la solution proposée par elsuket consistant à utiliser une colonne calculée, éventuellement persistante, ou une vue, ces deux solutions me paraissent très séduisantes, mais j'avoue que je n'ai pas testé ni validé la faisabilité de ces 2 méthodes pour l'attribution des clés uniques relatives.
Elsuket, si tu as des exemples complets de mise en œuvre pour les 2 méthodes que tu proposes respectivement colonne calculée et Vue ou si tu as publié des articles sur le sujet, je suis preneur. Cela m'intéresse .

Merci.

A+