Discussion :

[liseprachan]

Bonjour,

J'ai 2 tables :
-chercheur(num_chercheur,specialite_chercheur)
-specialite(acronyme,nb_chercheurs)

J'aimerais avoir un trigger qui déclenche une mise à jour du champ nb_chercheurs lors d'une mise à jour de la table chercheur (Update,Insert ou delete).

La commande For Each Row ne fonctionnant pas sous MS SQL Server, j'aimerais savoir commande parcourir chacune des lignes de la table chercheur afin de compter le nombre de chercheur affecté à une spécialité ?

J'ai créé le trigger suivant, mais ca ne fonctionne pas ! Qq1 peut-il m'aider ?

Merci d'avance

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
CREATE TRIGGER nb_chercheur ON chercheur
FOR INSERT,DELETE,UPDATE
AS
 
--ouverture curseur sur table chercheur
declare Cur_NumChercheur cursor
for
      select num_chercheur, specialite_chercheur
      from chercheur
      order by specialite_chercheur,num_chercheur
 
declare @numChercheur int, @specialite nvarchar (50), @nb int, @spe nvarchar(50)
 
open Cur_NumChercheur
fetch Cur_NumChercheur into @numChercheur, @spe
set @nb=0
while @@fetch_status=0
begin
      set @spe=@specialite
      while @spe=@specialite
      begin
            set @nb=@nb+1
            update specialite
            set nb_chercheurs=@nb
            where acronyme=@specialite
      end
 
      fetch Cur_NumChercheur into @numChercheur, @spe
      set @spe=@specialite
end   
close Cur_NumChercheur
deallocate Cur_NumChercheur

[SQLpro]

Les triggers SQL Server sont par nature ensembliste. Donc vous ne devez faire que des requêtes SQL
Dans votre cas un simple

Code :

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

1
2
SELECT Specialité, COUNT(*) AS NOMBRE
FROM MaTable

A +

[Sergejack]

Il vous suffit de faire un simple MERGE à la place d'employer un curseur :

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
 
CREATE TRIGGER nb_chercheur ON chercheur
FOR INSERT,DELETE,UPDATE
AS
BEGIN
	MERGE specialite AS T
	USING (
		SELECT
			COALESCE(D.specialite_chercheur, I.specialite_chercheur) AS specialite_chercheur
			, ISNULL(I.Cnt, 0) - ISNULL(D.Cnt, 0)  AS Delta
		FROM (
			SELECT
				I.specialite_chercheur
				, COUNT(*) AS Cnt
			FROM inserted AS I
			GROUP BY I.specialite_chercheur
		) AS I
		FULL JOIN (
			SELECT
				D.specialite_chercheur
				, COUNT(*) AS Cnt
			FROM deleted AS D 
			GROUP BY D.specialite_chercheur
		) AS D ON (
			D.specialite_chercheur = I.specialite_chercheur
		)
	) AS Src ON (
		T.acronyme = Src.specialite_chercheur
	)
	WHEN NOT MATCHED THEN
		INSERT (acronyme, nb_chercheurs)
		VALUES (Src.specialite_chercheur, Src.Delta)
	WHEN MATCHED THEN
		UPDATE
		SET
			T.nb_chercheurs = T.nb_chercheurs + Src.Delta
	;
END

[elsuket]

Bonjour,

Tout cela me parait bien compliqué pour ce qui devrait en fait être une vue ...

Code :

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

1
2
3
4
5
6
7
CREATE VIEW v_specialite
	WITH SCHEMABINDING
AS
	SELECT	specialite_chercheur AS acronyme
		, COUNT(*) AS nb_chercheurs
	FROM	dbo.chercheur
	GROUP	BY specialite_chercheur

Un simple SELECT sur cette vue vous donne le résultat, un peu à la manière de ce que SQLPro vous a donné.

Si vous avez de nombreux chercheurs, un index non-cluster sur la colonne specialite_chercheur supportera la requête.

