Discussion :

[Lionhart]

Bonjour,


Je suis actuellement en cours de réalisations de trigger (sur SQLServer) mais je trouve que ce que je fait n'est pas top.

Mon trigger ressemble à ceci :

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
 
 
create trigger TI_CommandeFournisseur
on CommandeFournisseurs
instead of Insert
as 
 
Begin
Declare @max int
Declare @prefix varchar(10);
Declare @inserted_year varchar(2)
Declare @lastcommande_year varchar(2)
Declare @res varchar(11)
 
 
select @max=max(cast((substring(NumeroCommande,6,len(NumeroCommande)-5))as int)) from CommandeFournisseurs;
select @lastcommande_year=right(Year((select top 1 DateCommande from CommandeFournisseurs order by NumeroCommande desc)),2);
select @inserted_year=right(year(CURRENT_TIMESTAMP),2);
 
		if((@inserted_year<>@lastcommande_year) OR (@max is null))
		begin
			select @max=0;
		end
		else
		begin
			select @max=@max+1;
		end
 
		select @prefix= 
					case 
						when @max<10 then '000'
						when @max<100 then '00'
						when @max<1000 then '0'
						else ''
					end		
 
		insert into CommandeFournisseurs with (TABLOCK)
		(ID,IDFournisseur,IDEtablissementLivraison,NumeroDevisFournisseur,CodeBonLivraisonRecu,CodeFactureRecu,RegleParDemandeur,
		IDPersonneDemandeur,NumeroCommande,DateCommande,IDClient,IDDevise,Commentaire,CommentaireReception,CodeEtat,AutreFournisseur,
		DelaiFournisseur,ReglementFournisseur,FaxFournisseur,TelephoneFournisseur,ContactFournisseur,LivraisonAdr1,LivraisonAdr2,LivraisonAdr3) 
 
		select ID,IDFournisseur,IDEtablissementLivraison,NumeroDevisFournisseur,CodeBonLivraisonRecu,CodeFactureRecu,RegleParDemandeur,
		IDPersonneDemandeur,@res,DateCommande,IDClient,IDDevise,Commentaire,CommentaireReception,CodeEtat,AutreFournisseur,
		DelaiFournisseur,ReglementFournisseur,FaxFournisseur,TelephoneFournisseur,ContactFournisseur,LivraisonAdr1,LivraisonAdr2,LivraisonAdr3 from inserted
 
end

Mon problème est donc la requête d'insertion qui me paraît vraiment trop longue surtout pour définir le NumeroCommande qui est le seul affecté par ce trigger...

Le champ ciblé NumeroCommande est défini à NOT NULL je ne peux donc pas faire d'UPDATE après l'insertion, je suis obligé de le définir à l'insertion d'où le INSTEAD OF.

Puis-je faire la même chose sans être obligé de lister tous les champs de ma table ?

Merci

[hmira]

