Ecriture ensembliste de triggers

Lors de mes participations au forum SQL Server de ce site, j’ai plusieurs fois vu des participants montrer leur trigger, qui spécifie du code non ensembliste, c’est-à-dire :

– Un traitement ligne à ligne, avec une boucle WHILE, ou un curseur,
– Une affectation de variables par sélection des tables virtuelles INSERTED et DELETED.

Dans le premier cas, il faut savoir que les SGBDR modernes sont conçus pour traiter des ensembles de données, et non pas pour traiter des lignes une par une, à la façon d’un curseur. Rappelons que les curseurs datent de COBOL (si vous vous rappelez des fichiers séquentiels indexés et de la rigidité de ce langage, cela doit vous dégoûter d’en écrire).
Par conséquent, tout traitement qui n’est pas ensembliste sera forcément plus long que celui qui l’est.

Dans le second cas, c’est en fait la première ligne mise à jour qui subira le traitement du trigger, parce qu’une variable ne peut être affectée au plus que par une seule valeur.

	/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

	1. Cas des instructions n'affectant qu'une seule ligne

	\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Cela fonctionne parfaitement pour une instruction :
– INSERT spécifiée avec la clause VALUES,
– UPDATE spécifiée sans clause FROM.

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
SET NOCOUNT ON
GO

IF EXISTS
(
SELECT *
FROM sys.tables
WHERE name = 'TEST'
)
DROP TABLE dbo.TEST
GO

CREATE TABLE dbo.TEST
(
unEntier INT
)
GO

CREATE TRIGGER TRIGGER_TEST
ON dbo.TEST
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @unEntier INT,
@unEntierPlusUn INT

SELECT @unEntier = unEntier,
@unEntierPlusUn = unEntier + 1
FROM INSERTED

UPDATE dbo.TEST
SET unEntier = @unEntierPlusUn
WHERE unEntier = @unEntier
END
GO

INSERT INTO dbo.TEST VALUES (1)
INSERT INTO dbo.TEST VALUES (3)
INSERT INTO dbo.TEST VALUES (5)
INSERT INTO dbo.TEST VALUES (7)
INSERT INTO dbo.TEST VALUES (9)
GO

SELECT * FROM dbo.TEST
GO

UPDATE dbo.TEST
SET unEntier = unEntier + 1
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
2
4
6
8
10

unEntier
———–
4
5
7
9
11

En revanche pour l’instruction DELETE, seule la dernière valeur est affectée par le traitement du trigger :

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
ALTER TRIGGER TRIGGER_TEST
ON dbo.TEST
INSTEAD OF DELETE
AS
BEGIN
DECLARE @unEntier INT,
@unEntierMoinsUn INT

SELECT @unEntier = unEntier,
@unEntierMoinsUn = unEntier - 1
FROM DELETED

UPDATE dbo.TEST
SET unEntier = @unEntierMoinsUn
WHERE unEntier = @unEntier
END
GO

TRUNCATE TABLE dbo.TEST
GO

INSERT INTO dbo.TEST VALUES (1)
INSERT INTO dbo.TEST VALUES (3)
INSERT INTO dbo.TEST VALUES (5)
INSERT INTO dbo.TEST VALUES (7)
INSERT INTO dbo.TEST VALUES (9)
GO

DELETE FROM dbo.TEST
WHERE unEntier > 2
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
1
3
5
7
8

	/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

	2. Cas des instructions affectant plusieurs lignes

	\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Voyons ce que cela donne lorsqu’on souhaite traiter plusieurs lignes avec des triggers non ensemblistes :

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
SET NOCOUNT ON
GO

IF EXISTS
(
SELECT *
FROM sys.tables
WHERE name = 'TEST'
)
DROP TABLE dbo.TEST
GO

CREATE TABLE dbo.TEST
(
unEntier INT
)
GO

