Problème exécution trigger after update

**Fabien85** · 03/01/2009, 01h19

Bonjour
J'ai un problème lors de l'exécution d'un trigger sur SQL Server 2005 Express Edition.
Le 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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
create trigger tr_suppr_rencontrer_update on RENCONTRER
after update
as
    declare @nbProjet as smallint
 
    if update(numProjet)
    begin    
        declare @numProjet as int
 
        select @numProjet=numProjet
        from inserted
 
        select @nbProjet=count(numProjet)
        from RENCONTRER
        where numProjet=@numProjet
 
        if (@nbProjet=0)
        begin
            delete from PROJET
            where numProjet=@numProjet
        end;
    end;
 
    if update(numCommercial)
    begin
        declare @estActif as char(1)
        declare @numCommercial as int
 
        select @numCommercial=numCommercial
        from inserted
 
        select @nbProjet=count(numProjet)
        from RENCONTRER
        where numCommercial=@numCommercial
 
        select @estActif=estActif
        from COMMERCIAL
        where numCommercial=@numCommercial
 
        if (@nbProjet=0)and(@estActif='0')
        begin
            delete from COMMERCIAL
            where numCommercial=@numCommercial
        end;
    end;
 
    if update(numInterlocuteur)
    begin    
        declare @numInterlocuteur as int
        declare @nbInterlocuteur as smallint
 
        select @numInterlocuteur=numInterlocuteur
        from inserted
 
        select @nbInterlocuteur=count(numInterlocuteur)
        from RENCONTRER
        where numInterlocuteur=@numInterlocuteur
 
        if (@nbInterlocuteur=0)
        begin
            delete from INTERLOCUTEUR
            where numInterlocuteur=@numInterlocuteur
        end;
    end;

Ce trigger devrait normalement permettre, suite à la mise à jour du champ numProjet, de supprimer l'ancien projet si ce dernier n'est plus utilisé dans la base (clé étrangère) ainsi que faire la même chose pour la table interlocuteur et commercial. Or, même si je modifie le numéro de projet (ou un autre) sur ma table RENCONTRER et que l'ancien numéro n'est plus utilisé, la suppression ne s'effectue pas. Ce qui est le plus bizarre, c'est que j'ai presque le même trigger after delete sur cette table et que celui-ci fonctionne très bien. Je n'y comprend rien!

Merci d'avance si vous arrivez à résoudre mon problème!

**SQLpro** · 03/01/2009, 16h41

Bonjour,

sachez que votre trigger est complètement faux, car , les triggers étant par nature ensembliste dans SQL Server, le code que vous faites ne portera que sur une seule ligne au hasard et non toutes les lignes qui auront été visé par l'ordre SQL de mise à jour.

De plus pour que nous puissions comprendre ce qui se passe et vous corriger :
1) il nous faudrait un exemple de ce qui cloche (données SQL)
2) il nous faudrait la description des tables sous forme SQL
En cela vous ne ferez que vous conformer à la charte de postage de ce forum :
http://www.developpez.net/forums/d96...vement-poster/

Enfin, merci d'indenter votre code et vous servir de la balise CODE.

A +

**Fabien85** · 03/01/2009, 18h46

Bonjour,
Désolé mais je suis débutant en programmation et nouveau dans ce forum. Comme je l'ai dit dans mon précédent message. Ce trigger marche très bien avec un after delete, ce qui me paraît bizarre.
Je vais essayer d'être plus précis.

Premièrement, voici le modèle relationnel de mes tables :

COMMERCIAL(numCommercial,nomCommercial,prenomCommercial)

numCommercial:clé primaire

PROJET(numProjet,dateLivraison,estimatifProjet,echeancierProchContact)

numProjet:clé primaire

INTERLOCUTEUR(numInterlocuteur,nomInterlocuteur,prenomInterlocuteur,numClient)

numInterlocuteur:clé primaire

numClient:clé étrangère en référence à CLIENT

RENCONTRER(numCommercial,numProjet,numInterlocuteur,dateVisite,objVisite,remarques)

numCommercial,numProjet,numInterlocuteur:clé primaire

numCommercial: clé étrangère en référence à COMMERCIAL

numProjet: clé étrangère en référence à PROJET

