Aide pour les transactions SQL server

**Carmagamemnon** · 09/01/2009, 11h22

Bonjour à tous,

Voilà j'ai un léger problème, je débute avec les transactions et malgré mes recherches je n'arrive pas à comprendre l'erreur, ci-dessous ma procédure suivie de l'erreur renvoyée :

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
57
CREATE PROCEDURE SupprEd(@titre varchar(64), @complément varchar(128), @V_SORTIE INT =1 OUTPUT)
as
declare @t_original varchar(64), @annee varchar(4), @stock int, @cpted int, @bool int
declare CurEd cursor for(
  select TITRE_ORIGINAL, DATE_DE_SORTIE
  from EDITION
  where TITRE = @titre and COMPLEMENT_DE_TITRE = @complément)
 
open CurEd
fetch next from CurEd
into @t_original, @annee
-- On teste si l'édition existe, si ce n'est pas le cas, on affiche un message d'erreur
   if(@@FETCH_STATUS != 0) print 'Aucune édition de ce type.'
-- Tant que le curseur CURED pointe sur un élément, on effectur les opérations suivantes
   begin tran
   while @@FETCH_STATUS = 0
      begin
      -- S'il une réservation a été faite sur l'édition, on affiche un message d'erreur
      if(exists(select * from RESERVATION R where R.TITRE = @titre and R.COMPLEMENT_DE_TITRE = @complément))
         begin
         print 'Une réservation est prévue sur cette édition...'
         set @V_SORTIE = 0
         end
      -- Sinon on supprime l'édition et les stocks correspondants
      else
         begin
         declare CurStockAux cursor for(
            select #STOCK
               from STOCK
               where TITRE = @titre and COMPLEMENT_DE_TITRE = @complément)
         open CurStockAux
         fetch next from CurStockAux
         into @stock
         set @cpted = 0
         while @@FETCH_STATUS = 0
            begin
            -- La procédure SupprStock renvoie 1 si elle s'est déroulée correctement, 0 sinon
	    EXECUTE SupprStock @stock, @V_SORTIE = @bool OUTPUT
            -- Si la suppression d'un élément du stock ne s'est pas effectuée correctement (location en cours),
            -- on annule la transaction.
	    if(@bool = 0)
               rollback tran
            set @cpted = @cpted + 1
	    fetch next from CurStockAux
            into @stock
            end
         close CurStockAux
         deallocate CurStockAux
         delete EDITION where TITRE = @titre and COMPLEMENT_DE_TITRE = @complément
         end
      print 'L''édition ' + @titre + ' : ' + @complément + ' et les stocks correspondants (' + CAST(@cpted as varchar) + ') ont été supprimés de la base.'
      fetch next from CurEd
      into @t_original, @annee
      end
   commit tran
   close CurEd
   deallocate CurEd;

Serveur : Msg 3902, Niveau 16, État 1, Procédure SupprEd, Ligne 56
La requête COMMIT TRANSACTION n'a pas de BEGIN TRANSACTION correspondante.

La procédure SupprStock supprime un élément du stock, sachant qu'une édition peut avoir 0 ou n stock(s) correspondant(s). Ici je souhaite utiliser un transaction car si une location est en cours sur un élément du stock correspondant à l'édition que je désire supprimer, il faudrait annuler toutes les éventuelles suppressions qui pourraient avoir été faites au préallable.

J'espère avoir été assez clair, en vous remerciant d'avance.

Cordialement.

**elsuket** · 09/01/2009, 12h39

Bonjour,

C'est normal, vous validez une transaction après peut-être l'avoir défaite ...
Votre ROLLBACK doit se faire sur une condition dont le ELSE doit contenir le COMMIT.

De plus :
- vos transactions doivent être les plus courtes possibles, c'est à dire contenir un minimum d'instructions,

- vous n'avez pas qualifié vos objets (tables et procédures stockées) avec le nom du schéma auquel ils appartiennent,