Ne vous en remettez pas aux déclencheurs, qui rallongent la durée des transactions, et donc diminuent la concurrence d'accès.
Utilisez-les quand il n'y a pas d'autre solution, et elles sont nombreuses quand on utilise les contraintes comme il se doit

Enfin et par dessous-tout, évitez les curseurs.

@++

[liseprachan]

Étant débutant avec Transact-SQL, la solution de Sergejack me parait intéressante à "travailler", pour améliorer ma connaissance.

Cependant, j'ai suivi les conseils de SQLpro (faire des choses simples avec SQL). Mon but est de travailler sur les triggers.

J'ai créé 3 triggers différents : insert, delete et update. Mais une juste une petite question : avec le trigger insert comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
create trigger update_nb_chercheur on chercheur
after insert
as
begin
    set nocount on;
    declare @acronyme nvarchar(5)
    select @acronyme=specialite_chercheur from inserted
    update specialite
    set nb_chercheurs=nb_chercheurs+1
    where acronyme=@acronyme
end

Mon trigger fonctionne bien, avec une insertion ligne par ligne :

Code :

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

1
2
INSERT INTO chercheur (nom_chercheur, specialite_chercheur) VALUES ( 'toto' , 'jou' ) ;
INSERT INTO chercheur (nom_chercheur, specialite_chercheur) VALUES ( 'titi' , 'jou' ) ;

Mais ne fonctionne pas avec une insertion multiple :

Code :

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

INSERT INTO chercheur (nom_chercheur, specialite_chercheur) VALUES ( 'toto' , 'jou' ) , ( 'titi' , 'jou' );

Ne faut-il pas dans ce cas introduire la notion curseur ?

[SQLpro]

Encore une fois, faites des requêtes ENSEMBLISTES !!! Ensembliste veut dire pas de boucle ni de variable unitaire (une variable unitaire n'est pas ensembliste...)

Donc, une seule requête POINT BARRE !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER update_nb_chercheur 
ON dbo.chercheur
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
 
    UPDATE T
    SET    nb_chercheurs = nb_chercheurs +1
    FROM   dbo.specialite AS T
           INNER JOIN inserted AS I
                 ON T.acronyme = i.specialite_chercheur;
END

N'oubliez pas de préfixez les objets de la base par dbo (schémas SQL).

A +

[liseprachan]

merci du conseil... je commence à mieux comprendre SQL !
Mes triggers fonctionnent avec des codes tout simple, pas besoin de boucle, pas besoin de curseur...
Je pense que j'aurais trouver la solution plus rapidement si je pouvais déboguer. Ca ne fonctionne pas sur mon poste à cause du pare-feu d'après le message, mais c'est un autre souci.
En tout cas merci à tous, spécialement à SQLPro...

[elsuket]

En effet SQL est un langage ensembliste, comme nous l'avons vu, mais aussi déclaratif : c'est-à-dire que l'on décrit seulement le résultat que l'on souhaite obtenir, mais pas la façon dont on souhaite l'obtenir comme dans les langages fonctionnels. La façon dont le résultat est construit revient à l'optimiseur de requêtes.

Cela étant, je ne comprend toujours pas l'intérêt du trigger dans ce cas de figure. Par ailleurs, le code de SQLPro ne traite pas le retrait d'un chercheur ...

@++

[liseprachan]

Les triggers ne sont surement pas utiles, c'est un cas d'école pour travailler les déclencheurs.

[Sergejack]

Encore une fois, faites des requêtes ENSEMBLISTES !!! Ensembliste veut dire pas de boucle ni de variable unitaire (une variable unitaire n'est pas ensembliste...)

Donc, une seule requête POINT BARRE !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER update_nb_chercheur 
ON dbo.chercheur
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
 
    UPDATE T
    SET    nb_chercheurs = nb_chercheurs +1
    FROM   dbo.specialite AS T
           INNER JOIN inserted AS I
                 ON T.acronyme = i.specialite_chercheur;
END

N'oubliez pas de préfixez les objets de la base par dbo (schémas SQL).

A +

À propos d'ensemblisme, +1, vraiment ?!