numInterlocuteur: clé étrangère en référence à INTERLOCUTEUR

CLIENT(numClient,typeClient,refMagasin,nomSociete)

numClient: clé primaire

Si je modifie sur ma table RENCONTRER le numéro de projet (je remplace admettons le projet 53 par le 57), je voudrais que le projet 53 se supprime dans la table PROJET dans le cas où il n'est plus utilisé dans la table RENCONTRER (car il n'est dans ce cas plus utile). C'est pour cela que dans mon trigger:
1- je récupérais l'ancien numéro de projet si ce dernier avait été modifié
2- je comptais le nombre de fois où il était utilisé dans ma table RENCONTRER
3- et si il n'était jamais utilisé (@nbProjet=0) alors j'effectuais la suppression dans la table PROJET.

Il faut croire que ce n'est pas la meilleure manière de procéder mais comment dois-je m'y prendre alors?

Merci de m'aider

**SQLpro** · 04/01/2009, 12h58

je voudrais que le projet 53 se supprime dans la table PROJET dans le cas où il n'est plus utilisé dans la table RENCONTRER

Comment cela se caractrérise t-il ? :

plus de ligne dans la table pour la référence ?
Date obsolète ?

Bref quel est le critère qui vous permet de dire cela ???

A +

**Fabien85** · 04/01/2009, 18h35

Je me base sur le fait qu'il n'y a plus de ligne dans la table RENCONTRER pour la référence. De plus, je me base sur les mêmes critères pour la table INTERLOCUTEUR. C'est pour cela que je fait un count(numProjet) dans ma table RENCONTRER pour voir si la référence du projet est encore utilisée.

A+

**SQLpro** · 05/01/2009, 08h14

La solution devrait être :

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
delete from COMMERCIAL
where  numCommercial IN (SELECT numCommercial
                         from   inserted AS I
                                INNER JOIN COMMERCIAL AS C
                                     ON I.numCommercial = C.numCommercial
                                LEFT OUTER JOIN RENCONTRER AS R
                                     ON I.numCommercial = R.numCommercial
                         WHERE  estActif = 0
                           AND  R.numCommercial IS NULL);
delete from INTERLOCUTEUR
where numInterlocuteur = (SELECT numInterlocuteur 
                          from   inserted AS I
                                 LEFT OUTER JOIN RENCONTRER AS R
                                      ON I.numInterlocuteur = R.numInterlocuteur
                            AND  R.numInterlocuteur IS NULL);

Vous n'arriverez à rien si vous mettez des variables dans un trigger du fait de la nature ensembliste de ce dernier.

A +

**elsuket** · 05/01/2009, 12h26

Bonjour,

Vous pouvez lire le billet que j'ai écrit sur l'écriture ensembliste de triggers, et les effets produits par une écriture non-ensembliste de ceux-ci.

@++

**Fabien85** · 07/01/2009, 23h29

Bonjour

Malheureusement, le trigger proposé ne fonctionne pas. De plus, je connaît très mal les INNER JOIN et LEFT OUTER JOIN, ce qui fait que je n'arrive pas à corriger le problème. Maintenant, dès que je modifie une donnée de la table RENCONTRER (même si cela n'affecte pas les clés étrangères comme quand je modifie le champ remarques par exemple), SQL Server génère une erreur liée au fait que l'instruction DELETE est en contrainte avec les clés étrangères (pourtant, aucun delete ne devrait s'effectuer lorsque je modifie seulement le champ remarques). Je vous envoie ci-joint une capture d'écran du message d'erreur généré. Le message d'erreur ne se génère pas pour la table COMMERCIAL mais c'est parce qu'il y a en plus la ligne:

Code SQL :

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

WHERE  estActif = 0

Savez-vous d'où provient cette erreur?

Merci d'avance

PS: Je tiens aussi à remercier elsuket pour son tuto très bien expliqué sur l'écriture ensembliste des triggers. Cela m'a permis de voir à quel point mon premier trigger était faux!

**elsuket** · 08/01/2009, 11h38

Bonjour,

En ce qui concerne les jointures, voici un exemple qui devrait vous éclairer :

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
CREATE TABLE A
(
	A TINYINT
)
GO
 
CREATE TABLE B
(
	B TINYINT
)
GO
 
