Gestion d'une transaction

**ed222** · 21/06/2010, 11h10

Bonjour,

J'ai écrit une procédure "réception" censée enregistrer l'arrivée de produits dans un stock (table stock). Le magasin est constitué d'emplacements disposant chacun d'une capacité connue. La procédure teste la capacité de l'emplacement choisi par l'utilisateur. Si elle n'est pas suffisante, on choisit un nouvel emplacement. S'il convient, il y a mise à jour de la capacité dudit emplacement. Je rencontre un problème : si l'opérateur fait une faute de frappe (ex: article inexistant dans la table Articles, recensant les références produit), le stock n'est pas modifié (message d'erreur et tant mieux

) mais par contre la table Emplacement est tout de même mise à jour!... il y a certainement une transaction à annuler. Mais je ne sais pas comment la faire : annuler la transaction à partir du moment où il y a eu un problème dans la procédure, quel qu'il soit...

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
47
48
49
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[reception] 
	-- Paramètres de la procédure
	  @ID_stock int
	, @codearticle varchar(50)
    , @num_lot varchar(50)
    , @IDpers nchar(10)
    , @qte float
    , @empl varchar(50)
 
--Test sur la capacité de l'emplacement choisi
AS IF EXISTS (SELECT Code_emplacement FROM Emplacement WHERE Code_emplacement=@empl AND Emplacement.capacite >= @qte)
 
--1er Cas : la quantité à stocker peut être placé dans l'emplacement choisi.
 
BEGIN
 
    SET NOCOUNT ON;
 
   INSERT INTO Stock (
	ID_stock
	, Code_article
	, Num_lot
	, Code_emplacement
	, quantite
	, ID_personne)
 
	VALUES (@ID_stock,@codearticle,@num_lot,@empl,@qte,@IDpers)
 
	--mise  à jour de la capacité de l'emplacement.
	UPDATE Emplacement
	SET Capacite=Capacite-@qte WHERE Emplacement.Code_emplacement=@empl
END
 
--2ème cas : capacité de l'emplacement choisi trop petite pour accueillir la quantité voulue.
 
ELSE
BEGIN
 
RAISERROR ('choisir un autre emplacement',11,1)
 
END

merci,

Edouard.

**SQLpro** · 21/06/2010, 13h41

Il faut que vous testiez chaque ordre SQL afin de savoir s'il part en erreur et quoi faire.

D'autre part beaucoup d'erreur dans votre code :
- les erreurs utilisateur doivent avoir une sévérité de 16
- séparer le test EXISTS de l'insertion à toutes les chances d'être casse gueule. Entre les deux requêtes quelqu'un peut insérer des données et le EXISTE qui valait VRAI peut devenir faux à l'INSERT... C'est comme cela que l'on fait du surbooking sans le savoir.

Dans votre cas :

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
47
48
49
50
51
52
53
54
55
56
ALTER PROCEDURE [dbo].[reception] 
	-- Paramètres de la procédure
	  @ID_stock int
	, @codearticle varchar(50)
    , @num_lot varchar(50)
    , @IDpers nchar(10)
    , @qte float
    , @empl varchar(50);
 
SET NOCOUNT ON;
--Test INUTILE !!!!sur la capacité de l'emplacement choisi
--AS IF EXISTS (SELECT Code_emplacement FROM Emplacement WHERE Code_emplacement=@empl AND Emplacement.capacite >= @qte)
 
--1er Cas : la quantité à stocker peut être placé dans l'emplacement choisi.
 
---BEGIN
 
 
BEGIN TRANSACTION   
 
   INSERT INTO Stock (
	ID_stock
	, Code_article
	, Num_lot
	, Code_emplacement
	, quantite
	, ID_personne)
 
	SELECT @ID_stock, @codearticle, @num_lot, 
           @empl, @qte, @IDpers
    WHERE  EXISTS (SELECT * 
                   FROM   Emplacement 
                   WHERE  Code_emplacement=@empl 
                     AND  Emplacement.capacite >= @qte);
    IF @@ERROR <> 0 GOTO LBL_ERROR;
 
	--mise  à jour de la capacité de l'emplacement.
    IF @@ROWCOUNT > 0
	   UPDATE Emplacement
	   SET Capacite=Capacite-@qte 
       WHERE Emplacement.Code_emplacement=@empl;
       IF @@ERROR <> 0 GOTO LBL_ERROR;
    ELSE
    BEGIN
 
       RAISERROR ('choisir un autre emplacement',16,1)
 
    END
 
