Utilisation de déclencheurs DML contenant des instructions COMMIT ou ROLLBACK TRANSACTION
Dans les versions de SQL Server ultérieures à SQL Server version 7.0, le nombre de transactions ne s’incrémente au sein d’une transaction que s’il est égal à 0 au début de l'instruction. Dans SQL Server version 7.0, le comptage des transactions est toujours incrémenté, quelle que soit sa valeur au démarrage de l'instruction. Par conséquent, dans les versions ultérieures, la valeur que @@TRANCOUNT retourne dans les déclencheurs peut être inférieure à celle observée dans SQL Server 7.0.
Dans les versions ultérieures, si une instruction COMMIT TRANSACTION ou COMMIT WORK est exécutée dans un déclencheur et qu'il n'existe pas d'instruction BEGIN TRANSACTION explicite ou implicite correspondante au démarrage du déclencheur, les utilisateurs peuvent constater un comportement différent par rapport à SQL Server version 7.0. Il est déconseillé de placer des instructions COMMIT TRANSACTION ou COMMIT WORK dans un déclencheur.
Lorsque des déclencheurs contenant des instructions COMMIT ou ROLLBACK TRANSACTION sont exécutés à partir d'un traitement, ils annulent le traitement tout entier. Dans SQL Server 2008 et SQL Server 2005, une erreur est également retournée.
Dans l'exemple suivant, si l'instruction INSERT active un déclencheur DML contenant une instruction ROLLBACK TRANSACTION, l'instruction DELETE ne s'exécutera pas car le traitement est annulé.
/* Start of Batch */
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION.
DELETE employee WHERE emp_id = 'PMA42628M'
GO
Si des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont activés à partir d'une transaction définie par l'utilisateur, l'instruction ROLLBACK TRANSACTION restaure toute la transaction. Dans l'exemple suivant, si l'instruction INSERT active un déclencheur contenant une instruction ROLLBACK TRANSACTION, l'instruction UPDATE est également restaurée.
/* Start of Transaction */
BEGIN TRANSACTION
UPDATE employee SET hire_date = '7/1/94' WHERE emp_id = 'VPA30890F'
INSERT employee VALUES ('XYZ12345M', 'New', 'M', 'Employee', 1, 1, '9952', '6/1/95') -- Causes trigger to fire and ROLLBACK TRANSACTION.