Discussion :

[Sergejack]

Bonjour,

Je n'arrive pas à comprendre en quoi un trigger que j'ai écrit est mal rédigé.
En effet, lorsque que j'essaie d'exécuter le query de création, j'ai cette erreur qui se manifeste :

Msg 4104, Level 16, State 1, Procedure tMembers_AFUPDATE, Line 25
The multi-part identifier "I.Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure tMembers_AFUPDATE, Line 25
The multi-part identifier "D.FederalNumber" could not be bound.

Voici mon code :

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
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tMembers_AFUPDATE]'))
DROP TRIGGER [dbo].[tMembers_AFUPDATE]
GO

CREATE TRIGGER tMembers_AFUPDATE
   ON  tMembers
   AFTER UPDATE
AS 
BEGIN
	/*
	A member with no FederalNumber is a member know of Mygolf only.
	So we're sure we have to send a synchro message when the federal number is set with an UPDATE.

	Members from other system than mygolf, will be created (INSERT) with a federalnumber.
	*/

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @SynchroMembers TABLE
	(
		IdMember BIGINT
		, IdClub INT
		, Federated BIT
	);

	WITH concernedClubs
	(
		IdClub
	)
	AS
	(
		SELECT
			C.Id
		FROM tClubs AS C
		WHERE C.IsMygolf = 1
		AND C.Federal = 1
	)

	INSERT @SynchroMembers
	(
		IdMember
		, IdClub
		, Federated
	)
	SELECT
		I.Id
		, C.IdClub
		, CASE WHEN D.FederalNumber = '' THEN 0 ELSE 1 END AS Federated
	FROM inserted AS I
	INNER JOIN deleted AS D ON (
		D.Id = I.Id
	)
	CROSS JOIN (
		/*
		Retrieve the MyGolf Home Club for any operation (which are supposed to come from the Home Club).
		Or any MyGolf Club for a federal number attribution.
		*/
		SELECT CM.IdClub
		FROM tClubMembers AS CM
		INNER JOIN concernedClubs AS C ON (
			C.IdClub = CM.IdClub
		)
		WHERE CM.IdMember = I.Id
		AND (
			IsHomeclub = 1
			OR
			D.FederalNumber = ''
		)
	) AS C
	WHERE I.FederalNumber <> ''

[...]

Ce qui est très curieux, c'est que si je retire la CTE, aucune erreur n'est présentée.
Je suis plus que perplexe.

Pouvez-vous m'aider ?

Merci.

[Sergejack]

J'ai trouvé une syntax qui fonctionne.

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
 
DECLARE @SynchroMembers TABLE
	(
		IdMember BIGINT
		, IdClub INT
		, Federated BIT
	);
 
	WITH concernedClubs
	(
		IdClub
	)
	AS
	(
		SELECT
			C.Id
		FROM tClubs AS C
		WHERE C.IsMygolf = 1
		AND C.Federal = 1
	)
 
	INSERT @SynchroMembers
	(
		IdMember
		, IdClub
		, Federated
	)
	SELECT
		I.Id
		, CM.IdClub
		, CASE WHEN D.FederalNumber = '' THEN 0 ELSE 1 END AS Federated
	FROM inserted AS I
	INNER JOIN deleted AS D ON (
		D.Id = I.Id
	)
	INNER JOIN tClubMembers AS CM ON (
		/*
		Retrieve the MyGolf Home Club for any operation (which are supposed to come from the Home Club).
		Or any MyGolf Club for a federal number attribution.
		*/
		CM.IdMember = I.Id
		AND
		(
			IsHomeclub = 1
			OR
			D.FederalNumber = ''
		)
		AND
		EXISTS (
			SELECT TOP(1) 1
			FROM concernedClubs AS C
			WHERE C.IdClub = CM.IdClub
		)
	)
	WHERE I.FederalNumber <> ''

Faut croire, qu'on ne peut pas faire de sous select liée aux tables jointes à l'extérieur du CROSS JOIN.
Dommage...

[elsuket]

Bonjour,

Quel est l'intérêt d'utiliser une expression de table commune pour peupler une variable de type table ?

@++

[Sergejack]

Alléger l'écriture, en isolant un concept, en limitant l'indention, en évitant la réecriture si la table était accédée par plusieurs jointures de portées distinctes.