COMMIT TRANSACTION;
 
RETURN;
 
LBL_ERROR:
 
ROLLBACK TRANSACTION

A +

**ATMorphine** · 21/06/2010, 13h50

Utilise au début de la procédure :

Code :

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

1
2
3
Begin transaction
[ton code]
Commit transaction

http://msdn.microsoft.com/en-us/library/ms188929.aspx

De plus utilise la gestion d'erreur Try Catch
et dans le catch mets : rollback transaction
http://msdn.microsoft.com/fr-fr/library/ms175976.aspx

**ed222** · 21/06/2010, 14h18

Envoyé par SQLpro

Il faut que vous testiez chaque ordre SQL afin de savoir s'il part en erreur et quoi faire.

D'autre part beaucoup d'erreur dans votre code :
- les erreurs utilisateur doivent avoir une sévérité de 16
- séparer le test EXISTS de l'insertion à toutes les chances d'être casse gueule. Entre les deux requêtes quelqu'un peut insérer des données et le EXISTE qui valait VRAI peut devenir faux à l'INSERT... C'est comme cela que l'on fait du surbooking sans le savoir.

A +

Bonjour,

merci pour votre aide. J'ai compris mes erreurs, (je suis novice...), notamment la place où doit se situer le test EXISTS. Pour la sévérité des erreurs, je ne connais pas l'échelle. Je vais faire des recherches là-dessus.

