Discussion :

[smart76]

bonjour,

J'ai un problème que je n'arrive pas à résoudre, le trigger ci-dessous provoque une erreur Microsoft SQL Native Client, Native Error 523, Error State 12, Severity 16, Un déclencheur a retourné un ensemble de résultats et/ou fonctionnait avec SET NOCOUNT OFF tandis qu'un autre ensemble de résultats en suspens était actif.

Si je désactive le trigger, je n'ai pas d'erreur, si j'active le trigger en supprimant tout le code et en laissant uniquement la déclaration du curseur, j'ai également l'erreur. L'ajout de "set nocount on" ne change rien.

Merci de votre aide.

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
ALTER TRIGGER [TRG_MATABLE1]
ON [MATABLE1]
FOR insert,update
AS
BEGIN
	declare @r_empl_matri varchar(10)
	declare @r_empl_seirestetab varchar(1)
	declare @r_empl_nompre varchar(30)
	declare @r_empl_seinomfam varchar(30)
	declare @r_empl_seiprenom varchar(30)
	declare @nompre varchar(30)
	declare @r_empl_horsect varchar(40)
	declare @r_empl_seiresetab varchar(1)
	declare @ssinetab varchar(5)
	declare @r_empl_seiqual varchar(4)
	declare @r_ctra_sciqualif varchar(4)
	declare @alias varchar(30)
	declare @libqual varchar(20)
	declare @v_tmp varchar(20)
 
 
	set ansi_warnings off
set nocount on
  -- déclaration des variables
	declare c_empl insensitive cursor for 
		select matri, SEIRESETAB, nompre, horsect, seiresetab, seiqual, seinomfam, seiprenomn from inserted
 
	open c_empl
	fetch c_empl into @r_empl_matri, @r_empl_seirestetab, @r_empl_nompre, @r_empl_horsect, @r_empl_seiresetab, @r_empl_seiqual, @r_empl_seinomfam, @r_empl_seiprenom 
	if @@fetch_status=0
	begin
		if @r_empl_seinomfam is null set @r_empl_seinomfam=' '
		if @r_empl_seiprenom is null set @r_empl_seiprenom=' '
		set @nompre=substring(@r_empl_seinomfam+' '+@r_empl_seiprenom, 1, 30)
		set @alias=upper(substring(@r_empl_seiprenom, 1, 1)+@r_empl_seinomfam)
		if @r_empl_seiresetab is null or @r_empl_seiresetab=' ' set @r_empl_seiresetab ='N'
		select @v_tmp=min(code) from MATABLE2 where type='H' and element='SCIREMPL' and code=substring( @r_empl_horsect, 1,2)+ @r_empl_matri
		if @v_tmp is null 
		begin
			insert into MATABLE2  (type, element, code, libcode) values ('H', 'SCIREMPL', substring( @r_empl_horsect, 1,2)+ @r_empl_matri, @nompre)
		end
		else
		begin
			update MATABLE2  set libcode=@nompre where type='H' and element='SCIREMPL' and code=substring( @r_empl_horsect, 1,2)+ @r_empl_matri
		end 
		select @ssinetab=max(ssinetab) from hopsech where horsect=@r_empl_horsect
		select @r_Ctra_sciqualif =max(sciqualif) from hopctra where matri=@r_empl_matri and convert(datetime,convert(varchar, getdate(), 103), 103) between datdeb and datfin
		select @libqual=max(substring(libcode, 1, 20)) from hopcode where type='H' and element='SCIQUALIF' and code=@r_Ctra_sciqualif 
		if @ssinetab is null set @ssinetab=' '
		if @libqual is null set @libqual=' '
		if @r_empl_seiresetab ='O'
		begin
			update MATABLE1  set seiresetab='N' where matri<>@r_empl_matri and horsect in (select horsect from hopsech where ssinetab=@ssinetab)
			update MATABLE3  set nomdir=@r_empl_nompre, qualifdir=@libqual where etab=@ssinetab
		end
		update MATABLE1  set nompre=@nompre, seiresetab=@r_empl_seiresetab where matri=@r_empl_matri and (nompre<>@nompre or alias<>@alias or seiresetab<>@r_empl_seiresetab) 
 
	end 
	close c_empl
	deallocate c_empl
 
