Discussion :

[romulus]

Bonjour,

Je souhaite créer un trigger sous MSSQL et je suis paumé avec la syntaxe et les mots-clés qui peuvent être employés (j'ai l'habitude de ce genre d'exercice sous Oracle mais pas sous MS).

Donc, pourriez-vous m'indiquer quelques sources vraiment explicites concernant les triggers et l'utilisation de code procédural sous MSSQL ?
(J'en ai trouvé quelques unes mais rien de bien détaillé... Dans le genre j'explique à quelqu'un qui sait déjà faire...)

J'ai accès au MSDN et je vais me pencher dessus dès Lundi.

En attendant, je vous explique ce que je veux faire et vous colle le code du trigger que j'ai commencé :

-Donc, j'ai une table Question dans laquelle on trouve une colonne 'type', une colonne 'Proposition3FR' et une colonne 'Proposition4FR' (entre autres...).
-Je souhaite, lors d'un INSERT ou UPDATE sur cette table, déclencher un trigger qui va vérifier la valeur de 'type' et affectera, en conséquence, une valeur prédéfinie à 'Proposition3FR' et 'Proposition4FR'.

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
 
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
BEGIN
 
      IF UPDATE (type)
	IF type = 'QCM VRAI-FAUX' OR type='chutier V/F'
		Proposition3FR = NULL
		Proposition4FR = NULL
 
      	IF type = 'QCM 3' OR type = 'chutier QCM3'
		Proposition3FR = '.'
		Proposition4FR = NULL
 
       	IF type = 'QCM 4' OR type = 'chutier QCM4'
		Proposition3FR = '.'
		Proposition4FR = '.'
 
END

Si vous pouvez m'aider à compléter, corriger ce code afin d'obtenir le résultat attendu tout en m'expliquant rapidement les tenants et aboutissants, ce serait super cool

Merci.

[elsuket]

Bonjour,

Vous pouvez télécharger ou consulter la documentation complète de SQL Server.

La commande CREATE TRIGGER est détaillée ici.

Voici à quoi devrait ressembler votre déclencheur :

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
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
BEGIN
	IF UPDATE(type)
	BEGIN
		IF type = 'QCM VRAI-FAUX' OR type = 'chutier V/F'
		BEGIN
			UPDATE dbo.Question
			SET Proposition3FR = NULL,
				Proposition4FR = NULL
			FROM dbo.Question Q
			JOIN INSERTED I ON I.ID = Q.ID
		END
 
		IF type = 'QCM 3' OR type = 'chutier QCM3'
		BEGIN
			UPDATE dbo.Question
			SET Proposition3FR = '.'
				Proposition4FR = NULL
			FROM dbo.Question Q
			JOIN INSERTED I ON I.ID = Q.ID
		END
 
		IF type = 'QCM 4' OR type = 'chutier QCM4'
		BEGIN
			UPDATE dbo.Question
			SET Proposition3FR = '.'
				Proposition4FR = '.'
			FROM dbo.Question Q
			JOIN INSERTED I ON I.ID = Q.ID
		END
	END
END

La table virtuelle INSERTED (et DELETED) possède strictement la même structure que la table à laquelle est attachée le déclencheur.
Pour faire votre mise à jour, il vous suffit donc d'effectuer une jointure entre cette table virtuelle et votre table cible.

Vous devez délimiter chaque traitement par les mots-clé BEGIN et END.
Sinon, vous allez exécuter la commande juxtaposée au test du IF si celui est vrai + toutes les commandes qui suivent, quel que soit le résultat de test du IF.

Il manque le traitement en cas d'insertion, ou bien votre trigger devrait être un "FOR UPDATE"

A+

[SAHARBI]

SALUT
tester le code suivant :

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
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
BEGIN
 
	DECLARE @vType varchar(50),@MonID int
 
	SELECT  @vType=type,@MonID=MonID from INSERTED
 
	IF @vType = 'QCM VRAI-FAUX' OR @vType='chutier V/F'
	BEGIN
		UPDATE Question SET Proposition3FR = NULL, Proposition4FR = NULL WHERE @MonID=MonID
	END
 
      	IF @vType = 'QCM 3' OR @vType = 'chutier QCM3'
	BEGIN	
		UPDATE Question SET Proposition3FR = '.', Proposition4FR = NULL WHERE @MonID=MonID
 
 	END
 
       	IF @vType = 'QCM 4' OR @vType = 'chutier QCM4'
	BEGIN
		UPDATE Question SET Proposition3FR = '.', Proposition4FR = '.' WHERE @MonID=MonID
 
	END
 
END

[SQLpro]

En une seule requête :

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
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
IF UPDATE (type)
   UPDATE dbo.Question
   SET    Proposition3FR = (SELECT CASE 
                                      WHEN type = 'QCM 4' OR type = 'chutier QCM4'      THEN '.'
                                      WHEN type = 'QCM 3' OR type = 'chutier QCM3'      THEN '.'
                                      WHEN type = 'QCM VRAI-FAUX' OR type='chutier V/F' THEN NULL
                            FROM inserted i WHERE Q.ID = i.id),
          Proposition4FR = (SELECT CASE 
                                      WHEN type = 'QCM 4' OR type = 'chutier QCM4'      THEN '.'
                                      WHEN type = 'QCM 3' OR type = 'chutier QCM3'      THEN NULL
                                      WHEN type = 'QCM VRAI-FAUX' OR type='chutier V/F' THEN NULL
                            FROM inserted i WHERE Q.ID = i.id)
 
   FROM dbo.Question Q
        INNER JOIN inserted j 
              ON j.ID = Q.ID

A +

[elsuket]

Saharbi, votre trigger n'est pas ensembliste.
Donc, lorsque l'on va insérer ou mettre à jour plusieurs lignes, les traitements effectués par votre trigger ne porteront que sur la dernière ligne qui vient d'être insérée ou mise à jour, et non pas sur toutes les lignes !

[SQLpro]

Contrairement à Oracle, les déclencheurs SQL Server sont uniquement ensembliste. Pas de trigger FOR EACH ROW. Votre code ne doit donc pas comporter de variables, sauf à faire appel à un curseur (à éviter si vous voulez des performances).

A +

[romulus]

Ok, merci à tous pour vos réponses

J'ai testé les différents codes transmis.

Je ne parviens pas à compiler le code de SQLPro ni de Elsuket car j'obtiens les erreurs suivantes (par exemple pour le code de SQLPro) :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
/*-----------------------------
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
-----------------------------*/
Serveur : Msg 156, Niveau  15, État 1, Procédure tgSetPropFRselonType, Ligne 10
Syntaxe incorrecte vers le mot clé 'FROM'.
Serveur : Msg 156, Niveau  15, État 1, Procédure tgSetPropFRselonType, Ligne 15
Syntaxe incorrecte vers le mot clé 'FROM'.

Je ne vois pas ce que peut être le problème... Apparement, la table INSERTED et son alias i ne semblent pas plaire au compilateur...


Pour ce qui est du code de Saharbi, j'ai modifié MonID par ID (afin que cela corresponde à la table). => Trigger compilé et fonctionnel !

J'obtiens, avec ce code les résultats escomptés, cependant, je ne comprend pas très bien la notion d'ensemblisme et de son impact dans mon cas...
Alors si vous indiquez que celui-ci peut poser pb, je m'inquiète...


Relativement au traitement, je souhaite effectivement que le déclencheur soit opérationnel aussi bien en insert qu'en update... A priori, tel que Elsuket l'indique, il manque le traitement pour l'insertion...
Pour traiter l'insertion, le fait de modifier l'événement déclencheur pour le passer en AFTER au lieu de FOR me permettrait-il de ne pas avoir à ajouter de code ?

Désolé pour ces questions un peu 'bateau', mais en passant sous microsoft, j'ai l'impression de devoir tout réapprendre....

[elsuket]

Bonjour,

Essayez le code suivant :

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
CREATE TRIGGER [tgSetPropFRselonType] ON [dbo].[Question] 
FOR INSERT, UPDATE 
AS
BEGIN
	IF UPDATE (type)
	   UPDATE dbo.Question
	   SET Proposition3FR = CASE 
					WHEN I.type = 'QCM 4' OR I.type = 'chutier QCM4' THEN '.'
					WHEN I.type = 'QCM 3' OR I.type = 'chutier QCM3' THEN '.'
					WHEN I.type = 'QCM VRAI-FAUX' OR I.type = 'chutier V/F' THEN NULL
				END,
		Proposition4FR = CASE 
					WHEN I.type = 'QCM 4' OR I.type = 'chutier QCM4' THEN '.'
					WHEN I.type = 'QCM 3' OR I.type = 'chutier QCM3' THEN NULL
					WHEN I.type = 'QCM VRAI-FAUX' OR I.type = 'chutier V/F' THEN NULL
				END
		FROM dbo.Question Q
		JOIN INSERTED I ON Q.ID = I.ID
END

Le code d'un déclencheur doit être ensembliste pour pouvoir fonctionner lorsque vous effectuez un UPDATE ou INSERT de plusieurs lignes.
En effet dans le code du trigger de Saharbi, on réalise la mise à jour à partir de @MonID : si l'on fait une insertion ou une mise à jour de votre table avec plusieurs lignes, à votre avis combien de valeurs peut prendre @MonID ?
Réponse : une seule, et ce sera l'ID de la dernière ligne insérée ou mise à jour. Donc pour toutes les autres lignes, la traitement du trigger ne se fera pas.

Au début de votre trigger vous avez spécifié :

Code :

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

IF UPDATE (type)

Donc le traitement de celui-ci ne s'effectuera que lors d'une commande UPDATE affectant la colonne type de la table Question.
Si ce n'est pas le traitement que vous souhaitez, alors enlevez cette ligne.

a+

[romulus]

Merci pour tous vos conseils

Elsuket, J'ai donc mis en place la dernière version du trigger et il fonctionne très bien.

Je vais avoir du mal à me mettre en tête le fonctionnement de MSSQL car avec Oracle et son PL/SQL, c'est nettement plus simple et intuitif (ce n'est que mon avis)... Mais rien n'est impossible à qui se donne la peine

Pour info, après avoir regardé dans la doc, j'avais décidé d'utiliser IF UPDATE(type) pour déclencher le code en cas de modification/instanciation du champs type que ce soit en insert ou en update, et je confirme qu'il est fonctionnel tant en insertion qu'en update.

Encore merci. Vos remarques additionnelles sont les bienvenues.

[elsuket]

Bonjour,

Pourriez-vous nous donner un exemple de trigger sous Oracle ? Que je comprenne pourquoi vous trouvez ceux-ci nettement plus simples et intuitifs

[romulus]

Je n'en ai pas de perso sous la main car j'ai changé de société récemment, mais le PL/SQL permet de manipuler les données de façon plus aisée, à mon sens Evidement, cela est directement relatif à mon expérience sur ce SGBDR et au fait que j'ai démarré l'utilisation des triggers avec celui-ci.

Mais en 2 mots, là où il faut requêter une base Inserted avec jointure dans MSSQL, il suffit simplement de préfixer les champs par :NEW ou :OLD dans Oracle...

Un exemple capté sur Développez :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE OR REPLACE TRIGGER TRG_PRIME_ENFANT
before UPDATE
ON employes        -- sur la table EMPLOYES
FOR each row       -- pour chaque ligne
begin
  IF (:new.NB_ENF !=  :old.NB_ENF)   then
   :new.salaire := :old.salaire +200 *(:NEW.NB_ENF -  :OLD.NB_ENF) ;
  end IF ;
end ;

Avoues que c'est quand même moins 'saoûlant' que la syntaxe microsoftienne qui oblige à faire 10 lignes de code là où 3 suffisent dans un autre langage... (évidement, je suis parti pris et cet avis n'engage que moi car je passe de PHP/Oracle à VS2008/MSSQL et VS2008/MYSql .... Mais je vais m'y faire )

Toujours est-il, encore merci Et promis, je vais essayer de ne pas user de démagogie sur Microsoft (le debugger étant par exemple un superbe outil)

[elsuket]

Bonsoir,

Tous à priori mis à part, car je ne défends pas une marque plus qu'une autre, je vous accorde que la syntaxe d'Oracle est plus simple, mais moins normative et surtout "ligne à ligne".

Je ne sais pas si dans les faits les triggers sous Oracle manipulent des ensembles de lignes ou bien procèdent ligne à ligne, mais les SGBDR, à mon sens, doivent manipuler des ensembles de données et non pas faire du "ligne à ligne" comme on peut le faire par exemple avec un curseur : ce qui me pose le doute est la clause "FOR EACH ROW". Voyez ce que cela pose comme problème en cherchant RBAR avec votre moteur de recherche préféré.
C'est pourquoi je préfère la syntaxe que propose SQL Server, dans laquelle il n'y a pas de doute : on manipule bien des ensembles.

J'avoue cependant que si je devais passer à Oracle, j'aurais du mal aussi(je n'ai pas vu la syntaxe que vous nous montrez depuis l'IUT ... ). Alors bon courage et revenez quand vous voulez