INSERT INTO A VALUES (1)
INSERT INTO A VALUES (2)
INSERT INTO A VALUES (3)
INSERT INTO A VALUES (4)
INSERT INTO A VALUES (5)
GO
 
INSERT INTO B VALUES (1)
INSERT INTO B VALUES (2)
INSERT INTO B VALUES (3)
GO
 
-- Equi-jointure
SELECT A.A, B.B
FROM A
INNER JOIN B ON B.B = A.A
GO
 
-- Demi-jointure gauche OU jointure externe gauche
SELECT A.A, B.B
FROM A
LEFT OUTER JOIN B ON B.B = A.A
GO
 
INSERT INTO B VALUES (6)
GO
 
-- Demi-jointure droite OU jointure externe droite
SELECT A.A, B.B
FROM A
RIGHT OUTER JOIN B ON B.B = A.A
GO
-- Jointure externe complète
SELECT A.A, B.B
FROM A
FULL OUTER JOIN B ON B.B = A.A
 
-- Jointure croisée (produit cartésien)
SELECT A.A, B.B
FROM A
CROSS JOIN B

Les jointures permettent de récupérer les données de plusieurs tables en les liant logiquement suivant les relations qui existent entre elles (clés primaires, clés étrangères).
Comme dans l'exemple, il existe plusieurs types de jointure :

- Une équi-jointure, spécifiée en T-SQL par [INNER] JOIN, vous retournera les lignes pour lesquelles chaque clé des deux tables sur laquelle vous effectuez la jointure existe dans les deux tables.
Les clés n'existant pas dans l'une ou dans l'autre table sont exclues.

- Une demi-jointure, spécifiée en T-SQL par LEFT|RIGHT [OUTER] JOIN, vous retournera toutes les lignes de la table à gauche|droite de l'opérateur de jointure, y compris celles qui n'ont pas de correspondance dans la table à droite|gauche de l'opérateur de jointure.

- Une jointure externe complète, spécifiée en T-SQL par FULL [OUTER] JOIN vous retournera tous les lignes des deux tables, y compris celles qui n'ont pas de correspondance dans la table à gauche ou à droite de l'opérateur de jointure

- Une jointure croisée, spécifiée en T-SQL par CROSS JOIN, effectue le produit cartésien de deux tables.

@++

**elsuket** · 08/01/2009, 11h58

C'est bizarre que le trigger de SQLPro ne fonctionne pas.
Je vous en ai fait un plus découpé :

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
CREATE TRIGGER tr_suppr_rencontrer_update
	ON RENCONTRER
AFTER UPDATE
AS
BEGIN 
    IF UPDATE(numProjet)
	BEGIN
		WITH
			CTE_NbProjets AS
			(
				SELECT numProjet, COUNT(*)
				FROM RENCONTRER R
				JOIN INSERTED I ON I.numProjet = R.numProjet
				GROUP BY numProjet
				HAVING COUNT(*) = 0
			)
		DELETE FROM PROJET
		FROM PROJET P
		INNER JOIN CTE_NbProjets C ON C.numProjet = P.numProjet
	END
 
    IF UPDATE(numCommercial)
	BEGIN
		WITH
			CTE_Commercial AS
			(
				SELECT C.numCommercial, C.estActif, COUNT(R.*) nbProjets
				FROM COMMERCIAL C
				INNER JOIN RENCONTRER R ON C.numCommercial = R.numCommercial
				INNER JOIN INSERTED I ON I.numCommercial = C.numCommercial
				WHERE C.estActif = '0'
				GROUP BY C.numCommercial, C.estActif
				HAVING COUNT(*) = 0
			)
		DELETE FROM COMMERCIAL
		FROM COMMERCIAL COM
		INNER JOIN CTE_Commercial CTE ON CTE.numCommercial = COM.numCommercial
	END
 
    IF UPDATE(numInterlocuteur)
	BEGIN
		WITH
			CTE_NbInterlocuteurs AS
			(
				SELECT numInterlocuteur, COUNT(*)
				FROM RENCONTRER R
				JOIN INSERTED I ON I.numInterlocuteur = R.numInterlocuteur
				GROUP BY numInterlocuteur
				HAVING COUNT(*) = 0
			)
		DELETE FROM INTERLOCUTEUR
		FROM INTERLOCUTEUR I
		INNER JOIN CTE_NbInterlocuteurs C ON C.numInterlocuteur = I.numInterlocuteur
	END