CREATE TRIGGER TRIGGER_TEST
ON dbo.TEST
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @unEntier INT,
@unEntierPlusUn INT

SELECT @unEntier = unEntier,
@unEntierPlusUn = unEntier + 1
FROM INSERTED

UPDATE dbo.TEST
SET unEntier = @unEntierPlusUn
WHERE unEntier = @unEntier
END
GO

INSERT INTO dbo.TEST
SELECT 1
UNION SELECT 3
UNION SELECT 5
UNION SELECT 7
UNION SELECT 9
GO

SELECT * FROM dbo.TEST
GO

unEntier
———–
2
3
5
7
9

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
WITH
CTE_Nombres_A_Mettre_A_Jour AS
(
SELECT 3 AS n
UNION ALL
SELECT n + 2
FROM CTE_Nombres_A_Mettre_A_Jour
WHERE n < 15
)
UPDATE dbo.TEST
SET unEntier = CTE.n
FROM dbo.TEST T
JOIN CTE_Nombres_A_Mettre_A_Jour CTE ON CTE.n = T.unEntier
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
2
4
5
7
9

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
ALTER TRIGGER TRIGGER_TEST
ON dbo.TEST
INSTEAD OF DELETE
AS
BEGIN
DECLARE @unEntier INT,
@unEntierMoinsUn INT

SELECT @unEntier = unEntier,
@unEntierMoinsUn = unEntier - 1
FROM DELETED

UPDATE dbo.TEST
SET unEntier = @unEntierMoinsUn
WHERE unEntier = @unEntier
END
GO

TRUNCATE TABLE dbo.TEST
GO

INSERT INTO dbo.TEST VALUES (1)
INSERT INTO dbo.TEST VALUES (3)
INSERT INTO dbo.TEST VALUES (5)
INSERT INTO dbo.TEST VALUES (7)
INSERT INTO dbo.TEST VALUES (9)
GO

WITH
CTE_Nombres_A_Supprimer AS
(
SELECT 7 AS n
UNION ALL
SELECT n + 2
FROM CTE_Nombres_A_Supprimer
WHERE n < 15
)
DELETE FROM dbo.TEST
FROM dbo.TEST T
JOIN CTE_Nombres_A_Supprimer CTE ON CTE.n = T.unEntier
WHERE unEntier > 2
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
1
3
5
7
8

On vient donc de voir que lors de l’exécution d’un instruction ensembliste :
– de type INSERT ou UPDATE, seule la première valeur rencontrée dans la colonne est affectée
– de type DELETE, seule la dernière valeur rencontrée dans la colonne est affectée

Or on ne peut pas prévoir le type des instructions qui vont affecter une table.
Le fait de n’affecter qu’une seule ligne d’une table revient à affecter un ensemble de lignes de cardinal égal à 1 de cette table.

	/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\

	3. Écriture de triggers à traitements ensemblistes

	\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Attachons-nous donc à créer des triggers dont le code spécifie des jointures avec les tables virtuelles INSERTED et DELETED.

Commençons avec l’instruction INSERT spécifiée avec la clause VALUES, puis avec une instruction INSERT spécifiée avec une commande SELECT :

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
SET NOCOUNT ON
GO

IF EXISTS
(
SELECT *
FROM sys.tables
WHERE name = 'TEST'
)
DROP TABLE dbo.TEST
GO

CREATE TABLE dbo.TEST
(
unEntier INT
)
GO

CREATE TRIGGER TRIGGER_TEST
ON dbo.TEST
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE dbo.TEST
SET unEntier = I.unEntier + 1
FROM dbo.TEST T
JOIN INSERTED I ON I.unEntier = T.unEntier
END
GO

INSERT INTO dbo.TEST VALUES (1)
INSERT INTO dbo.TEST VALUES (3)
INSERT INTO dbo.TEST VALUES (5)
INSERT INTO dbo.TEST VALUES (7)
INSERT INTO dbo.TEST VALUES (9)
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
2
4
6
8
10

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
TRUNCATE TABLE dbo.TEST
GO