Votre Trigger comporte énormément d'erreurs graves !
Il savoir, par exemple , que sous SQL Server les Trigger sont de type Statement et non pas de type Row. En d'autes termes, ils s'appliquent à un ensemble de lignes et non pas à une seule ligne (en l'occurrence plusieurs lignes la table CommandeFournisseurs peuvent être insérées à la fois ! ).

Avant de faire du bricolage ! Je vous conseille vivement de lire l'excellent article de SQLPro et tout particulièrement la paragraphe "3.2. La solution : une table des clefs"
http://sqlpro.developpez.com/cours/clefs/

A+

[Lionhart]

Je suis débutant dans le domaine.
Je cherche juste à créer un Numero unique avec la valeur que je souhaite dans la table CommandeFournisseur.
Je vais lire l'article en espérant trouver une réponse.

Sinon je ne peux pas utiliser le système de computed value ou un système de séquence (dans ce genre la : http://sqlhints.com/2013/04/14/seque...l-server-2012/) que je pourrai utiliser à l'insertion et réinitialiser ?

Il faut savoir que je cherche à générer un format de ce type : IC/140001

IC/ ne change pas
14 est l'année
0001 est un id qui doit être réinitialisé à 0 lors du changement d'année.

merci

[hmira]

..Je cherche juste à créer un Numero unique avec la valeur que je souhaite dans la table CommandeFournisseur.
Je vais lire l'article en espérant trouver une réponse.

Dans un environnement multi-utilisateurs, où l'accès aux données est utltra-concurrentiel, la génération des clés uniques constituées sur une logique "Fonctionnelle" :
Exemple :
Concaténation de l'année +
+ 2 premières lettres du nom du client
+ L'âge de Jeanne-Antoinette Poisson, marquise de Pompadour
+ Max(xxx)
+ etc..

est une affaire très délicate et très subtile. Et, j'ai vu beaucoup de développeurs se fourvoyer complètement sur le sujet ! aboutissant à des numéros de factures en double, à des numéros de commandes en double, etc. tout simplement parce qu'ils n'avaient rien compris à l'accès concurrentiel et aux niveaux d'isolation.
L'article de SQLPro, que je vous ai indiqué va sur ce point vous éclairer sur le sujet. Vous pouvez ensuite personnaliser et adapter les exemples donnés dans l'article à vos propres besoins. L'important est de "distribuer" des clés uniques (dans votre cas, il s'agit d'initialiser la colonne NumeroCommande de la table CommandeFournisseurs avec une clé fonctionnelle unique) et c'est pas gagné d'avance !

A+,

[Rei Ichido]

Je suis débutant dans le domaine.
Je cherche juste à créer un Numero unique avec la valeur que je souhaite dans la table CommandeFournisseur.
Je vais lire l'article en espérant trouver une réponse.

Utilisez une colonne définie en IDENTITY.
Vous la remettrez à 0 tous les ans, manifestement.

[Lionhart]

L'important est de "distribuer" des clés uniques (dans votre cas, il s'agit d'initialiser la colonne NumeroCommande de la table CommandeFournisseurs avec une clé fonctionnelle unique) et c'est pas gagné d'avance !

C'est bien pour cela que je suis ici et c'est aussi la raison pour laquelle j'essaie de gérer le NumeroCommande du côté SQL.

J'ai bien lu l'article qui consiste à créer une table qui contient l'ID max.
Ce qui me fera donc créer une table qui ne contient qu'une seule ligne et un TRIGGER FOR INSERT qui va modifier cette ligne en plus de la récupérer pour le mettre dans le numéro de commande.

Je me suis donc permis de demander ceci :

Sinon je ne peux pas utiliser le système de computed value ou un système de séquence (dans ce genre la : http://sqlhints.com/2013/04/14/seque...l-server-2012/) que je pourrai utiliser à l'insertion et réinitialiser ?

Car la séquence sur un trigger d'insertion (qui permettra de réinitialiser la séquence en cas de changement d'année) me paraît intéressante.

[Lionhart]

Utilisez une colonne définie en IDENTITY.
Vous la remettrez à 0 tous les ans, manifestement.

Le problème de ceci est le fait que je dispose déjà d'une clé primaire qui est un auto-incrément. Que je ne peux donc pas réinitialiser car clé primaire.

Et surtout ceci :

Plusieurs colonnes identité spécifiées pour la table 'test7'. Il n'est autorisé qu'une seule colonne identité par table.

[aieeeuuuuu]

Bonjour,

Quel est votre version de SQL Server ? à partir de 2012, vous pourriez utiliser les SEQUENCES.

[Lionhart]

Apparemment 2012 donc je dois pouvoir utiliser les séquences.

Donc le plus judicieux serait de faire un Trigger avec le système de séquence ou une table avec la dernière valeur enregistrée.

[aieeeuuuuu]

Vous pouvez simplement ajouter une colonne à votre table avec la sequence comme valeur par défaut.
Au besoin, ajoutez ensuite une colonne calculée (ou faites une vue) pour calculer votre code complet utilisant la sequence.
Ainsi vous pouvez même vous passer du déclencheur.

[SQLpro]

Vous m'avez demandé par mail privé de jeter un coup d’œil sur votre problème - ce qui ne se fait pas - alors que des réponses censées ont été apportée et renvoie aux articles que j'ai écrit. Je suis actuellement en vacances aux Philippines ou je fais de la plongée sous marine sur l'un des plus beau spot de plongée du monde, à savoir Apo Island ! http://en.wikipedia.org/wiki/Apo_Island

En sus il est difficile de garantir une unicité de calcul par requête de lecture (SELECT) du fait de la concurrence d'accès qui permet des lectures simultanées.

Le SELECT MAX... est évidemment une gigantesque connerie :

• pas de garantie d'unicité
• blocage de l'intégralité de la table le temps de calculer le MAX

Votre déclencheur est une hérésie :

• il ne prend pas en compte l'aspect ensembliste
• il comporte de nombreuses requêtes alors qu'une seule (de type UPDATE) aurait suffit
• vous employez explicitement un verrou ce qui non seulement n'a aucun intérêt dans le cas présent, mais en plus "gèle" le dynamisme du verrouillage.

Bref, apprenez à écrire des requêtes SQL convenablement et profitez des articles dont les URL ont été fournies

A +

[Lionhart]

Je ne vous ai pas demandé par mail privé mais par message privé de manière courtoise il me semble.
Je pensais que cette fonctionnalité était là pour contacter les personnes du forum... (qui peut-être bloqué je pense)

Le forum étant fait pour l'entraide.

Je suis actuellement en vacances aux Philippines ou je fais de la plongée sous-marine

Je ne le savais pas, non écris sur votre profil. Surtout que vous avez répondu à de nombreux topics toute la semaine

alors que des réponses censées ont été apportée et renvoie aux articles que j'ai écrit.

Oui mais, depuis votre topic (à partir duquel je vous ai contacté) j'ai vu qu'il existe un objet de type séquence dont vous ne parlez pas, donc d'où la raison pour laquelle je me suis tourné vers vous.

Le SELECT MAX... est évidemment une gigantesque connerie :

pas de garantie d'unicité
blocage de l'intégralité de la table le temps de calculer le MAX



Votre déclencheur est une hérésie :

il ne prend pas en compte l'aspect ensembliste
il comporte de nombreuses requêtes alors qu'une seule (de type UPDATE) aurait suffit
vous employez explicitement un verrou ce qui non seulement n'a aucun intérêt dans le cas présent, mais en plus "gèle" le dynamisme du verrouillage.

Je ne le savais pas, je suis là pour apprendre et pouvoir avancer donc merci pour ces quelques infos données avec élégance, simplicité et gentillesse.

Bref, apprenez à écrire des requêtes SQL convenablement et profitez des articles dont les URL ont été fournies

J'y travaille, je pense même qu'il s'agit de la raison pour laquelle je pose des questions sur le forum et me suis permis de vous contacter pour mieux comprendre les choses.

Désolé de demander l'aide à quelqu'un de plus expérimenter quand on est débutant dans le domaine.

De plus désolé si je vous ai dérangé, là n’était pas mon objectif.

Sur ce merci de votre réponse relativement sympa !

[hmira]

Bonjour,

Quel est votre version de SQL Server ? à partir de 2012, vous pourriez utiliser les SEQUENCES.

Apparemment 2012 donc je dois pouvoir utiliser les séquences.

Donc le plus judicieux serait de faire un Trigger avec le système de séquence ou une table avec la dernière valeur enregistrée.

Les séquences à mon avis ne seront pas la meilleure solution pour générer une clé fonctionnelle telle que décrit par l'auteur de cette discussion Lionhart. En effet :

1 - Il faudra une séquence par année civile parce que dans la pratique vous (ou plutôt votre application) serez amené à générer des numéro de commandes fournisseurs sur au moins 2 années civiles en parallèle.
Si votre comptable part en vacance le 25 décembre 2014 et ne revient que le 5 janvier 2015 alors qu'il n'a pas fini de saisir dans le SI, toutes les commandes de 2014, il voudra sûrement pouvoir travailler sur les 2 années civiles en parallèle (2014 et 2015). Pour cela il faudra donc 2 séquences une pour 2014 et l'autre pour 2015. Il ne faudra surtout pas réinitialiser l'unique séquence !!!

2 - Chaque année, vous devez courir après tous vos clients ( les bases de données de vos clients) pour créer la séquence pour la nouvelle année civile ! ou alors, vous donnez les droits à votre application de créer à la volée la séquence (DDL) de l'année en cours si celle-ci n'existe pas. Ce qui représente un danger pour la sécurité de votre application.

3 - Même si vous avez plusieurs séquences, vous devez ensuite recourir systématiquement à l'utilisation du SQL Dynamique pour créer des commandes fournisseurs car vous utiliserez l'une ou l'autre des séquences en fonction de l'année cible (2014, 2015 etc.).

C'est pour toutes ces raisons que personnellement j'opterais pour la solution de table de Clé telle que décrit de l'article de SQLPro. Il faudra juste créer une ligne par année civile et donc intégrer l'année dans les valeurs de la colonne NomTable) Exemple :

Code :

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

1
2
3
4
5
NomTable                          ValeurClef
--------------------------------  ----------------
CommandeFournisseurs_2014 		      7852 
CommandeFournisseurs_2015 		        14
CommandeFournisseurs_2016 		         0

Personnellement, j'ai déjà, à plusieurs reprises, mis en œuvre, cette solution éprouvée fiable et sûre, inspirée de l'article de SQLPro, et ce, dans plusieurs applications.

En effet, cette demande de numérotation unique par année civile n'est pas exceptionnelle, mais bien au contraire, il s'agit d'une demande fonctionnelle récurrente.

Les séquences ont été les bienvenues dans SQL Server 2012 (surtout pour moi qui vient d'une culture ORACLE où les séquences existaient depuis belle lurette !), Les séquences ont vraiment leur utilité mais, selon moi, dans d'autres circonstances, mais pas dans le contexte indiqué. Les séquences peuvent remplacer avantagement les clés techniques autoincrémentée (IDENTITY, etc..) lorsque les dites clés doivent se propager ou fédérer plusieurs tables.

A+

[Lionhart]

Merci beaucoup pour ton point de vue qui m'éclaire relativement bien.

Je vais voir pour mettre cette solution en place.



A+

[Lionhart]

Suite à ce que tu m'as dit j'ai fait ceci :

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
create trigger TI_Test8
on test8
for Insert
as 
begin
	declare @id int;
	declare @prefix Varchar(4);
	declare @res Varchar(11);
	declare @lastcommande_year varchar(2);
 
	select @lastcommande_year=right(Year((select top 1 date1 from test8 order by test desc)),2);
 
	if(@lastcommande_year <> right(year(CURRENT_TIMESTAMP),2))
	begin
		ALTER SEQUENCE seqtest
		RESTART WITH 0
		;
	end
 
	SELECT @id=NEXT VALUE FOR seqtest;
	select @prefix= 
				case 
					when @id<10 then '000'
					when @id<100 then '00'
					when @id<1000 then '0'
					else ''
				end		
	select @res='IC/'+right(year(Current_timestamp),2)+@prefix+cast(@id as varchar(5));
	update test8 set test = @res where id=(select id from inserted);
end

Je n'ai pas besoin de gérer les dates sur 2 ans, je ne peux pas avoir de chevauchement ou usage parallèle.


Je n'ai pas le choix de faire ceci pour vérifier les différences de date pour évaluer si je dois réinitialiser la séquence :

Code :

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

1
2
 
	select @lastcommande_year=right(Year((select top 1 date1 from test8 order by test desc)),2);

Est-ce que j'ai fait paraît cohérent ? ou y a des choses à améliorer ? ou dois-je complètement changer de mode de fonctionnement ?

[hmira]

Votre trigger comporte encore plusieurs anomalies :

- Votre Trigger est conçu comme de type row alors comme je l'ai déjà expliqué ci-haut, sous SQL Server, les triggers sont de type statement (ils s'appliquent à un ensemble de lignes et non pas à une seule ligne)

- Votre Trigger présente une faille dans la gestion des accès concurrent dans le SELECT ci-dessous

Code SQL :

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

    SELECT @lastcommande_year=RIGHT(Year((SELECT top 1 date1 FROM test8 ORDER BY test DESC)),2);

Le résultat ce Select conditionne le Restart de votre séquence. Vous avez donc un risque important qu'une session ne voit pas le numéro de commande déjà pris par une autre session et fini par réinitialiser à tort la séquence (Cf Article déjà mentionné ci-dessus de SQLPro, (Niveau d'isolation Repeatable Read, etc..), notamment lors des toutes premières commandes de l'année N+1. Elles vont se télescoper entre elles et réinitialiser la séquence à 0 et finissent par prendre le même numéro 1 !!!