END

@++

**Fabien85** · 08/01/2009, 17h05

J'ai essayé ce dernier trigger mais j'ai été obligé de le modifier quelque peu. SQL Server générait des erreurs lors de l'exécution de la création du trigger
(notamment pour le with de PROJET et INTERLOCUTEUR ainsi que pour des numProjet et numInterlocuteur ambigüs).
Voici le trigger modifié:

Code SQL :

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
ALTER TRIGGER [dbo].[tr_suppr_rencontrer_update]
	ON [dbo].[RENCONTRER]
AFTER UPDATE
AS
BEGIN 
    IF UPDATE(numProjet)
	BEGIN
		WITH
			CTE_NbProjets(numProjet,nbProjets) AS
			(
				SELECT R.numProjet, COUNT(*)
				FROM RENCONTRER R
				JOIN INSERTED I ON I.numProjet = R.numProjet
				GROUP BY R.numProjet
				HAVING COUNT(*) = 0
			)
		DELETE FROM PROJET
		FROM PROJET P
		INNER JOIN CTE_NbProjets C ON C.numProjet = P.numProjet
	END
 
    IF UPDATE(numCommercial)
	BEGIN
		WITH
			CTE_Commercial AS
			(
				SELECT C.numCommercial, C.estActif, COUNT(*) nbProjets
				FROM COMMERCIAL C
				INNER JOIN RENCONTRER R ON C.numCommercial = R.numCommercial
				INNER JOIN INSERTED I ON I.numCommercial = C.numCommercial
				WHERE C.estActif = '0'
				GROUP BY C.numCommercial, C.estActif
				HAVING COUNT(*) = 0
			)
		DELETE FROM COMMERCIAL
		FROM COMMERCIAL COM
		INNER JOIN CTE_Commercial CTE ON CTE.numCommercial = COM.numCommercial
	END
 
    IF UPDATE(numInterlocuteur)
	BEGIN
		WITH
			CTE_NbInterlocuteurs(numInterlocuteur,nbInterlocuteur) AS
			(
				SELECT R.numInterlocuteur, COUNT(*)
				FROM RENCONTRER R
				JOIN INSERTED I ON I.numInterlocuteur = R.numInterlocuteur
				GROUP BY R.numInterlocuteur
				HAVING COUNT(*) = 0
			)
		DELETE FROM INTERLOCUTEUR
		FROM INTERLOCUTEUR I
		INNER JOIN CTE_NbInterlocuteurs C ON C.numInterlocuteur = I.numInterlocuteur
	END
END

