Trigger Insertion instead of

Version imprimable

Voir 40 message(s) de cette discussion en une page

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:

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:

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+

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:

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:

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:

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.

01/09/2014, 11h11
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:

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.
01/09/2014, 11h46
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 ?

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:

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:

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

01/09/2014, 12h07
Lionhart

Citation:

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

Comment je peux faire pour partir de 0 ?

Code:

(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.
01/09/2014, 12h12
StringBuilder

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

Citation:

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:

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...

01/09/2014, 13h15
StringBuilder

Citation:

Envoyé par Lionhart

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.

Citation:

Envoyé par Lionhart

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.

Citation:

Envoyé par Lionhart

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).

Citation:

Envoyé par Lionhart

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.
01/09/2014, 13h24
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é.

Modification

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

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:

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.

Citation:

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.

01/09/2014, 14h29
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:

1 2 3 4 UPDATE cle SET valeur = valeur + (SELECT count(*) FROM inserted) WHERE nom = 'cle1';
01/09/2014, 14h39
Lionhart

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

Merci beaucoup, ça fonctionne nettement mieux. :P

Voir 40 message(s) de cette discussion en une page