Vous devez apprendre à utiliser des outils comme SQLQueryStress (http://www.datamanipulation.net/SQLQueryStress/) pour tester votre trigger dans un environnement muti-thraed, muti-utilisateurs, ..

Personnellement, je ne commanderais pas chez vous (ou chez les entreprises qui utiliseront votre application) je ne voudrais me retrouver avec grille-pain alors j'ai commandé un IPad dernière génération ! (ne soyez pas offusqué, c'est juste une petite provocation !).

A+

[Lionhart]

Personnellement, je ne commanderais pas chez vous (ou chez les entreprises qui utiliseront votre application) je ne voudrais me retrouver avec grille-pain alors j'ai commandé un IPad dernière génération ! (ne soyez pas offusqué, c'est juste une petite provocation !).

J'occupe mon poste de développeur depuis presque 3 mois (il s'agit là de mon premier emploi) sans la personne ayant commencé le développement (de 2 ans).

- Votre Trigger est conçu comme de type row alors comme je l'ai déjà expliqué ci-haut, sous SQL Server, les triggers sont de type statement (ils s'appliquent à un ensemble de lignes et non pas à une seule ligne)

En ajoutant ceci :

Code :

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

1
2
3
4
5
 
create trigger TI_Test8
on test8
for Insert
for each row