END

[elsuket]

Bonjour,

Je suis désolé de vous le dire mais ce que vous avez écrit est le parfait exemple de tout ce qu'il ne faut pas faire :

- ne jamais utiliser les curseurs : SQL est un langage déclaratif et ensembliste : ce n'est à vous de dicter à SQL Server ce qu'il doit faire.
Vous exprimez ce dont vous avez besoin, pas la façon de l'obtenir : SQL Server sait bien mieux que nous comment il doit procéder.
D'autre part SQL est un langage qui est conçu pour traiter les données dans leur ensemble, et non pas dans leur unité comme vous le faire avec un curseur.
En conséquence les curseurs sont très contre-performants, comme la marche arrière d'une voiture : elle est livrée avec la voiture, mais on ne s'en sert que lorsque c'est nécessaire

- ne jamais utiliser un curseur dans un trigger : un trigger rallonge une transaction, puisqu'il fait partie de la transaction qui l'a déclenché.
Donc il faut faire en sorte qu'il s'exécute le plus vite possible.
Or avec un curseur, vous faites exactement l'inverse.

- 2 des variables dont vous vous servez pour le FETCH sont inutilisées, ce qui signifie que vous manipulez plus de données que nécessaire.
Donc vous consommez plus de mémoire du fait de votre curseur, qui pourrait être allouée à d'autre données.

- le nom des objets que vous vous manipulez, ici les tables (mais cela vaut aussi pour les procédures stockées, fonctions, vues, ...) doivent être qualifiés par le nom du schéma auquel ils appartiennent.
Comme vos tables sont par défaut stockées dans le schéma dbo, vous avez supposé que c'est toujours le cas pour toutes les tables et autres objets.
Avez-vous jeté un œil au schéma sys et INFORMATION_SCHEMA ?
En ne le précisant pas, vous forcez SQL Server à le chercher à chaque fois à votre place ...

Mais même en le réécrivant en quelques requêtes :

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
91
92
93
94
95
96
97
98
 
UPDATE		dbo.MATABLE2  
SET		libcode = SUBSTRING(I.seinomfam + ' ' + I.seiprenomn, 1, 30) 
FROM		inserted AS I
INNER JOIN	dbo.MATABLE2 AS T
			ON I.SUBSTRING(horsect, 1, 2) + matri = T.code
AND		T.type = 'H'
AND		T.element = 'SCIREMPL'
 
 
INSERT	INTO dbo.MATABLE2  
(
	type
	, element
	, code
	, libcode
)
SELECT	'H'
	, 'SCIREMPL'
	, SUBSTRING(horsect, 1, 2)+ matri
	, SUBSTRING(seinomfam + ' ' + seiprenomn, 1, 30)
 
FROM		dbo.MATABLE2 AS T
LEFT JOIN	inserted AS I
			ON I.SUBSTRING(horsect, 1, 2) + matri = T.code
WHERE		T.code IS NULL
 
;WITH
	CTE AS
	(
		SELECT		H.horsect
				, MAX(H.ssinetab) AS max_ssinetab
		FROM		dbo.hopsech AS H
		INNER JOIN	inserted AS I
					ON H.horsect = I.horsect
	)
UPDATE		dbo.MATABLE1
SET		seiresetab = 'N'
FROM		dbo.MATABLE1 AS T
INNER JOIN	inserted AS I
			ON T.matri <> I.matri
INNER JOIN	CTE AS C
			ON T.horsect = C.horsect
			AND H.ssinetab = C.max_ssinetab
;WITH
	CTE_SSINETAB AS
	(
		SELECT		H.horsect
				, COALESCE(MAX(H.ssinetab), ' ') AS max_ssinetab
		FROM		dbo.hopsech AS H
		INNER JOIN	inserted AS I
					ON H.horsect = I.horsect
	)
	, CTE_SCIQUALIF AS
	(
		SELECT		H.matri
				, MAX(H.sciqualif) AS max_sciqualif
		FROM		dbo.hopctra AS H
		INNER JOIN	inserted AS I
					ON H.matri = I.matri
		WHERE		CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) BETWEEN H.datdeb AND H.datfin
		GROUP BY	H.matri
	)
	, CTE_LIBCODE AS
	(
		SELECT		CS.max_sciqualif
				, COALESCE(MAX(SUBSTRING(H.libcode, 1, 20)), ' ') AS max_libcode
		FROM		CTE_SCIQUALIF AS CS
		INNER JOIN	dbo.hopcode AS H
					ON CS.max_sciqualif = H.code
		WHERE		H.type = 'H'
		AND		H.element = 'SCIQUALIF'
		GROUP BY	CS.max_sciqualif
	)
