Discussion :

[hmira]

Juste pour vous montrer à titre pédagogique comment transformer votre trigger de type Row (notion qui n'existe pas sous SQL Server !)
en Trigger de type Statement.
Pour cela je reprend presque la même structure de la table T_COMMANDE_CMD et le même jeux d'essai fournis par aieeeuuuuu

Code SQL :

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
CREATE SEQUENCE SEQ_CMD2 START WITH 1;
GO
 
CREATE TABLE T_COMMANDE_CMD2(
        CMD_ID INT NOT NULL PRIMARY KEY IDENTITY
      , CMD_REFERENCE  VARCHAR(11) NULL 
      , AutresColonnes VARCHAR(50)
 );
GO 
 
CREATE TRIGGER TR_T_COMMANDE_CMD2  
ON T_COMMANDE_CMD2 
FOR INSERT
AS 
BEGIN
	UPDATE T_COMMANDE_CMD2	    
	    SET CMD_REFERENCE = 'IC/'+FORMAT(CURRENT_TIMESTAMP, 'yy') + 
		                     RIGHT(REPLICATE('0',4)+CAST( (NEXT VALUE FOR SEQ_CMD2) as varchar(4)),4) 
    FROM T_COMMANDE_CMD2 
	INNER JOIN inserted  
	  ON Inserted.CMD_ID = T_COMMANDE_CMD2.CMD_ID     
END	  
GO 
 
INSERT INTO T_COMMANDE_CMD2 (AutresColonnes)  VALUES('donnees1');
INSERT INTO T_COMMANDE_CMD2 (AutresColonnes)  VALUES('donnees2');
INSERT INTO T_COMMANDE_CMD2  (AutresColonnes) VALUES('donnees3');
 
-- 1er janvier 
ALTER SEQUENCE SEQ_CMD RESTART WITH 1;
 
INSERT INTO T_COMMANDE_CMD2(AutresColonnes) VALUES
('donnees4')
,('donnees5')
,('donnees6')
 ;

Résultat :

Code SQL :

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

1
2
3
4
CMD_ID	CMD_REFERENCE	AutresColonnes
1	IC/140001	donnees1
2	IC/140002	donnees2
3	IC/140003	donnees3

- Remarquez au passage l'utilisation de la fonction Format(..., yy). Cette fonction, très pratique, a été rajoutée depuis SQL Server 2012

- Remarquez également la jointure interne entre la table des commandes (T_COMMANDE_CMD2) et pseudo table inserted, ce qui permet d'appliquer l'Update à toutes les lignes insérées (pseudo table Inserted).

- Retenez que la pseudo table Inserted contient 0 (zéro), une ou plusieurs lignes. (et non pas une seule ligne !)

PS : C'est à titre purement pédagogique concernant l'utilisation des Triggers. Je ne valide pas la solution retenue.

A+

[Lionhart]

Bonjour,

Suite à vos avis sur la question de mes numéros de commandes j'ai utilisé la table de clé.

J'ai donc réalisé ceci :

ma table de clé :

Code :

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

1
2
3
4
5
6
create table cles 
(
Id_cle int identity constraint pk_cle primary key,
nom_cle varchar(20),
value_cle int
);

Ajout de ma clé :

Code :

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

insert into cles values('cmd_cle',0);

Le trigger (sur une table test) qui doit permettre la modification de la table commande ainsi que la mise à jour de la table de clé.

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
 
create trigger ti_test6
on test6
for insert
as
begin
 
declare @prefix varchar(5)
declare @nextcle int
declare @id int
 
 
--Usage de curseur pour parcourir la table inserted, permettant de modifier 
--l'id pour chaque ligne dans le cas où il y a plusieurs lignes insérées.
declare cur cursor fast_forward for 
    select  id 
    from    inserted 
open cur
fetch next from cur into @id
while @@fetch_status = 0   
    begin
 
	--Récupération de la dernière clé afin de créer la prochaine clé et de rajouter le préfix
	select @nextcle= value_cle+1 from cles where nom_cle='cmd_cle'
	select @prefix= 
			case 
				when @nextcle<10 then '000'
				when @nextcle<100 then '00'
				when @nextcle<1000 then '0'
				else ''
			end
 
			--Modification de la commande en cours d'insertion afin de setter le numéro de commande
			update test6 set test='IC/'+FORMAT(CURRENT_TIMESTAMP, 'yy')+@prefix+cast(@nextcle as varchar)where id=@id;
 
			--Modification de la table de clés afin de définir la dernière clé rajoutée
			update cles set value_cle=@nextcle where nom_cle='cmd_cle';
 
 
    fetch next from cur into @id
    end
close cur
deallocate cur
end

Je pense donc avoir bien fait de poser la question.

Merci pour vos réponses.

[StringBuilder]

Bonjour,

Dans votre trigger, entre le moment où vous faire le SELECT dans la table CLES, et le moment où vous faites un UPDATE avec la nouvelle valeur, il peut se passer beaucoup de choses... notamment un déclenchement en // du trigger.

Par conséquent, il faut faire un lock dans la table des CLES pour empêcher la lecture de l'ancienne valeur de la clé avant sa mise à jour.

Code :

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

SELECT VALEUR FROM CLES WITH (XLOCK ROWLOCK)  WHERE NOM = 'maclé'

A noter que sur les dernières version de SQL Server, la notion de SEQUENCE a été ajoutée : c'est spécifiquement créé pour répondre à votre problématique.

Accessoirement, vous faites un CURSEUR dans votre trigger.

Les curseurs, c'est le mal.

Par conséquent, vous devriez chercher à réécrire votre trigger sans. Vous y gagnerez énormément en performances.

[Lionhart]

Ok;

donc tout le monde n'est pas du même avis.

On m'a recommandé dans les pages précédentes d'éviter les Séquence.

J'ai donc appliqué ce qu'on m'a conseillé qui est de faire une table de gestion des clés; jusque-là je suis ok.

Je dois donc vérifier toutes les lignes insérées une à une afin d'éviter les insertions de la même clé, d'où le curseur (je ne vois pas comment faire autrement).


En cas de lock que ce passe t'il la requête est mise en attente ?

[StringBuilder]

Voici votre trigger corrigé (attention, j'ai quelques différences dans les noms des tables ainsi que dans la valeur calculée par le trigger) :

Code sql :

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
 
drop table cle;
drop table ligne;
go
 
create table cle
(
	id int identity primary key,
	nom varchar(10) not null unique,
	valeur int not null
);
 
create table ligne
(
	id int identity primary key,
	cle varchar(10) not null,
	reference varchar(50),
	donnee varchar(50)
);
go
 
create unique index uix_ligne_reference
on ligne (reference)
where reference is not null;
go
 
create trigger ins_ligne
on ligne
for insert
as
begin
	with tmp (id, reference)
	as
	(
		select inserted.id, concat(cle.nom, '_', right(concat('000', cast(cle.valeur + ROW_NUMBER() over(partition by inserted.cle order by inserted.donnee) as varchar(3))), 3)) reference
		from inserted
		inner join cle with (xlock rowlock) on cle.nom = inserted.cle
	)
	update ligne
	set ligne.reference = tmp.reference
	from ligne
	inner join tmp on tmp.id = ligne.id;
 
	update cle
	set valeur = valeur + (select count(*) from inserted where inserted.cle = cle.nom);
end;

Et le script de test :

Code sql :

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
 
insert into cle (nom, valeur) values ('cle1', 0);
insert into cle (nom, valeur) values ('cle2', 0);
 
insert into ligne (cle, donnee) values ('cle1', 'Test 1');
insert into ligne (cle, donnee) values ('cle1', 'Test 2');
insert into ligne (cle, donnee) values ('cle2', 'Test 3');
insert into ligne (cle, donnee) values ('cle2', 'Test 4');
insert into ligne (cle, donnee) values ('cle1', 'Test 5');
 
 
with inserting (cle, donnee)
as
(
	select 'cle1', 'Test 6'
	union all
	select 'cle1', 'Test 7'
	union all
	select 'cle2', 'Test 8'
	union all
	select 'cle2', 'Test 9'
	union all
	select 'cle1', 'Test 10'
)
insert into ligne (cle, donnee)
select cle, donnee
from inserting;
 
select * from ligne;
select * from cle;

Attention, dans mon cas, vu que ROW_NUMBER() commence à 1 et non à 0, afin d'éviter de faire un calcul identique, la table CLE ne contient pas "la prochaine valeur", mais "la dernière valeur".

Ceci se rapproche d'ailleurs du comportement d'une SEQUENCE : CURRVAL et NEXTVAL

[Lionhart]

Attention, dans mon cas, vu que ROW_NUMBER() commence à 1 et non à 0

Comment je peux faire pour partir de 0 ?

Code :

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

(cle.valeur + ROW_NUMBER() over(partition BY inserted.cle ORDER BY inserted.donnee) AS varchar(3))

Je vais aller voir ça car cette partie est compliqué pour moi.

[StringBuilder]

Bah tu fais commencer les valeurs de ta table "CLES" à 0 plutôt que 1

[Lionhart]

INSERT INTO cle (nom, valeur) VALUES ('cle1', 0);

Je mets donc -1 à la place de 0 ?

Aussi, que se passe-t-il en cas de lock ? Il met en attente la requête suivante ?

Si j'ai bien compris :

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
 
CREATE TRIGGER ins_ligne
ON ligne
FOR INSERT
AS
begin
 
--Création de table temporaire avec l'id de la commande inséré,
--Création de la clé unique via row number
 
	WITH tmp (id, reference)
	AS
	(
		SELECT inserted.id, concat(cle.nom, '_', RIGHT(concat('000', cast(cle.valeur + ROW_NUMBER() over(partition BY inserted.cle ORDER BY inserted.donnee) AS varchar(3))), 3)) reference
		FROM inserted
		INNER JOIN cle WITH (xlock rowlock) ON cle.nom = inserted.cle
	)
	UPDATE ligne
	SET ligne.reference = tmp.reference
	FROM ligne
	INNER JOIN tmp ON tmp.id = ligne.id;
 
	UPDATE cle
	SET valeur = valeur + (SELECT count(*) FROM inserted WHERE inserted.cle = cle.nom);
end;

Si je réinitialise en début d'année je ne vais pas avoir de problème avec ROW_NUMBER() ?
De plus je ne veut pas que mon champ clé soit présente dans ma CommandeFournisseur...

[StringBuilder]

Je mets donc -1 à la place de 0 ?

En fait, dans VALEUR, il y a la VALEUR PRECEDENTE à celle que tu vas insérer dans la table.

Donc si ton premier numéro à attribuer est "1", alors il faut mettre "0" dans la table CLES.

Aussi, que se passe-t-il en cas de lock ? Il met en attente la requête suivante ?

Oui, les requêtes suivantes sont mises en file d'attente.
Après, si tu insères des milliards de lignes et que ça dure trop longtemps, il peut y avoir un timeout dans la file d'attente. Mais si le lock ne dure que quelques secondes (ici, quelques millisecondes tout au plus) c'est totalement transparent.

Si je réinitialise en début d'année je ne vais pas avoir de problème avec ROW_NUMBER() ?

En fait, ça dépend ce que tu entends pas "réinitialise".
Si tu veux repartir de 0 chaque année, pas de souci. En revanche, tu risques d'avoir des doublons dans la table (puisque tu prends des numéros déjà attribués).
Donc attention à bien avoir une autre valeur discriminante dans ton numéro généré (l'année par exemple).

ROW_NUMBER numérote les lignes (de 1 à n) d'une requête, basée sur les lignes en cours d'insertion (table virtuelle inserted). Ensuite, on ajoute à cette numérotation la dernière clé attribuée : Donc si la dernière ligne insérée dans la table était la valeur 5 et que tu insères un lot de 3 lignes, tu te retrouves avec les numéros 6, 7 et 8.
Si tu réinitialises la table clé pour repartir de 0, il n'y aura aucun problème, au détail près que tu vas générer les valeurs 1, 2 et 3, qui existent déjà en base (donc attention si ta colonne reference est unique).

De plus je ne veut pas que mon champ clé soit présente dans ma CommandeFournisseur...

Pas de souci. Moi j'ai écris la chose de façon à illustrer l'utilisation (et incrémentation) de plusieurs compteurs à la fois, au sein d'un même lot d'insertion.
Mais tu peux sans problème adapter les requêtes pour mettre en dur le code du compteur à utiliser, comme tu l'avais fais dans ton trigger initial.

[Lionhart]

ok, je vais faire ça et prendre le temps de bien tout relire.

Merci d'avoir pris le temps de me répondre.

Cela m'a beaucoup aidé.

[Lionhart]

J'ai essayer de modifier le trigger de la manière suivante :

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
CREATE TRIGGER ins_ligne
ON ligne
FOR INSERT
AS
begin
	WITH tmp (id, reference)
	AS
	(
		SELECT inserted.id, concat('IC/',FORMAT(CURRENT_TIMESTAMP,'yy'), RIGHT(concat('000', cast(ROW_NUMBER() over(ORDER BY inserted.donnee) AS varchar(3))), 3)) reference
		FROM inserted
		INNER JOIN cle WITH (xlock rowlock) ON cle.nom = 'cle1'
	)
	UPDATE ligne
	SET ligne.reference = tmp.reference
	FROM ligne
	INNER JOIN tmp ON tmp.id = ligne.id;
 
	UPDATE cle
	SET valeur = valeur + (SELECT count(*) FROM inserted WHERE nom = 'cle1');
end;

mais j'ai des erreur lors d'insertion.

Je dois dire que je comprend pas la partie

Code :

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

concat(cle.nom, '_', RIGHT(concat('000', cast(cle.valeur + ROW_NUMBER() over(partition BY inserted.cle ORDER BY inserted.donnee) AS varchar(3))), 3))

J'ai lu que le over partitionner la requête mais dans mon cas je n'utilise qu'une seule clé donc je ne sais pas comment retirer cette partie alors que le ROW_NUMBER() en a besoin.

Impossible d'insérer une ligne de clé en double dans l'objet «*dbo.ligne*» avec un index unique «*uix_ligne_reference*». Valeur de clé dupliquée*: (IC/14001).
L'instruction a été arrêtée.

[StringBuilder]

ROW_NUMBER() permet d'attribuer à un lot de ligne une numérotation incrémentale des lignes, avec :
- possibilité de partitioner (avoir plusieurs numérotations en //) en fonction d'un critère
- un ordre déterminé pour l'incrémentation

Dans votre cas, vu que vous ne gérez qu'un seul compteur, vous pouvez virer le partitionnement, ce que vous avez fait.

Quant à l'ordre, plutôt que d'inventer un ordre arbitraire qui ne suivra pas forcément l'ordre de la clé CLUSTERED, autant se baser sur l'ID généré par la clause INSERT, d'où mon choix de prendre INSERTED.ID

Mais le problème ne vient, je pense pas de là, mais de votre requête de mise à jour du compteur en fin de traitement, il y a une erreur.

Essayez plutôt ceci :

Code sql :

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

1
2
3
4
 
UPDATE cle
	SET valeur = valeur + (SELECT count(*) FROM inserted)
WHERE nom = 'cle1';

[Lionhart]

Effectivement, j'avais oublié de fermer ma parenthèse.

Merci beaucoup, ça fonctionne nettement mieux.