J'ai cette erreur :

Syntaxe incorrecte vers le mot clé 'FOR'.

Je vais donc me renseigner sur Niveau d'isolation, Repeatable Read etc..

Et faire une table pour le numéro commande.

Cela dit je ne vois toujours pas comment récupérer cette valeur pour la mettre dans la table de la commande à part via un trigger on insert pour faire un update du numéro de commande.

Merci

[aieeeuuuuu]

Bonjour,

SQL Server ne permet pas l'instruction FOREACH ROW dans les déclencheurs.
Il faudrait vous appuyer sur la pseudo table INSERTED, qui peut contenir plusieurs lignes (votre trigger actuel ne prend pas en compte cette éventualité)

Plus globalement :
Les remarques de hmira sont tout à fait justes, et une table des clefs serait plus évolutif... mais aussi plus compliqué à mettre en place. Cela exige de bien comprendre les mécanismes de verrouillage, niveau d'isolation des transaction,...
Si vous optez pour cette solution, il vous faudra en effet vous renseigner sur ces sujets, mais également effectuer beaucoup de tests pour bien comprendre les subtilités (et il y en a !!!)
Ce sera donc plus long et plus compliqué, mais aussi beaucoup plus enrichissant pour vous...

La solution a base de SEQUENCE est certainement plus simple à mettre en œuvre... mais vous l'avez particulièrement complexifiée
Comme je le disais, plus besoin de déclencheur.
Il me semble également inutile de vérifier à chaque insertion si l'année a changé depuis la dernière commande ! créez un job qui s’exécutera à chaque 1er janvier, et qui réinitialisera la séquence.

