Discussion :

[bobebe62]

Bonjour à tous,

Je suis sur sql-Server depuis quelques mois je suis encore en mode découverte, je me suis mis au trigger et j'avoue avoir beaucoup de mal ....

Tout d'abord j'ai parcouru le forum et rencontré pas mal ce problème. Mais je n'arrive pas à mettre en place la solution.

Le voici :
- j'ai une table A qui comprend des articles. Les utilisateurs peuvent voter pour ces articles. Dans ce cas un inert dans la table B valide le vote.
- A l'insertion du vote, je souhaite utiliser un declencheur qui va modifier un champ classement dans tous les enregistrements de la table A en fonction du nombre de vote.

Voici mon code, merci d'être indulgent.

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
 
USE [DB]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_vote] ON [dbo].[VOTE]  AFTER INSERT as
 
DECLARE 
@i int,
@VOTE int,
@ID_ARTICLE int;
 
BEGIN
set @i = 1
 
DECLARE MyCursor CURSOR FOR
 
select w.ID_ARTICLE,(select count(*) from VOTE where RESULTAT_VOTE=1 and ID_ARTICLE=w.ID_ARTICLE) as nb_vote from ARTICLE w left join VOTE v on w.ID_ARTICLE=v.ID_ARTICLE order by nb_vote desc ,w.ID_ARTICLE desc
open MyCursor
fetch MyCursor into @ID_ARTICLE, @VOTE
WHILE @@FETCH_STATUS = 0
begin
update ARTICLE set CLASSEMENT= @i where ID_ARTICLE=@ID_ARTICLE
SET @i = @i + 1
fetch MyCursor into @ID_ARTICLE, @VOTE
end
close MyCursor
DEALLOCATE MyCursor
END

Donc le trigger se lance mais l'insertion ne se fait pas.... Est ce du au curseur ?
D'avance merci de m'eclairer.

Amicalement.
Bob

[aieeeuuuuu]

Bonjour,

Quelle insertion ne se fait pas ? dans la table des votes ?
Je ne vois rien qui pourrai expliquer cela mais de toute façon, le curseur est inutile, vous pourriez passer directement par une requete ensembliste.
Il serait par ailleurs sans doute préférable de passer par une vue pour calculer cette colonne.
Si la performance venait à poser problème en raison de la volumétrie, vous pouvez indexer une vue pour les articles ayant des votes, et ajoutant une vue par dessus pour ajouter les articles sans vote.

[bobebe62]

Tout d'abord merci pour ta réponse,
c'est bien l'insertion de l'enregistrement dans la table vote qui ne se fait pas. C'est l'insert qui "déclenche le déclencheur" .
Tu penses qu'une vue serait plus adaptée? Je vais me pencher dessus et regarder son fonctionnement je vous dis quoi demain.
Merci.

[bobebe62]

Salut!
Bon j'ai regardé un peu les vues mais je ne vois pas trop comment cela peux m'aider....
Du coup je me suis repenché dans mon code. Mon insert la table vote ne s'effectue pas a cause du declencheur... Pourtant le declencheur doit se lancer lors de l'insertion.
Je ne comprend vraiment pas.
Si quelqu'un pouvait juste m'eclairer.

D'avance merci!

[elsuket]

Bonjour,

Voyons les deux approches, avec des tables qui sont certainement très simplifiées par rapport à ce que vous avez en place.
Voici les tables :

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
CREATE TABLE dbo.article
(
	id_article int NOT NULL IDENTITY
		CONSTRAINT PK_article PRIMARY KEY
	, nom_article varchar(32) NOT NULL
		CONSTRAINT UQ_article__nom_article UNIQUE
	, classement int NOT NULL
		CONSTRAINT DF_article__classement DEFAULT 0
)
GO
 
CREATE TABLE dbo.vote
(
	id_vote int NOT NULL IDENTITY
		CONSTRAINT PK_vote PRIMARY KEY
	, id_article int NOT NULL
		CONSTRAINT FK_vote__id_article FOREIGN KEY(id_article) REFERENCES dbo.article
	, date_vote datetime2(3) NOT NULL
		CONSTRAINT DF_vote__date_vote DEFAULT (SYSDATETIME())
)
GO