Maintenant aucune erreur n'est déclenchée lorsque je modifie le champ remarques de la table RENCONTRER. Par contre, la suppression pour la table PROJET et la table INTERLOCUTEUR ne fonctionnent pas. De plus, la suppression pour la table COMMERCIAL ne marche que partiellement. En effet, lorque j'effectue la modification du champ numCommercial de la table RENCONTRER (et que l'ancien numéro de commércial avait son champ estActif à 0). La suppression ne s'effectue pas directement. J'ouvre ma table COMMERCIAL et la ligne est toujours présente mais quand je clique sur cette ligne, un point d'exclamation rouge s'inscrit sur la ligne (comme si elle était en lecture seule). Je ferme alors la table COMMERCIAL et je la réouvre: la ligne a alors disparu!

On approche petit à petit de but!!!

**elsuket** · 08/01/2009, 21h40

OK, on en est à deux triggers et ça ne va toujours pas.
Je vous propose de nous donner le DDL de vos tables, et de nous dire ce que vous voulez exactement que le trigger fasse en français seulement, vous allez y gagner du temps

@++

**Fabien85** · 08/01/2009, 23h40

OK Je vous envoie ci-joint le ddl de ma base de données. Les données insérées ne sont que des exemples et donc beaucoup de projets ne sont pas utilisés.
Je veux que lorsque l'on met à jour la table RENCONTRER mon trigger se déclenche et:
- Si on a modifié la clé étrangère numProjet, on vérifie si l'ancienne clé (avant la mise à jour) est encore utilisée dans la table RENCONTRER. Si ce n'est pas le cas (il n'existe désormais aucune référence à l'ancien numéro de projet dans la table RENCONTRER), alors on supprime le projet concerné dans la table PROJET (puisqu'il ne sert plus à rien).
- Si on a modifié la clé étrangère numInterlocuteur, alors on fait la même manipulation que pour la table PROJET, mais cette fois pour la table INTERLOCUTEUR (j'ai un autre trigger qui se charge de supprimer le client correspondant si c'est nécessaire...qui fonctionne!!!).
- Enfin, même manière de procéder pour la clé étrangère numCommercial, à part qu'il faut aussi s'assurer que le champ estActif de l'ancien commercial est à 0 avant de le supprimer dans la table COMMERCIAL (le champ estActif permet de savoir si le commercial est toujours dans l'entreprise ou non. J'ai été obligé de le rajouter car des projets peuvent concerner un commercial, même si celui-ci n'est plus dans l'entreprise).

J'espère que vous comprenez mieux ce que je veux effectuer.

PS: J'ai réalisé le script avec Data Publishing Wizard car sinon, les données n'étaient pas conservées).

**SQLpro** · 09/01/2009, 09h56

Il est normal que vous ayez du mal à faire ce que vous voulez faire, car vous n'êtes pas sur la bonne voie pour le fonctionnel que vous voulez faire.

Il serait beaucoup plus intelligent et plus simple de réaliser cela en pilotant le mode de gestion de l'intégrité référentielle, soit en cascade, set null ou set default, utiliser des vues en lieu et place de vos table et enfin, faire passer un batch de nettoyage de manière cyclique...

A +

**Fabien85** · 09/01/2009, 17h30

le problème c'est qu'un delete cascade suprimerait forcément le projet dans la table PROJET, même s'il est utilisé par une autre ligne de la table RENCONTRER (donc problème lors de la suppression).
Un set null s'effectuerait à partir de table PROJET pour ensuite enlever la clé étrangère dans la table RENCONTRER, alors que l'on veut savoir s'il existe des projets dans la table RENCONTRER pour ENSUITE supprimer dans la table PROJET (et pas l'inverse).
Il en va de même pour le set default.
Quant au batch de nettoyage, il effectuerait en fait la même manipulation que le trigger que j'essaie de créer!

**elsuket** · 11/01/2009, 12h32

Bonjour,

Merci pour le DDL.
En fait vous avez utilisé la table virtuelle INSERTED, qui reflète les nouvelles valeurs appliquées à la table.
Or, vous recherchez celles qui y étaient avant, pour faire d'éventuelles suppressions.
Vous auriez donc du utiliser la table virtuelle DELETED, qui reflète les valeurs présentes dans la table avant l'UPDATE (ou le DELETE).

Voici donc le trigger que j'espère être correct :

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
CREATE TRIGGER TR_AU_RENCONTRER
	ON dbo.RENCONTRER
AFTER UPDATE
AS
BEGIN
	IF UPDATE(numProjet)
	BEGIN
		-- Recherche des rencontres qui viennent d'être mises à jour
		-- et dont il ne restait plus qu'un seul projet
		WITH
			CTE_RENCONTRES_SANS_PROJETS AS
			(
				SELECT numProjet, COUNT(*)
				FROM dbo.RENCONTRER R
				JOIN DELETED D ON D.numProjet = R.numProjet
				GROUP BY numProjet HAVING COUNT(*) = 1
			)
		-- Suppression des projets correspondants
		DELETE FROM dbo.PROJET
		FROM dbo.PROJET P
		JOIN CTE_RENCONTRES_SANS_PROJETS C ON C.numProjet = P.numProjet
	END
 
	IF UPDATE(numInterlocuteur)
	BEGIN
		-- Recherche des rencontres qui viennent d'être mises à jour
		-- et dont il ne restait plus qu'un seul interlocuteur
		WITH
			CTE_RENCONTRES_SANS_INTERLOCUTEUR AS
			(
				SELECT numInterlocuteur, COUNT(*)
				FROM dbo.RENCONTRER R
				JOIN DELETED D ON D.numInterlocuteur = R.numInterlocuteur
				GROUP BY numInterlocuteur HAVING COUNT(*) = 1
			)
		-- Suppression des interlocuteurs correspondants
		DELETE FROM dbo.INTERLOCUTEUR
		FROM dbo.INTERLOCUTEUR I
		JOIN CTE_RENCONTRES_SANS_INTERLOCUTEUR C ON C.numInterlocuteur = I.numInterlocuteur
	END
 
	IF UPDATE(numCommercial)
	BEGIN
		WITH
			CTE_RENCONTRES_SANS_COMMERCIAL AS
			(
				SELECT numCommercial, COUNT(*)
				FROM dbo.RENCONTRER R
				JOIN DELETED D ON D.numCommercial = R.numCommercial
				GROUP BY numCommercial HAVING COUNT(*) = 1
			)
		-- Suppression des commerciaux correspondants,
		-- seulement si ceux-ci ne sont plus dans l'entreprise
		DELETE FROM dbo.COMMERCIAL
		FROM dbo.COMMERCIAL COM
		JOIN CTE_RENCONTRES_SANS_COMMERCIAL C ON C.numCommercial = COM.numCommercial
		WHERE COM.estActif = '0'
	END