- vous utilisez des accents dans les noms de vos variables et des caractères diacritiques pour le nom de vos colonnes (@complément, #STOCK) ce qui est très dangereux,

- vous utilisez un curseur à la place de code ensembliste. Retenez qu'un traitement par curseur est au moins 10 fois plus lent que du code ensembliste.

Je vous ai corrigé la transaction :

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE PROCEDURE SupprEd
	@titre VARCHAR(64),
	@complément VARCHAR(128),
	@V_SORTIE INT = 1 OUTPUT
AS
BEGIN
	DECLARE @t_original VARCHAR(64),
			@annee VARCHAR(4),
			@stock INT,
			@cpted INT,
			@bool INT
 
	DECLARE CurEd cursor FOR
	  SELECT TITRE_ORIGINAL, DATE_DE_SORTIE
	  FROM EDITION
	  WHERE TITRE = @titre AND COMPLEMENT_DE_TITRE = @complément
	FOR READ ONLY
 
	OPEN CurEd
	FETCH NEXT FROM CurEd INTO @t_original, @annee
	-- On teste si l'édition existe, si ce n'est pas le cas, on affiche un message d'erreur
	IF(@@FETCH_STATUS != 0) PRINT 'Aucune édition de ce type.'
	BEGIN
		-- Tant que le curseur CURED pointe sur un élément, on effectur les opérations suivantes
			WHILE @@FETCH_STATUS = 0
			BEGIN
				-- S'il une réservation a été faite sur l'édition, on affiche un message d'erreur
				IF EXISTS
				(
					SELECT *
					FROM RESERVATION R
					WHERE R.TITRE = @titre
					AND R.COMPLEMENT_DE_TITRE = @complément
				)
				BEGIN
					PRINT 'Une réservation est prévue sur cette édition...'
					SET @V_SORTIE = 0
				END
				-- Sinon on supprime l'édition et les stocks correspondants
				ELSE
				BEGIN
					DECLARE CurStockAux CURSOR FOR
						SELECT #STOCK
						FROM STOCK
						WHERE TITRE = @titre
						AND COMPLEMENT_DE_TITRE = @complément
					FOR READ ONLY
 
					OPEN CurStockAux
					FETCH NEXT FROM CurStockAux INTO @stock
					SET @cpted = 0
 
					WHILE @@FETCH_STATUS = 0
					BEGIN
						BEGIN TRAN
						-- La procédure SupprStock renvoie 1 si elle s'est déroulée correctement, 0 sinon
						EXECUTE SupprStock
							@stock,
							@V_SORTIE = @bool OUTPUT
 
						-- Si la suppression d'un élément du stock ne s'est pas effectuée correctement (location en cours),
						-- on annule la transaction.
						IF @bool = 0
						BEGIN
						   ROLLBACK TRAN
						END
						ELSE
						BEGIN
							DELETE FROM EDITION
							WHERE TITRE = @titre
							AND COMPLEMENT_DE_TITRE = @complément
 
							COMMIT TRAN
						END
					SET @cpted = @cpted + 1
					FETCH NEXT FROM CurStockAux INTO @stock
				END
				CLOSE CurStockAux
				DEALLOCATE CurStockAux
			END
			PRINT 'L''édition ' + @titre + ' : ' + @complément + ' et les stocks correspondants (' + CAST(@cpted AS varchar) + ') ont été supprimés de la base.'
			FETCH NEXT FROM CurEd INTO @t_original, @annee
		END
		CLOSE CurEd
		DEALLOCATE CurEd
	END
END

@++

**Carmagamemnon** · 09/01/2009, 14h59

Merci elsuket pour ta réponse et tes conseils, je n'ai pas encore vu le code ensembliste mais je tiendrai compte de ta remarque à l'avenir

Concernant le code, je l'ai testé mais je n'obtiens pas encore ce que je cherche :/

Imaginons une édition E1 et une liste d'éléments du stock S={S1,S2,S3}. Tous les éléments de S correspondent à E1, en d'autres termes si E1 disparaît on n'a pas lieu de garder S.
Je rajoute un location L d'un abonné sur S2.
Avec le code ci-dessus lorsque j'exécute la procédure afin de supprimer E1, celle-ci supprime d'abord S1, bloque sur S2 car il est en location et "s'arrête". Au final seul S1 a été supprimé. Ce que je souhaiterais c'est un résultat "Tout ou rien", est-ce possible ?
En tout cas je te remercie pour l'interêt que tu portes à mon problème.

Bonne après-midi.

Bien cordialement.

**SQLpro** · 10/01/2009, 00h43

C'est un simple problème fonctionnel !
Comptez le nombre de lignes à supprimer au départ.
Supprimez par une requête DELETE.
Comptez le nombre de lignes impactées par votre requête DELETE. Si c'est différent faites un ROLLBACK. SI c'est le même, faites un COMMIT.

C'est vous qui décidez de la façon dont vous voulez que la transaction se termine...

A +

**Carmagamemnon** · 14/01/2009, 15h13

Bonjour,

Merci énormément, j'ai revu le code et tout fonctionne à merveille.
A très bientôt sur le forum ;)

