[SQL2005]trigger itératif --> trigger ensembliste

**Kropernic** · 04/10/2012, 15h59

Bonjour,

Suite à la discussion sur l'identification relative du forum administration, je me décide à faire quelques tests histoire de pouvoir juger par moi-même.

Seulement, je bloque déjà au niveau du trigger chargé d'incrémenter cette colonne d'identification relative.
J'ai réussi en m'appuyant sur l'un ou l'autre article un créer un trigger qui fonctionne mais qui est itératif. Et je n'aime pas ça.
J'avais réussi à en créer un ensembliste mais qui ne fonctionnait qu'à moitié dans le sens que, si on voulait insérer un ensemble de lignes, il ne fonctionnait que pour des valeurs de GFT_ID différentes.

Sans plus tarder, un peu de code :
Le DDL de la table

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
/****** Object:  Table [dbo].[T_ACTIVATION_ACT]    Script Date: 10/04/2012 15:48:35 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[T_ACTIVATION_ACT](
    [GFT_ID] [int] NOT NULL, --clef primaire de la table parente
    [ACT_ID] [tinyint] NOT NULL, --colonne d'identification relative
    [DOS_ID] [int] NULL,
    [ACT_DATE] [datetime] NOT NULL,
    [ACT_VALUE] [decimal](6, 2) NOT NULL,
 CONSTRAINT [PK_T_ACTIVATION_ACT] PRIMARY KEY CLUSTERED 
(
    [GFT_ID] ASC,
    [ACT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[T_ACTIVATION_ACT]  WITH CHECK ADD  CONSTRAINT [FK_T_ACTIVATION_ACT_TJ_GFT_DOS_JGD] FOREIGN KEY([GFT_ID], [DOS_ID])
REFERENCES [dbo].[TJ_GFT_DOS_JGD] ([GFT_ID], [DOS_ID])
GO
 
ALTER TABLE [dbo].[T_ACTIVATION_ACT] CHECK CONSTRAINT [FK_T_ACTIVATION_ACT_TJ_GFT_DOS_JGD]
GO

Et le DDL du trigger itératif :

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
/****** Object:  Trigger [dbo].[TR_RELATIVE_IDENTIFICATION_ACT]    Script Date: 10/04/2012 15:48:44 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TRIGGER [dbo].[TR_RELATIVE_IDENTIFICATION_ACT] ON [dbo].[T_ACTIVATION_ACT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT 
            ROW_NUM = ROW_NUMBER() OVER(ORDER BY GFT_ID),
            *
    INTO    
            #INS
    FROM
            INSERTED;        
 
    DECLARE @ITER INT;
    DECLARE @COUNT INT;
 
    SELECT @ITER=MIN(ROW_NUM) FROM #INS;
    SELECT @COUNT=MAX(ROW_NUM) FROM #INS;
 
    WHILE @ITER <= @COUNT
    BEGIN
 
        INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
        SELECT
                INS.GFT_ID,
                COALESCE(MAX(ACT.ACT_ID),0)+1,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE
        FROM
                T_ACTIVATION_ACT ACT
                    RIGHT OUTER JOIN #INS INS
                        ON    ACT.GFT_ID = INS.GFT_ID
        WHERE
                INS.ROW_NUM = @ITER
        GROUP BY
                INS.GFT_ID,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE;
 
        SET @ITER = @ITER+1;
    END
END
 
GO

J'ai encore du mal avec la logique ensembliste. C'est pourquoi je viens chercher votre aide.

Merci d'avance.

**SQLpro** · 04/10/2012, 18h38

Utilisez une CTE d'UPDATE avec la fonction ROW_NUMBER() OVER(PARTITION ... ORDER BY ...) et ajouter la valeur du dernier incrément utilisé.
Pour ce faire vous devez impérativement monter au niveau d'isolation SERIALIZABLE, car il faut empêcher tout ajout de lignes au moment du calcul et de l'attribution des clefs, sinon, le risque de collision est grand...
Inconvénient de tout ceci, plus on monte le niveau d'isolation, et plus les attentes sont longues (donc moins de concurrence possible) et plus la possibilité de survenance d'un verrou mortel augmente. C'est pourquoi je n'utilise ni ne préconise jamais l'identification relative !

A +

**elsuket** · 04/10/2012, 18h43

Bonjour,

Il est tard dans mon pays et, même après avoir relu le sujet original de Kropernic, je ne comprends pas pourquoi on ne peut pas écrire :

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
ALTER TRIGGER dbo.TR_RELATIVE_IDENTIFICATION_ACT
	ON dbo.T_ACTIVATION_ACT
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON
 
	INSERT	INTO dbo.T_ACTIVATION_ACT
	(
		GFT_ID
		, ACT_ID
		, DOS_ID
		, ACT_DATE
		, ACT_VALUE
	)
	SELECT		INS.GFT_ID
			, COALESCE(MAX(ACT.ACT_ID), 0) + 1
			, INS.DOS_ID
			, INS.ACT_DATE
			, INS.ACT_VALUE
	FROM		INSERTED AS INS
	INNER JOIN	dbo.T_ACTIVATION_ACT AS A
				ON INS.GFT_ID = A.GFT_ID
	GROUP BY	INS.GFT_ID
			, INS.DOS_ID
			, INS.ACT_DATE
			, INS.ACT_VALUE;
END

Est-ce que je dois réellement aller me coucher ?

En ce qui concerne le fait que SQL Server n'utilise que la colonne la plus à gauche d'une statistique pour effectuer une estimation de cardinalités, je me pose toujours la question de savoir pourquoi le vecteur statistique est collecté ...
En effet si on pose un index composite dont les colonnes sont toutes peu sélective, le vecteur, lui, peut le devenir.
De la même façon, j'ai observé certains cas où, pour une raison que je ne suis pas parvenu à établir, SQL Server choisira l'index dont l'ordre des colonnes est de la moins sélective à la plus sélective, alors même que l'index avec les colonnes dans l'ordre inverse n'est pas sélectionné. C'est donc bien que le vecteur est utilisé, mais quand ?

@++

**Kropernic** · 04/10/2012, 20h10

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
ALTER TRIGGER TR_RELATIVE_IDENTIFICATION_ACT 
ON T_ACTIVATION_ACT 
INSTEAD OF INSERT 
AS 
BEGIN
     SET NOCOUNT ON;
     INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, ACT_DATE, ACT_VALUE)
     SELECT
             INS.GFT_ID,
             COALESCE(MAX(ACT.ACT_ID),0)+1,
             INS.ACT_DATE,
             INS.ACT_VALUE
     FROM
             T_ACTIVATION_ACT ACT
                 RIGHT OUTER JOIN INSERTED INS
                     ON    ACT.GFT_ID = INS.GFT_ID
     GROUP BY
             INS.GFT_ID,
             INS.ACT_DATE,
             INS.ACT_VALUE
END

Voilà le code du trigger que j'avais produit à la base. A part la jointure, je ne vois pas de différence.

Pourtant, cela ne fonctionnait pas.

Je faisais mes tests par paquets de 4 insertions.

Avec INNER JOIN, malgré le message me confirmant que 4 lignes avaient bien été insérées, je ne retrouvais rien dans ma table.

Avec RIGHT OUTER JOIN, je retrouvais bien mes lignes mais cela ne fonctionnait que pour des valeurs de GFT_ID différentes. Sans quoi, il tentait d'insérer plusieurs fois le même couple (GFT_ID, ACT_ID) et évidemment, vu qu'il s'agit de la clef primaire, ça ne va pas.

Etant donné qu'il s'agit d'une application qui sera utilisée par relativement peu d'utilisateur et les tables ayant une identification relative ne seront utilisées que par un seul service (composés de 3 personnes), cela ne conviendrait-il tout de même pas malgré les réticences exposés par sqlpro ?

EDIT : La colonne DOS_ID n'est apparue qu'après lorsque je me suis aperçu d'une erreur sur le mcd.

**SQLpro** · 05/10/2012, 09h46

Il es difficile de prévoir l'avenir... Aujourd'hui 3 personnes. Demain peut être zéro comme 100 !
Mettez au moins le niveau d’isolation SERIALIZABLE et redescendez en fin de traitement à READ COMMITTED.

A +

**Kropernic** · 05/10/2012, 09h58

Envoyé par SQLpro

Il es difficile de prévoir l'avenir... Aujourd'hui 3 personnes. Demain peut être zéro comme 100 !

C'est certain mais n'étant pas dans une boite de consultance, nous développons en interne pour notre entreprise. Ce qui veut dire qu'on connait le public cible de nos applications. Et je vous garantis que la tendance est plus à la diminition du personnel que l'inverse

Mettez au moins le niveau d’isolation SERIALIZABLE et redescendez en fin de traitement à READ COMMITTED.

Je n'ai jamais fait cela jusqu'ici, je vais tacher de voir ce qui ce dit sur le sujet. J'étais déjà tombé sur un de vos articles parlant du niveau d'isolation des transactions mais je dois bien avouer que ça m'étais passé un peu par dessus la tête...

**Kropernic** · 05/10/2012, 10h17

Bon, je pense avoir pigé le truc pour les niveaux d'isolation.

Par contre, je bloque toujours sur la CTE d'UPDATE. J'ai bien trouvé un exemple sur le net mais je ne vois pas trop comment l'appliqué dans mon cas.

**Kropernic** · 05/10/2012, 12h05

Bon, j'ai décidé de m'attaquer à un problème à la fois.

J'ai donc commencé par le niveau d'isolation que je pensais avoir compris mais finalement, je me pose quand même des questions.

De ce que j'avais compris, je devais le passer en SERIALIZABLE au début du trigger et le repasser en READ COMMITTED à la fin du trigger.

Ensuite je tombe sur un de vos articles concernant les transactions où vous passer en level SERIALIZABLE au début mais vous n'y toucher pas à la fin.

Est-ce normal dans le contexte de l'exemple ? S'agit-il d'un oubli ? Suis-je passé à côté de quelque chose ?

Merci d'avance.

**SQLpro** · 08/10/2012, 09h18

Tout dépend de l'utilisation qui en est faite.

(L'url ne marche pas)

A +

**Kropernic** · 08/10/2012, 09h23

Envoyé par SQLpro

(L'url ne marche pas)

J'ai mis le lien à jour (chapitre 4.6 de l'article).

**SQLpro** · 08/10/2012, 11h04

Effectivement on peut penser qu'il manque un SET TRANSACTION ISOALTION LEVEL en fin. Mais si la proc est utilisée en mode découplée (appli web par exemple) alors chaque reconnexion recommence en READ COMMITTED (niveau par défaut dans MS SQL Server).

A +

**Kropernic** · 08/10/2012, 11h12

Ok donc si je comprends bien, le niveau d'isolation transactionnel n'est modifié que pour la connexion courante.

Quand on le change dans un trigger, cela a lieu à quel "niveau" alors ?

Exemple pour être sûr que je sois bien clair
- Un user (X) se connecte à la DB ;
- X effectue un insert dans une table qui a un trigger sur l'insertion (peu importe que soit after ou instead of je pense) ;
- dans le trigger, on change le niveau de transaction ;
- la DB renvoie un message comme quoi la ligne a bien été insérée
- X se déconnecte

Dans cet exemple, le changement d'isolation ne se passe-t-il que pour le user X ?

Pour le contexte, je travaille en .NET en mode déconnecté. C-à-d que l'utilisateur se connecte/déconnecte pour chaque action qu'il veut faire en rapport avec la DB.

**SQLpro** · 08/10/2012, 11h16

oui et que pour le code du trigger.

Plus tu monte en niveau d’isolation, plus le verrouillage est intense donc bloquant. pour éviter trop de blocage il ne faut monter en isoaltion :

qu'à bon escient
le moins longtemps possible

Mieux vaut de toute façon prévoir un reset du niveau d’isolation et surtout un bloc TRY CATCH dans toutes les procédures et triggers !

A +

**Kropernic** · 08/10/2012, 11h30

Ah bin puisque vous parlez des blocs TRY...CATCH, j'ai un souci avec ces derniers.

Pour l'exemple, voici le trigger que j'ai produit et dont je cherche toujours une solution ensembliste (vous aviez suggéré une CTE d'UPDATE mais j'ai pas encore réussi à mettre en application).

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
USE [GIFT_MANAGEMENT]
GO
/****** Object:  Trigger [dbo].[TR_INSTEAD_OF_INSERT_ACT]    Script Date: 10/08/2012 11:24:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER TRIGGER [dbo].[TR_INSTEAD_OF_INSERT_ACT] ON [dbo].[T_ACTIVATION_ACT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
 
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 
    BEGIN TRANSACTION
    SELECT 
            ROW_NUM = ROW_NUMBER() OVER(ORDER BY GFT_ID),
            *
    INTO    
            #INS
    FROM
            INSERTED;        
 
    DECLARE @ITER INT;
    DECLARE @COUNT INT;
 
    SELECT @ITER=MIN(ROW_NUM) FROM #INS;
    SELECT @COUNT=MAX(ROW_NUM) FROM #INS;
 
    WHILE @ITER <= @COUNT
    BEGIN
 
        INSERT INTO T_ACTIVATION_ACT(GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
        SELECT
                INS.GFT_ID,
                COALESCE(MAX(ACT.ACT_ID),0)+1,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE
        FROM
                T_ACTIVATION_ACT ACT
                    RIGHT OUTER JOIN #INS INS
                        ON    ACT.GFT_ID = INS.GFT_ID
        WHERE
                INS.ROW_NUM = @ITER
        GROUP BY
                INS.GFT_ID,
                INS.DOS_ID,
                INS.ACT_DATE,
                INS.ACT_VALUE;
 
        SET @ITER = @ITER+1;
    END
 
    DROP TABLE #INS;
 
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION
    ELSE
        COMMIT TRANSACTION
 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
END

Qui en fait le même que dans le premier message avec la transaction et le niveau d'isolation en plus.

J'ai voulu y ajouter un bloc try...catch pour être certain de faire un reset du niveau d'isolation ainsi qu'un rollback mais sql server n'appréciait guère ma manière de faire... Je ne me souviens précisément du message d'erreur mais c'était en rapport avec la transaction. Un truc du genre qu'il ne trouvait pas l'ouverture correspondante au rollback se trouvant dans le bloc catch.

**SQLpro** · 08/10/2012, 14h28

Quelque chose comme ça :

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
ALTER TRIGGER [dbo].[TR_INSTEAD_OF_INSERT_ACT] 
   ON [dbo].[T_ACTIVATION_ACT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
BEGIN TRY
 
    WITH 
    T0 AS (SELECT GFT_ID, DOS_ID, ACT_DATE, ACT_VALUE, ROW_NUMBER() OVER(ORDER BY GFT_ID) AS ROW_NUM
           FROM   inserted)
    INSERT INTO T_ACTIVATION_ACT (GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
    SELECT T0.GFT_ID,
           COALESCE(MAX(ACT.ACT_ID), 0)+1,
           T0.DOS_ID,
           T0.ACT_DATE,
           T0.ACT_VALUE
    FROM   T_ACTIVATION_ACT AS ACT
           RIGHT OUTER JOIN T0 
                 ON    ACT.GFT_ID = INS.GFT_ID
    GROUP BY T0.GFT_ID,
             T0S.DOS_ID,
             T0.ACT_DATE,
             T0.ACT_VALUE;
 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
END TRY
BEGIN CATCH
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    RAISERROR('Erreur dans trigger', 16, 1)
END CATCH
 
END

Attention : un trigger est déjà à l'intérieur de la transaction..... Il ne faut donc pas faire un nouveau BEGIN TRANSACTION....

A +

**Kropernic** · 08/10/2012, 14h51

Ok pour la transaction et le try...catch.

Par contre, le trigger ne fonctionne pas. Enfin si, il ne génère pas d'erreur mais les valeurs dans ACT_ID ne sont pas correct.

J'ai fait une copie de la table T_ACTIVATION_ACT (en enlevant toutes les contraintes histoire d'être certain que l'insertion fonctionne) et j'ai executé cet ordre d'insertion :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO T_TEST_TST(GFT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
SELECT 1,1,GETDATE(),101
UNION ALL
SELECT 1,2,GETDATE(),102
UNION ALL
SELECT 1,3,GETDATE(),103
UNION ALL
SELECT 2,1,GETDATE(),201
UNION ALL
SELECT 3,1,GETDATE(),301
UNION ALL
SELECT 2,2,GETDATE(),202

Le résultat attendu est une incrémentation d'ACT_ID en fonction de GFT_ID.
Ici, le résultat est le même que le premier trigger que j'avais produit et qu'elsuket a proposé également.

Dans cet exemple, ACT_ID a pour valeur 1 dans toutes les lignes alors que cette valeur devrait aller de 1 à 3 pour GFT_ID = 1 et de 1 à 2 pour GFT_ID = 2.

Evidemment, en pratique, l'insertion de plusieurs lignes en même temps pour la même valeur de GFT_ID n'arrivera jamais. Mais bon, j'aime bien que les choses fonctionnent dans tous les cas, au cas où. Comme vous le dites, difficiles de prévoir l'avenir...

EDIT : Je viens de réaliser que vous n'utilisez pas la colonne ROW_NUM... C'est probablement pour ça... (Ca m'apprendra à exécuter bêtement sans regarder).
Je reteste^^

**Kropernic** · 08/10/2012, 15h11

Et donc ça fonctionne bien sûr parfaitement.

J'ai juste du utiliser ROW_NUM au lieu de 1 pour l'incrémentation qui se trouve dans COALESCE et j'ai aussi du ajouter la clause PARTITION BY GFT_ID dans la fonction de fenêtrage.

Voilà le code "corrigé" pour ceux que cela intéresse :

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
ALTER TRIGGER [dbo].[TR_INSTEAD_OF_INSERT_TST] 
   ON [dbo].T_TEST_TST
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
BEGIN TRY;
 
    WITH 
    T0 AS (SELECT GFT_ID, DOS_ID, ACT_DATE, ACT_VALUE, ROW_NUMBER() OVER(PARTITION BY GFT_ID ORDER BY GFT_ID) AS ROW_NUM
           FROM   inserted)
    INSERT INTO T_TEST_TST (GFT_ID, ACT_ID, DOS_ID, ACT_DATE, ACT_VALUE)
    SELECT T0.GFT_ID,
           COALESCE(MAX(ACT.ACT_ID), 0)+ROW_NUM,
           T0.DOS_ID,
           T0.ACT_DATE,
           T0.ACT_VALUE
    FROM   T_TEST_TST AS ACT
           RIGHT OUTER JOIN T0 
                 ON    ACT.GFT_ID = T0.GFT_ID
    GROUP BY T0.GFT_ID,
             T0.DOS_ID,
             T0.ACT_DATE,
             T0.ACT_VALUE,
             T0.ROW_NUM;
 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
END TRY
BEGIN CATCH
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    RAISERROR('Erreur dans trigger', 16, 1)
END CATCH
 
END