END

Pourquoi, dans la table COMMERCIAL, n'avez vous pas utilisé le type BIT à la place d'un CHAR(1) contenant en plus un chiffre ?
Retenez que les comparaisons de nombres sont beaucoup plus rapides que les comparaisons de chaîne, surtout si vous utilisez une collation insensible à la casse.
La différence se fait aussi dans le stockage : vous utilisez actuellement un octet, un bit n'utilise ... qu'un bit

Que se passera-t-il si un jour un utilisateur saisit 2 ? A moins que ce ne soit géré par votre applicatif ...

Si vous voulez mettre à jour la table COMMERCIAL :

Code :

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

1
2
ALTER TABLE dbo.COMMERCIAL
ALTER COLUMN estActif BIT NOT NULL

@++

**Fabien85** · 14/01/2009, 20h01

Bonjour

En effet, je croyais que la table virtuelle inserted servait à la copie des lignes avec un insert ou update et que la table virtuelle deleted servait uniquement pour la copie des lignes avec un delete.
Par contre, ce dernier trigger ne fonctionne toujours pas (eh oui, je suis désolé!!!). En fait maintenant les instructions delete des 3 tables se lancent mais trop tôt :il reste encore une ligne concernée dans la table RENCONTRER. par exemple, il y a 2 lignes dans la table RENCONTRER concernant le projet 53. Je tente de modifier l'une des lignes (il en reste donc une) et l'instruction delete se lance quand même en tentant de supprimer le projet 53 dans la table PROJET, d'où un message d'erreur que j'ai joint avec ce message.
J'ai tenté de modifier le trigger en faisant :

Code SQL :

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

GROUP BY R.numProjet HAVING COUNT(*) = 0

à la place de

Code SQL :

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

GROUP BY R.numProjet HAVING COUNT(*) = 1

mais il ne se passe alors plus rien (les delete ne se déclenchent plus du tout).

Merci aussi de tuyau, je ne savais pas que le traitement d'un char(1) était plus long que celui d'un bit. Etant donné que le type booléen n'existe pas dans SQL Server, j'avais utilisé ce type comme moyen de substitution. Par contre l'utilisateur ne peut pas saisir 2 car c'est l'application qui gère cette modification.

**elsuket** · 14/01/2009, 21h54

Bonjour,

En effet, je croyais que la table virtuelle inserted servait à la copie des lignes avec un insert ou update et que la table virtuelle deleted servait uniquement pour la copie des lignes avec un delete

- La table virtuelle INSERTED reflète les valeurs appliquées lors d'un INSERT ou d'un UPDATE
- La table virtuelle DELETED reflète les anciennes valeurs lors d'un UPDATE ou d'un DELETE.

ce dernier trigger ne fonctionne toujours pas (eh oui, je suis désolé!!!)

Ce n'est pas grave et cela prouve que je ne l'ai pas assez testé

Code :

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

GROUP BY R.numProjet HAVING COUNT(*) = 0

C'est normal, vous essayez alors de compter les projets qui n'existent pas dans la table

L'UPDATE ayant déjà été fait, vous ne pouvez plus le compter.

