283
8.3 Optimisation du code SQL
Il est donc plus prudent d’effectuer le ROLLBACK dans le code appelant, ou d’utiliser le mécanisme des points de sauvegarde, à l’aide d’un SAVE TRANSACTION nom_du_point;, et ensuite
d’un ROLLBACK TRANSACTION nom_du_point;, qui effectivement n’annule que cette partie de la
transaction.
Un trigger est déclenché quel que soit le nombre de lignes affectées. Une instruction DML avec une clause de filtre qui ne retourne aucun match fera donc exécuter
le déclencheur. Comme ceci par exemple :
DELETE FROM sales.currency WHERE CurrencyCode = 'FRF';
-- ou plus simplement :
DELETE FROM sales.currency WHERE 1 = 0;
Il est bon de tester cela au début du trigger, pour éviter l’exécution du code en
pure perte. La variable @@ROWCOUNT est disponible à l’intérieur du trigger pour cela :
ALTER TRIGGER atr_d$sales_currency2$archive
ON sales.currency2
AFTER DELETE
AS BEGIN
IF @@ROWCOUNT = 0 RETURN;
...
Pour éviter d’exécuter le code de votre déclencheur si les colonnes qui vous intéressent ne sont même pas mises à jour, servez-vous des fonctions UPDATE() et
COLUMNS_UPDATED(), qui vous indiquent quelles colonnes ont été mentionnées dans
l’instruction qui déclenche le trigger. Référez-vous au BOL pour la syntaxe de ces
fonctions, mais notez qu’elles n’indiquent pas si une colonne est réellement mise à
jour (si sa valeur a changé), mais si elle est mentionnée dans l’instruction DML.
Un SET NOCOUNT ON est aussi une bonne idée.
Un déclencheur, comme tout bon événement, interagit le moins possible avec la
session, et principalement ne renvoie pas de jeu de résultat. C’est inutile et dangereux pour l’application cliente, qui peut être parasitée par un recordset indésirable.
Vous pouvez désactiver le renvoi de SELECT malencontreusement présents à l’intérieur d’un trigger à l’aide de l’option de serveur 'disallow results from triggers' :
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'disallow results from triggers', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
Si le but est d’afficher ou de rediriger le résultat d’une opération, songez à utiliser
la clause OUTPUT, plus légère, plutôt qu’un déclencheur : disponible dans les instructions INSERT, UPDATE et DELETE, cette clause rend visible les pseudo-tables DELETED et
INSERTED. Exemple pour l’archivage :
DELETE FROM sales.currency2
OUTPUT deleted.CurrencyCode, deleted.Name, CURRENT_TIMESTAMP
INTO sales.currencyArchive;
8.3 Optimisation du code SQL
Il est donc plus prudent d’effectuer le ROLLBACK dans le code appelant, ou d’utiliser le mécanisme des points de sauvegarde, à l’aide d’un SAVE TRANSACTION nom_du_point;, et ensuite
d’un ROLLBACK TRANSACTION nom_du_point;, qui effectivement n’annule que cette partie de la
transaction.
Un trigger est déclenché quel que soit le nombre de lignes affectées. Une instruction DML avec une clause de filtre qui ne retourne aucun match fera donc exécuter
le déclencheur. Comme ceci par exemple :
DELETE FROM sales.currency WHERE CurrencyCode = 'FRF';
-- ou plus simplement :
DELETE FROM sales.currency WHERE 1 = 0;
Il est bon de tester cela au début du trigger, pour éviter l’exécution du code en
pure perte. La variable @@ROWCOUNT est disponible à l’intérieur du trigger pour cela :
ALTER TRIGGER atr_d$sales_currency2$archive
ON sales.currency2
AFTER DELETE
AS BEGIN
IF @@ROWCOUNT = 0 RETURN;
...
Pour éviter d’exécuter le code de votre déclencheur si les colonnes qui vous intéressent ne sont même pas mises à jour, servez-vous des fonctions UPDATE() et
COLUMNS_UPDATED(), qui vous indiquent quelles colonnes ont été mentionnées dans
l’instruction qui déclenche le trigger. Référez-vous au BOL pour la syntaxe de ces
fonctions, mais notez qu’elles n’indiquent pas si une colonne est réellement mise à
jour (si sa valeur a changé), mais si elle est mentionnée dans l’instruction DML.
Un SET NOCOUNT ON est aussi une bonne idée.
Un déclencheur, comme tout bon événement, interagit le moins possible avec la
session, et principalement ne renvoie pas de jeu de résultat. C’est inutile et dangereux pour l’application cliente, qui peut être parasitée par un recordset indésirable.
Vous pouvez désactiver le renvoi de SELECT malencontreusement présents à l’intérieur d’un trigger à l’aide de l’option de serveur 'disallow results from triggers' :
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'disallow results from triggers', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
Si le but est d’afficher ou de rediriger le résultat d’une opération, songez à utiliser
la clause OUTPUT, plus légère, plutôt qu’un déclencheur : disponible dans les instructions INSERT, UPDATE et DELETE, cette clause rend visible les pseudo-tables DELETED et
INSERTED. Exemple pour l’archivage :
DELETE FROM sales.currency2
OUTPUT deleted.CurrencyCode, deleted.Name, CURRENT_TIMESTAMP
INTO sales.currencyArchive;