Et le trigger :

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
CREATE TRIGGER TR_A_I_vote
	ON dbo.vote
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON;
 
	WITH
		CTE AS
		(
			SELECT	id_article
				, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS dr
			FROM	dbo.vote
			GROUP	BY id_article
		)
	UPDATE		dbo.article
	SET		classement = c.dr
	FROM		CTE AS C
	INNER JOIN	dbo.article AS A
				ON C.id_article = A.id_article;
END
GO

On est obligé de recalculer les rang de tous les articles après chaque vote.
Pas de curseur, puisque le moteur est conçu pour ne pas y avoir recours et optimisé pour traiter les lignes dans leur ensemble, malgré tout ce que vous entendrez sur le sujet.

Ajoutons quelques articles : l'article A aura l'id_article 1, l'article B l'id_article 2, et ainsi de suite :

Code :

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

1
2
3
INSERT INTO dbo.article(nom_article)
VALUES ('article A'), ('article B'), ('article C')
GO

Puis les votes :

Code :

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

1
2
3
INSERT INTO dbo.vote (id_article)
VALUES (1), (1), (1), (2), (2), (2), (2), (2), (2), (2), (2), (2), (3), (3), (3), (3), (3)
GO

Donc c'est l'article d'id_article 2, soit l'article B, qui sera le premier au classement par votes, suivi du C puis du A.
Si on exécute le SELECT suivant, c'est bien ce que l'on obtient :

Code :

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

1
2
3
SELECT	id_article, classement
FROM	dbo.article
GO

Mais comme l'écrit aieeeuuuuu, on peut se servir d'une vue pour ce faire.
Ceci nous évite d'avoir à recalculer le classement à chaque vote, et de ne réaliser le calcul que lorsqu'on souhaite voir le classement.
En d'autre termes, si votre système supporte l'enregistrement de nombreux votes, donc de nombreuses écritures, l'avantage est clairement à la vue :

Code :

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

1
2
3
4
5
6
7
CREATE VIEW v_article_classement
AS
	SELECT	id_article
		, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) AS dr
	FROM	dbo.vote
	GROUP	BY id_article
GO

Pour optimiser les lectures, on aurait pu effectivement avoir recours à une vue indexée, dont la définition suit :

Code :

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

1
2
3
4
5
6
7
8
ALTER VIEW v_article_classement
	WITH SCHEMABINDING
AS
	SELECT	id_article
		, DENSE_RANK() OVER(ORDER BY COUNT_BIG(*) DESC) AS dr
	FROM	dbo.vote
	GROUP	BY id_article
GO

Mais comme cette définition contient une fonction de fenêtrage, il n'est pas possible d'ajouter l'index suivant :

Code :

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

1
2
CREATE UNIQUE CLUSTERED INDEX IX_v_article_classement
ON v_article_classement(id_article)

Qu'à cela ne tienne, puisque l'ordre dépend du nombre de votes : si l'on compte seulement les votes dans la vue à indexer :

Code :

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

1
2
3
4
5
6
7
8
9
 
ALTER VIEW v_article_classement
	WITH SCHEMABINDING
AS
	SELECT	id_article
		, COUNT_BIG(*) AS nombre_vote
	FROM	dbo.vote
	GROUP	BY id_article
GO

On peut alors exécuter :

Code :

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

1
2
CREATE UNIQUE CLUSTERED INDEX v_article_classement
ON dbo.v_article_classement(id_article)

Et donc on peut aussi exécuter :

Code :

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

1
2
3
SELECT	id_article
	, DENSE_RANK() OVER(ORDER BY nombre_vote DESC) AS classement
FROM	dbo.v_article_classement

Ou encore :

Code :

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

1
2
3
4
5
6
7
SELECT		A.id_article
		, A.nom_article
		, VAC.nombre_vote
		, DENSE_RANK() OVER(ORDER BY nombre_vote DESC) AS classement
FROM		dbo.article AS A
INNER JOIN	dbo.v_article_classement AS VAC
			ON A.id_article = VAC.id_article

@++

[bobebe62]

Que dire à part merci pour cette explication qui se rapproche plus d'une démonstration!
Je regarde l'ensemble et vous tient informé.
Merci à vous deux.

[bobebe62]

Bonjour à tous avec pas mal de retard je viens vous remercier. La solution de la vue est parfaite et fonctionne à merveille.
Bonne journée à tous.