J'aurais plutôt dû vous proposer de supprimer de la table PROJET les lignes qui n'ont plus de projet dans RENCONTRER, c'est à dire compter dans la table PROJET les projets qui n'ont pas de rencontre

Il en va de même pour INTERLOCUTEUR et COMMERCIAL :
- compter dans la table PROJET les projets qui n'ont plus d'interlocuteur,
- compter dans la table PROJET les projets qui n'ont plus de commercial.

Voici donc le nouveau 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
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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TR_AU_RENCONTRER]
	ON [dbo].[RENCONTRER]
AFTER UPDATE
AS
BEGIN
	IF UPDATE(numProjet)
	BEGIN
		WITH
			CTE_RENCONTRES_SANS_PROJETS AS
			(
				SELECT P.numProjet, COUNT(R.numProjet) NbProjets
				FROM dbo.PROJET P
				LEFT JOIN dbo.RENCONTRER R ON R.numProjet = P.numProjet
				JOIN DELETED D ON D.numProjet = P.numProjet
				GROUP BY R.numProjet, P.numProjet
				HAVING COUNT(R.numProjet) = 0
			)
		-- Suppression des projets correspondants
		DELETE FROM dbo.PROJET
		FROM dbo.PROJET P
		JOIN CTE_RENCONTRES_SANS_PROJETS C ON C.numProjet = P.numProjet
	END
 
	IF UPDATE(numInterlocuteur)
	BEGIN
		WITH
			CTE_RENCONTRES_SANS_INTERLOCUTEUR AS
			(
				SELECT I.numInterlocuteur, COUNT(R.numInterlocuteur) NbInterlocuteurs
				FROM dbo.INTERLOCUTEUR I
				LEFT JOIN dbo.RENCONTRER R ON R.numInterlocuteur = I.numInterlocuteur
				JOIN DELETED D ON D.numInterlocuteur = I.numInterlocuteur
				GROUP BY I.numInterlocuteur
				HAVING COUNT(R.numInterlocuteur) = 0
			)
		-- Suppression des interlocuteurs correspondants
		DELETE FROM dbo.INTERLOCUTEUR
		FROM dbo.INTERLOCUTEUR I
		JOIN CTE_RENCONTRES_SANS_INTERLOCUTEUR C ON C.numInterlocuteur = I.numInterlocuteur
	END
 
	IF UPDATE(numCommercial)
	BEGIN
		WITH
			CTE_RENCONTRES_SANS_COMMERCIAL AS
			(
				SELECT C.numCommercial, COUNT(R.numCommercial) NbCommerciaux
				FROM dbo.COMMERCIAL C
				JOIN dbo.RENCONTRER R ON R.numCommercial = C.numCommercial
				JOIN DELETED D ON D.numCommercial = C.numCommercial
				GROUP BY C.numCommercial
				HAVING COUNT(R.numCommercial) = 0
			)
		-- Suppression des commerciaux correspondants,
		-- seulement si ceux-ci ne sont plus dans l'entreprise
		DELETE FROM dbo.COMMERCIAL
		FROM dbo.COMMERCIAL COM
		JOIN CTE_RENCONTRES_SANS_COMMERCIAL C ON C.numCommercial = COM.numCommercial
		WHERE COM.estActif = '0'
	END
END

J'ai testé celui-ci avec le jeu de données que vous avez fourni, il fonctionne

P.S. : TR_AU_RENCONTRER pour TRigger After Update RENCONTRER

@++

**Fabien85** · 15/01/2009, 17h23

Bonjour

Ca y est, ce trigger fonctionne parfaitement bien!

Il a juste fallu effectuer une toute petite modification pour les commerciaux. J'ai mis:

Code SQL :

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

LEFT JOIN dbo.RENCONTRER R ON R.numCommercial = C.numCommercial

à la place de

Code SQL :

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

JOIN dbo.RENCONTRER R ON R.numCommercial = C.numCommercial

Je vous remercie énormément pour toute l'aide que vous m'avez apportée. Cela a dû vous prendre du temps: Le problème était plutôt coriace!!! De plus, j'ai à présent compris le rôle des tables inserted et deleted ainsi que la subtilité des jointures.

Merci encore

@+