INSERT INTO dbo.TEST
SELECT 1
UNION SELECT 3
UNION SELECT 5
UNION SELECT 7
UNION SELECT 9
GO

SELECT * FROM dbo.TEST
GO

unEntier
———–
2
4
6
8
10

Dans les deux cas, nos instructions ont bénéficié du traitement du trigger.
Continuons avec une instruction UPDATE sans clause FROM, puis avec clause FROM:

1
2
3
4
5
6
7
8
9
10

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
UPDATE dbo.TEST
SET unEntier = unEntier + 1
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
4
6
8
10
12

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
WITH
CTE_Nombres_A_Mettre_A_Jour AS
(
SELECT 6 AS n
UNION ALL
SELECT n + 2
FROM CTE_Nombres_A_Mettre_A_Jour
WHERE n < 14
)
UPDATE dbo.TEST
SET unEntier = CTE.n
FROM dbo.TEST T
JOIN CTE_Nombres_A_Mettre_A_Jour CTE ON CTE.n = T.unEntier
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
4
7
9
11
13

Les deux types d’instruction UPDATE ont subi le traitement du trigger pour toutes les lignes affectées.
Terminons avec deux instructions DELETE, l’une sans clause FROM, l’autre avec une clause FROM:

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

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
ALTER TRIGGER TRIGGER_TEST
ON dbo.TEST
INSTEAD OF DELETE
AS
BEGIN
UPDATE dbo.TEST
SET unEntier = D.unEntier - 1
FROM dbo.TEST T
JOIN DELETED D ON D.unEntier = T.unEntier
END
GO

TRUNCATE TABLE dbo.TEST
GO

INSERT INTO dbo.TEST VALUES (1)
INSERT INTO dbo.TEST VALUES (3)
INSERT INTO dbo.TEST VALUES (5)
INSERT INTO dbo.TEST VALUES (7)
INSERT INTO dbo.TEST VALUES (9)
GO

SELECT *
FROM dbo.TEST
GO

DELETE FROM dbo.TEST
WHERE unEntier > 4

SELECT *
FROM dbo.TEST
GO

unEntier
———–
1
3
4
6
8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

---------------------------------
-- Nicolas SOUQUET - 09/11/2008 -
---------------------------------
WITH
CTE_Nombres_A_Supprimer AS
(
SELECT 6 AS n
UNION ALL
SELECT n + 2
FROM CTE_Nombres_A_Supprimer
WHERE n < 14
)
DELETE FROM dbo.TEST
FROM dbo.TEST T
JOIN CTE_Nombres_A_Supprimer CTE ON CTE.n = T.unEntier
WHERE unEntier > 2
GO

SELECT *
FROM dbo.TEST
GO

unEntier
———–
1
3
4
5
7

Là encore, les deux types d’instruction ont parfaitement subi le traitement du trigger.

	/\/\/\/\/\/\/

	4. Conclusion

	\/\/\/\/\/\/\

C’est vrai pour les triggers, mais plus généralement pour toute instruction SQL exécutée dans un SGBDR : si le code est ensembliste, il s’exécute plus vite et sur tous les types d’instructions.

Dans le cas des triggers, il faut se servir de la puissance procurée par les tables virtuelles INSERTED et DELETED : elles reflètent la même structure que la table cible, mais ne contiennent que les lignes de la table qui viennent d’être affectées. Il suffit donc de spécifier une jointure entre la table cible et ces deux tables virtuelles sur la clé de la table spécifier un trigger dont le code est ensembliste.

ElSuket

Identifiez-vous

Créer un compte

Le Blog SQL Server d'ElSüket

Nicolas Souquet – Expert SQL Server

Laisser un commentaire Annuler la réponse.

Identifiant
Mot de passe

Mot de passe oublié ?