Un petit exemple :

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
 
CREATE SEQUENCE SEQ_CMD START WITH 1;
 
CREATE TABLE T_COMMANDE_CMD(
    CMD_ID INT NOT NULL PRIMARY KEY IDENTITY
  , CMD_NUM INT NOT NULL DEFAULT((YEAR(CURRENT_TIMESTAMP) - 2000) * 10000 + NEXT VALUE FOR SEQ_CMD)
  , CMD_REFERENCE AS 'IC/' + CAST(CMD_NUM AS VARCHAR(60))
  , AutresColonnes VARCHAR(50)
  );
 
INSERT INTO T_COMMANDE_CMD (AutresColonnes) VALUES('donnees1');
INSERT INTO T_COMMANDE_CMD (AutresColonnes) VALUES('donnees2');
INSERT INTO T_COMMANDE_CMD (AutresColonnes) VALUES('donnees3');
 
 
--1er janvier 
ALTER SEQUENCE SEQ_CMD RESTART WITH 1;
 
INSERT INTO T_COMMANDE_CMD (AutresColonnes) VALUES
('donnees4')
,('donnees5')
,('donnees6')
  ;

Une vue plutôt qu'un colonne calculée serait sans doute préférable : vous pourrez masquer la colonne CMD_NUM, et vous pourrez mieux maitriser (par le biais de déclencheur pour le coup !) la mise à jour de ces colonnes.

Par ailleurs, vous pourriez faire commencer la séquence directement à 140001, (puis à 150001 en 2015). Il faudra alors faire du SQL dynamique pour la réinitialisation, mais ça vous laissera une certaine souplesse.

[Lionhart]

Je vais voir pour mettre en place la solution avec la séquence pour commencer.

Il me semble également inutile de vérifier à chaque insertion si l'année a changé depuis la dernière commande ! créez un job qui s’exécutera à chaque 1er janvier, et qui réinitialisera la séquence.

Merci pour cette idée, y a plus qu'à trouver comment faire.


Par la suite je mettrai bien en place la 2nd solutions afin d'en apprendre plus et de faire quelque chose de plus robuste.

Merci pour toutes ces infos, y a plus qu'à réussir.

[hmira]

Bon courage. Vous allez surement y arriver ..

Juste pour Info :

Suite à cette discussion, j'ai saisi dans le carnet des doléances "Qu'est-ce qu'il manque selon vous à SQL Server ?"
une demande d'évolution concernant les séquences (voir lien ci-dessous) :
http://www.developpez.net/forums/d10...r/#post7938229

A+