Bien cordialement.

**Carmagamemnon** · 14/01/2009, 18h24

Bonsoir à tous,

Finalement je rencontre un autre problème, je n'arrive pas à récupérer la valeur de retour renvoyée par ma procédure SupprStock. La voici :

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
CREATE PROCEDURE SupprStock
   @STOCK INT,
   @V_SORTIE INT =1 OUTPUT
AS
BEGIN
   DECLARE @titre varchar(64),
      @annee DATETIME
 
   DECLARE CurStock CURSOR FOR
      SELECT TITRE_ORIGINAL, DATE_DE_SORTIE
      FROM STOCK
      WHERE #STOCK = @STOCK
      FOR READ ONLY
 
   OPEN CurStock
   FETCH NEXT FROM CurStock INTO @titre, @annee
   -- On teste si l'article existe, si ce n'est pas le cas, on affiche un message d'erreur
   IF(@@FETCH_STATUS != 0) PRINT 'Aucun stock portant cet ID.'
   BEGIN
   -- Tant que le curseur CURSTOCK pointe sur un élément, on effectue les opérations suivantes
   WHILE @@FETCH_STATUS = 0
      BEGIN
      -- Si une location est en cours sur cet article, on affiche un message d'erreur
      IF EXISTS
         (
         SELECT *
         FROM LOCATION L
         WHERE L.#Stock = @Stock
         )
         BEGIN
            PRINT 'Une location est toujours en cours...'
            SET @V_SORTIE = 0
         END
         -- Sinon on supprime l'article
      ELSE
         BEGIN
         DELETE FROM STOCK
         WHERE #STOCK = @STOCK
         PRINT 'Le film n°' + CAST(@STOCK as varchar) + ' : ' + @titre + '(' + CAST(YEAR(@annee) as varchar) + ') a été retiré des stocks.'
         END
      FETCH NEXT FROM CurStock INTO @titre, @annee
      END
   END
CLOSE CurStock
DEALLOCATE CurStock
RETURN @V_SORTIE
END;

Dans ma seconde procédure SupprEd ci-dessous, j'ai voulu dans un premier temps "printer" ma variable @res, mais rien ne s'affichait. Je l'ai donc initialisé à 0 et je constate que cette valeur reste la même :/ J'imagine donc que je ne m'y prends pas bien pour récupérer la valeur de retour de ma première procédure. Voici la seconde procédure :

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE PROCEDURE SupprEd
   @titre VARCHAR(64),
   @complément VARCHAR(128),
   @V_SORTIE INT = 1 OUTPUT