Encore merci (ainsi qu'à ATMorphine),

à+

**ed222** · 21/06/2010, 14h49

J'ai essayé le code. J'ai une erreur à l'exécution:

Code :

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

1
2
Msg*102, Niveau*15, État*1, Procédure*reception, Ligne*13
Syntaxe incorrecte vers ';'.

Le problème est qu'il n'y a aucun ";" à la ligne 13. Elle correspond à une des déclarations de variable:

Code :

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

  , @num_lot varchar(50)

je suis sous SQL server 2005. Pourquoi voit-il un ";"???

merci,

**ed222** · 21/06/2010, 15h17

Re,

bon je commence à comprendre ce qui se passe. Les lignes correspondant à des commentaires ne sont pas comptabilisées. Pratique...
Le problème se situerait alors au ";" à la fin de la déclaration des paramètres de la procédure. Je les ai enlevés, mais ça génère un autre message d'erreur...

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE [dbo].[reception2] 
	-- Paramètres de la procédure
	  @ID_stock int
	, @codearticle varchar(50)
    , @num_lot varchar(50)
    , @IDpers nchar(10)
    , @qte float
    , @empl varchar(50);
 
SET NOCOUNT ON;

Où se situe donc l'erreur de syntaxe?...

merci,
Edouard.

**SQLpro** · 21/06/2010, 15h36

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE [dbo].[reception2] 
	-- Paramètres de la procédure
	  @ID_stock int,
	, @codearticle varchar(50)
    , @num_lot varchar(50)
    , @IDpers nchar(10)
    , @qte float
    , @empl varchar(50);
 
SET NOCOUNT ON;

Voire en rouge la virgule manquante !

A +

**ed222** · 21/06/2010, 15h50

Merci!

mais ça ne va toujours pas

... car il y a déjà une virgule entre @ID_stock et @codearticle. ça fait donc 2 virgules et plusieurs messages d'erreurs puisqu'il ne s'y retrouve plus du coup avec les paramètres... vraiment je ne vois pas.

merci,

**ed222** · 21/06/2010, 16h45

J'ai supprimé le ";" à la fin de la déclaration des paramètres, puis ajouté "AS" juste avant le BEGIN TRANSACTION. L'exécution SQL fonctionne.
Du coup, j'ai également supprimé :

Code :

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

1
2
3
4
5
6
ELSE
    BEGIN
 
       RAISERROR ('choisir un autre emplacement',16,1)
 
    END

puisqu'avec les lbl_error ça ne sert plus.

autre chose, j'ai du supprimer IF @@ROWCOUNT > 0, sinon la table emplacement ne se met pas à jour. Et là je ne vois pas pourquoi...

merci,

**elsuket** · 22/06/2010, 08h51

Bonjour,

Puisque vous êtes sous SQL Server 2005, autant utiliser TRY ... CATCH :

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
47
48
49
ALTER PROCEDURE [dbo].[reception] 
	@ID_stock int
	, @codearticle varchar(50)
	, @num_lot varchar(50)
	, @IDpers nchar(10)
	, @qte float
	, @empl varchar(50)
AS
BEGIN 
	SET NOCOUNT ON
 
	BEGIN TRY
		BEGIN TRANSACTION
 
		INSERT INTO dbo.Stock
		(
			ID_stock
			, Code_article
			, Num_lot
			, Code_emplacement
			, quantite
			, ID_personne
		)
		SELECT	@ID_stock
			, @codearticle
			, @num_lot
			, @empl
			, @qte
			, @IDpers
		FROM	dbo.Emplacement 
		WHERE	Code_emplacement = @empl 
		AND	Emplacement.capacite >= @qte
 
		-- Mise  à jour de la capacité de l'emplacement.
		IF @@ROWCOUNT > 0
		BEGIN
			UPDATE	Emplacement
			SET	Capacite = Capacite - @qte 
			WHERE	Emplacement.Code_emplacement = @empl
		END
 
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		RAISERROR('choisir un autre emplacement', 16, 1)
 
		ROLLBACK TRANSACTION
	END CATCH
END

@++

**ed222** · 22/06/2010, 10h05

Bonjour Elsuket,

encore merci, ça marche parfaitement

! effectivement, la gestion des erreurs par try catch semble plus souple. Juste une précision : si je souhaite afficher un message d'erreur qui serait fonction du type d'erreur, comment faire? Pour faire simple, il peut y avoir 2 types d'erreur : erreur de frappe (produit non reconnu dans la table Article) et emplacement pas assez suffisant pour la réception (ce que fait déjà le catch que vous avez écrit).
Faut-il faire 2 blocs try/catch ou bien faire une distinction dans le seul bloc catch ( IF....RAISERROR, ELSE RAISERROR)?

merci!

**ATMorphine** · 22/06/2010, 10h47

Lis le lien que je t'ai fournis sur le Try Catch,
Ca t'explique comment l'utiliser et tu gagneras du temps pour ton developpement

**elsuket** · 22/06/2010, 11h38

Vous devez pour cela effectuer deux tests.
Sachez que vous pouvez stocker et référencer en premier paramètre de RAISERROR l'identifiant de vos propres messages d'erreur à l'aide de la procédure stockée système sp_addmessage

@++

**ed222** · 22/06/2010, 12h00

Ok,

je vais tenter de faire ça.

merci,

**SQLpro** · 22/06/2010, 15h37

Envoyé par ed222

Bonjour Elsuket,

encore merci, ça marche parfaitement

! effectivement, la gestion des erreurs par try catch semble plus souple. Juste une précision : si je souhaite afficher un message d'erreur qui serait fonction du type d'erreur, comment faire? Pour faire simple, il peut y avoir 2 types d'erreur : erreur de frappe (produit non reconnu dans la table Article) et emplacement pas assez suffisant pour la réception (ce que fait déjà le catch que vous avez écrit).
Faut-il faire 2 blocs try/catch ou bien faire une distinction dans le seul bloc catch ( IF....RAISERROR, ELSE RAISERROR)?

merci!

Dans ce cas évitez le TRY CATCH qui est "global" et utilisez le bon vieux GOTO :

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
...
IF @@ROWCOUNT =  0 GOTO LBL_PLUS_DE_STOCK
 
...
IF @@ERROR <> 0 GOTO LBL_ERREUR
 
 
COMMIT TRANSACTION;
RETURN;
LBL_PLUS_DE_STOCK:
   ROLLBACK TRANSACTION
   RAISERROR('Ya plus de stock', 16, 1);
   RETURN
LBL_ERREUR:
   ROLLBACK TRANSACTION
   RAISERROR('Erreur SQL', 16, 1);

A +

RAISERROR(

**ed222** · 23/06/2010, 11h03

Bonjour,

j'ai essayé avec le try/catch mais j'avoue que je n'y arrive pas puisque je me demande comment faire...

merci,

Gestion d'une transaction

Développement SQL Server

Discussions similaires

Partager

Partager