UPDATE		dbo.MATABLE3
SET		nomdir = I.nompre
		, qualifdir = CL.max_libcode
FROM		dbo.MATABLE3 AS T
INNER JOIN	CTE_LIBCODE AS CL
			ON ???
INNER JOIN	CTE_SSINETAB AS CS
			ON T.etab = CS.max_ssinetab
INNER JOIN	inserted AS I 
			ON I.seiresetab = T.seiresetab
WHERE		I.seiresetab = 'O'
 
UPDATE		dbo.MATABLE1
SET		nompre = SUBSTRING(I.seinomfam + ' ' + I.seiprenomn, 1, 30)
		, seiresetab = I.seiresetab
FROM		dbo.MATABLE1 AS T
INNER JOIN	inserted AS I
			ON T.matri = I.matri
			AND 
			(
				T.nompre <> SUBSTRING(I.seinomfam + ' ' + I.seiprenomn, 1, 30)
				OR T.alias <> UPPER(SUBSTRING(I.seiprenomn, 1, 1) + I.seiprenomn)
				OR T.seiresetab <> I.seiresetab
			)

Je doute sincèrement que ce soit suffisant.
Cela fera peut-être passer l'erreur, mais la vitesse de traitement sera de toute façon longue.
Voyez si vous ne pouvez pas le faire dans une procédure stockée, et si les index qui sont sur la table TABLE1 sont les bons.
Enfin sachez que les jointures sur des colonnes stockant des valeurs de type chaîne de caractère ou utilisant des fonctions sont contre-performants.

@++

[smart76]

bonjour et merci pour ces conseils avisés que je ne manquerais pas de mettre en application. Le problème c'est que dans mon cas actuel, ma table est tellement petite que je n'ai pas à me soucier des temps de réponse, le plantage SQL que j'obtiens est immédiat dès le premier enregistrement traité et suite à mes tests je constate que ce n'est pas le code du trigger qui est en cause mais bel est bien la déclaration du trigger en lui-même. en effet, j'ai fait un test en supprimant tout le code du trigger (je laisse CREATE TRIGGER ... AS BEGIN END) et le plantage SQL se produit encore.

Je cherche donc à comprendre la signification du message d'erreur suivant : Un déclencheur a retourné un ensemble de résultats et/ou fonctionnait avec SET NOCOUNT OFF tandis qu'un autre ensemble de résultats en suspens était actif.

Cela me permettra peut-être de trouver la cause du problème.

[smart76]

bonjour,
je n'ai malheureusement pas eu de réponse et ce problème est toujours d'actualité.

Je cherche toujours à comprendre la signification du message d'erreur suivant : Un déclencheur a retourné un ensemble de résultats et/ou fonctionnait avec SET NOCOUNT OFF tandis qu'un autre ensemble de résultats en suspens était actif.

alors que j'ai bien l'option SET NOCOUNT ON dans le trigger.

[SQLpro]

La réponse de Elsuket était la bonne :
vous utilisez un curseur là ou vous devriez utiliser de simples requêtes SQL.
Comme vous faites des mise à jour de données (UPDATE) qui portent sur les données mêmes du curseur, vous vous emmêlez les pinceaux en parcourant les mêmes données à la fois dans le déclencheur et dans les requêtes SQL, d'ou le message d'erreur llié à la circularité des verrous...
Bref, vous faite de la programmation spaghetti !

Plusieurs solutions :
1) supprimer le déclencheur et faire que des requêtes. C'est la meilleure solution.
2) faire vos mise à jour en interne dans le curseur en utilisant la syntaxe UPDATE ... WHERE CURRENT OF MonCurseur.
3) utiliser des options spécifique au curseur pour en minimiser la charge.

a +