AS
BEGIN
   DECLARE @stock INT,
      @nb_Stock INT,
      @nb_Stock_Suppr INT,
      @res INT
 
   DECLARE CurEd cursor FOR
      SELECT *
      FROM EDITION
      WHERE TITRE = @titre AND COMPLEMENT_DE_TITRE = @complément
      FOR READ ONLY
 
   OPEN CurEd
   FETCH NEXT FROM CurEd
   -- On teste si l'édition existe, si ce n'est pas le cas, on affiche un message d'erreur
   IF(@@FETCH_STATUS != 0) PRINT 'Aucune édition de ce type.'
   BEGIN
   -- Tant que le curseur CURED pointe sur un élément, on effectur les opérations suivantes
   WHILE @@FETCH_STATUS = 0
      BEGIN
      -- S'il une réservation a été faite sur l'édition, on affiche un message d'erreur
      IF EXISTS
         (
         SELECT *
         FROM RESERVATION R
         WHERE R.TITRE = @titre
         AND R.COMPLEMENT_DE_TITRE = @complément
         )
         BEGIN
            PRINT 'Une réservation est prévue sur cette édition...'
            SET @V_SORTIE = 0
         END
         -- Sinon on supprime l'édition et les stocks correspondants
      ELSE
         BEGIN
         DECLARE CurStockAux CURSOR FOR
            SELECT #STOCK
            FROM STOCK
            WHERE TITRE = @titre
            AND COMPLEMENT_DE_TITRE = @complément
            FOR READ ONLY
 
         OPEN CurStockAux
         FETCH NEXT FROM CurStockAux INTO @stock
         SET @nb_Stock = 0
         SET @nb_Stock_Suppr = 0
set @res = 0
 
         BEGIN TRAN SupprEd
         WHILE @@FETCH_STATUS = 0
            BEGIN
            -- La procédure SupprStock renvoie 1 si elle s'est déroulée correctement, 0 sinon
            EXECUTE SupprStock @stock, @V_SORTIE = @res OUTPUT
print cast(@res as varchar)
            SET @nb_Stock = @nb_Stock + 1
print cast(@nb_Stock as varchar)
            SET @nb_Stock_Suppr = @nb_Stock_Suppr + @res
print cast(@nb_Stock_Suppr as varchar)
            FETCH NEXT FROM CurStockAux INTO @stock
            END
         -- Si la suppression d'un élément du stock ne s'est pas effectuée correctement (location en cours),
         -- @nb_Stock != @nb_Stock_Suppr donc on annule la transaction.
         IF @nb_Stock = @nb_Stock_Suppr
            BEGIN
            COMMIT TRAN SupprEd
            DELETE FROM EDITION
            WHERE TITRE = @titre
            AND COMPLEMENT_DE_TITRE = @complément
            PRINT 'L''édition ' + @titre + ' : ' + @complément + ' et les stocks correspondants (' + CAST(@nb_Stock AS varchar) + ') ont été supprimés de la base.'
            END
         ELSE
            BEGIN
            ROLLBACK TRAN SupprEd
            PRINT 'Aucun élément du stock n''a été supprimé, une location est toujours en cours.'
            SET @V_SORTIE = 0
            END
         CLOSE CurStockAux
         DEALLOCATE CurStockAux
         END
      FETCH NEXT FROM CurEd
      END
   CLOSE CurEd
   DEALLOCATE CurEd
   END
END

Et enfin le message obtenu lors de l'exécution de SupprEd :

(1 ligne(s) affectée(s))

(1 ligne(s) affectée(s))

Le film n°8 : titre de test2(2008) a été retiré des stocks.
0
1
0

(1 ligne(s) affectée(s))

Le film n°9 : titre de test2(2008) a été retiré des stocks.
0
2
0

(1 ligne(s) affectée(s))

Le film n°10 : titre de test2(2008) a été retiré des stocks.
0
3
0
Aucun élément du stock n'a été supprimé, une location est toujours en cours.

(0 ligne(s) affectée(s))

En théorie cet appel devrai supprimer les trois éléments du stock (8, 9 et 10) puis l'édition elle-même. Il n'y a aucune location en cours, j'ai vérifié et revérifié ;)

Merci à ceux qui pourront m'aider.

Bien cordialement.

Aide pour les transactions SQL server

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager