[2005] Voir l'execution plan d'un trigger
Bonjour,
Je travaille avec Sql Server Managment Studio (2005) et j'aimerais savoir comment prévisualiser l'execution plan d'un trigger.
Code:
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
|
CREATE TRIGGER [dbo].[tClubMembers_AFUPDINS]
ON [dbo].[tClubMembers]
AFTER UPDATE, INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
UPDATE tMembers
SET HomeClub = I.IdClub
FROM inserted AS I
WHERE I.IsHomeclub = 1
AND tMembers.Id = I.IdMember
-- to avoid meaningless update (and potential trigger recusrion)
AND tMembers.HomeClub <> I.IdClub
UPDATE tClubMembers
SET IsHomeclub = 0
FROM inserted AS I
INNER JOIN tMembers AS T ON (
T.Id = I.IdMember
)
WHERE I.IsHomeclub = 1
AND tClubMembers.IdMember = I.IdMember
AND tClubMembers.IdClub <> T.HomeClub;
-- Synchro
WITH concernedClubs
(
IdClub
) AS (
SELECT
C.Id
FROM tClubs AS C
WHERE C.IsMygolf = 1
AND C.Federal = 1
)
INSERT tSynchro_BatchMemberLines
(
IdMember
, IdClub
)
SELECT
I.IdMember
, I.IdClub
FROM inserted AS I
INNER JOIN tMembers AS M ON (
M.Id = I.IdMember
AND
M.FederalNumber <> ''
)
WHERE EXISTS (
SELECT TOP(1) 1
FROM concernedClubs AS C
WHERE C.IdClub = I.IdClub
)
AND
(
I.Membership > GETDATE()
OR EXISTS (
SELECT TOP(1) 1
FROM deleted AS D
WHERE D.IdMember = I.IdMember
AND D.IdClub = I.IdClub
AND D.Membership > GETDATE()
)
)
END |
Le problème, si j'utilise "Query" -> "Display estimated execution plan..." est que ce plan sera établit sur la commande CREATE :aie:
Savez-vous comment faire ? (en employant les tables virtuelles inserted et delete, je ne peux hélas pas sortir le query d'une creation de trigger).
