282
Chapitre 8. Optimisation du code SQL
SELECT @CurrencyCode = CurrencyCode,
@Name = Name,
@DeletedDate = CURRENT_TIMESTAMP
FROM Deleted
INSERT INTO sales.currencyArchive
(CurrencyCode, Name, DeletedDate)
VALUES
(@CurrencyCode, @Name, @DeletedDate)
END
Bien entendu, ceci ne fonctionne correctement que si une seule ligne est supprimée. Si toute la table sales.currency est vidée, une seule ligne sera inscrite dans la
table sales.currencyArchive. C’est une erreur fréquente, méfiez-vous en.
Dans le contexte du déclencheur, deux pseudo-tables sont disponibles, pour
retrouver les lignes affectées par l’instruction : DELETED contient les lignes supprimées par un DELETE ou un UPDATE, et INSERTED les lignes ajoutées par un INSERT ou un
UPDATE. L’UPDATE est considéré logiquement comme un DELETE suivi d’un INSERT. Ces
pseudo-tables sont gérées en interne par des versions de ligne stockées dans le version
store de tempdb (voir section 4.4). De ce fait, le déclenchement d’un trigger provoquera une copie de version et de l’activité dans tempdb. De même, ces pseudo-tables
n’ont pas d’index, et les recherches se feront toujours par scan (vous verrez dans le
plan d’exécution des opérateurs particuliers, avec leur coût relatif : « Inserted scan »
et « Deleted scan »). Évitez donc les déclencheurs sur des tables massivement modifiées.
Le plan d’exécution du déclencheur est visible via l’affichage des plans dans
SSMS, que ce soir le plan estimé (donc aussi via SET SHOWPLAN_XML) ou le plan réel.
Par contre, SET STATISTICS IO ON n’affiche pas les statistiques de lectures et d’écriture des pseudo-tables.
Depuis SQL Server 2005, le ROLLBACK à l’intérieur d’un trigger, pour annuler la
transaction de l’instruction déclenchante, envoie une erreur 3609 dans la session,
afin d’avertir l’utilisateur. Vous pouvez placer le ROLLBACK dans un bloc TRY CATCH
pour éviter cette erreur.
Bonne pratique
En réalité, l’erreur 3609 en envoyée si @@TRANCOUNT est égal à 0 à la sortie du déclencheur. Elle
apparaît donc aussi si un COMMIT TRANSACTION sans BEGIN TRANSACTION est exécuté dans le trigger, ce qui est sans intérêt. Déclencher l’erreur 3609 a aussi un sens en cas de ROLLBACK. Il est
relativement dangereux d’exécuter un ROLLBACK dans un trigger, car cette instruction annule
toute la chaîne des transactions, jusqu’à la transaction la plus externe. Ce n’est peut-être pas
ce que vous souhaitez, dans la mesure où vous n’avez jamais de garantie que le code déclenchant le trigger ne gère pas de transaction explicite. De même, un ROLLBACK fermera les éventuels curseurs créés dans le code appelant, sauf s’ils sont STATIC ou INSENSITIVE et si
CURSOR_CLOSE_ON_COMMIT est à OFF. Enfin, si vous faites un ROLLBACK, n’oubliez pas de le faire
suivre par un RETURN, pour éviter d’exécuter ensuite du code qui serait en dehors de la transaction, et donc validé.
Chapitre 8. Optimisation du code SQL
SELECT @CurrencyCode = CurrencyCode,
@Name = Name,
@DeletedDate = CURRENT_TIMESTAMP
FROM Deleted
INSERT INTO sales.currencyArchive
(CurrencyCode, Name, DeletedDate)
VALUES
(@CurrencyCode, @Name, @DeletedDate)
END
Bien entendu, ceci ne fonctionne correctement que si une seule ligne est supprimée. Si toute la table sales.currency est vidée, une seule ligne sera inscrite dans la
table sales.currencyArchive. C’est une erreur fréquente, méfiez-vous en.
Dans le contexte du déclencheur, deux pseudo-tables sont disponibles, pour
retrouver les lignes affectées par l’instruction : DELETED contient les lignes supprimées par un DELETE ou un UPDATE, et INSERTED les lignes ajoutées par un INSERT ou un
UPDATE. L’UPDATE est considéré logiquement comme un DELETE suivi d’un INSERT. Ces
pseudo-tables sont gérées en interne par des versions de ligne stockées dans le version
store de tempdb (voir section 4.4). De ce fait, le déclenchement d’un trigger provoquera une copie de version et de l’activité dans tempdb. De même, ces pseudo-tables
n’ont pas d’index, et les recherches se feront toujours par scan (vous verrez dans le
plan d’exécution des opérateurs particuliers, avec leur coût relatif : « Inserted scan »
et « Deleted scan »). Évitez donc les déclencheurs sur des tables massivement modifiées.
Le plan d’exécution du déclencheur est visible via l’affichage des plans dans
SSMS, que ce soir le plan estimé (donc aussi via SET SHOWPLAN_XML) ou le plan réel.
Par contre, SET STATISTICS IO ON n’affiche pas les statistiques de lectures et d’écriture des pseudo-tables.
Depuis SQL Server 2005, le ROLLBACK à l’intérieur d’un trigger, pour annuler la
transaction de l’instruction déclenchante, envoie une erreur 3609 dans la session,
afin d’avertir l’utilisateur. Vous pouvez placer le ROLLBACK dans un bloc TRY CATCH
pour éviter cette erreur.
Bonne pratique
En réalité, l’erreur 3609 en envoyée si @@TRANCOUNT est égal à 0 à la sortie du déclencheur. Elle
apparaît donc aussi si un COMMIT TRANSACTION sans BEGIN TRANSACTION est exécuté dans le trigger, ce qui est sans intérêt. Déclencher l’erreur 3609 a aussi un sens en cas de ROLLBACK. Il est
relativement dangereux d’exécuter un ROLLBACK dans un trigger, car cette instruction annule
toute la chaîne des transactions, jusqu’à la transaction la plus externe. Ce n’est peut-être pas
ce que vous souhaitez, dans la mesure où vous n’avez jamais de garantie que le code déclenchant le trigger ne gère pas de transaction explicite. De même, un ROLLBACK fermera les éventuels curseurs créés dans le code appelant, sauf s’ils sont STATIC ou INSENSITIVE et si
CURSOR_CLOSE_ON_COMMIT est à OFF. Enfin, si vous faites un ROLLBACK, n’oubliez pas de le faire
suivre par un RETURN, pour éviter d’exécuter ensuite du code qui serait en dehors de la transaction, et donc validé.