Comportement des évènements de table de Microsoft Access 2010 dans une transaction

Alors que les éloges vont bon train concernant Access 2010 et ses nombreuses nouvelles fonctionnalités, il est temps de pousser un peu plus en détail les recherches et de soumettre celles-ci aux cas les plus extrêmes.

Pour cet article, j'ai choisi de m'intéresser plus particulièrement au comportement des évènements de table (Triggers) dans un mode transactionnel.

Pour rappel, voici comment Frédéric Brouard, auteur d'ouvrages autour de SQL définit une transaction :

Mécanisme permettant de cumuler différentes instructions qui seront vues comme une tâche unique, dont l'enchaînement est structuré, et pour laquelle chaque instruction est indissociable.

A quoi servent les transactions ?
Elles servent à opérer une liste d'instructions entre deux changements d'état de la base de données :

1. La base est en lecture
2. La base devient éditée, les instructions sont lancées une à une
3. La base revient en mode de lecture

Si une comparaison de la base est faite entre le début et la fin de la transaction, il est impossible, depuis l'extérieur, d'affirmer si les modifications ont été réalisées par une ou plusieurs instructions.

Quel intérêt ?
Le grand intérêt des transactions réside dans le fait que les ordres passés entre les deux changements d'état peuvent être soit validés, soit annulés : en d'autres termes, il est possible de revenir en arrière.

L'exemple le plus fréquent pour symboliser une transaction est l'échange bancaire entre deux comptes.

1. Le compte A est débité de 100 Euros
2. Le compte B est crédité de 100 Euros

Il est évident qu'en cas de dysfonctionnement de la deuxième instruction, il est nécessaire de procéder à une annulation globale (Rollback) faute de quoi les 100 Euros du compte A auront disparus. En revanche, si toutes les étapes se déroulent avec succès, la transaction peut être validée, on parle alors de Commit.

Qu'en est-il des modifications apportées aux données par les évènements de table sous Access 2010, sont-elles inclues dans une transaction ou sont-elles exécutées à part ? C'est à cette question précise que nous allons tenter de répondre au travers de ce document.

Pour aborder cette notion, nous allons nous baser sur l'exemple d'une gestion de stock.

Le modèle relationnel est certes minimaliste mais convient parfaitement à la mise en place de quelques triggers : la table tbl_Produit regroupe les produits du stock et la table tbl_Mouvement recense les différentes entrées et sorties. Le champ Type_Mouvement est un numérique à deux combinaisons : la valeur 1 signifie une entrée, la valeur -1 signifie une sortie. Cette donnée multipliée par Qte_mouvement sert d'expression au champ calculé Calcul_Mouvement qui facilitera les opérations mathématiques à effectuer sur la table tbl_Produit par simple addition dans les évènements de table : nouvelle quantité = quantité actuelle + Calcul_Mouvement

Par exemple, après insertion (AfterInsert) :

SetLocalVar
  Name  :  var_Calcul_Mouvement
  Expression : [Calcul_Mouvement]

SetLocalVar
  Name  :  var_Produit_Mouvement
  Expression : [Produit_Mouvement]

Look Up A Record In tbl_Produit
  Where Condition  :  [ID_Produit]=var_Produit_Mouvement
  Alias  :  LeProduit
  
  EditRecord
    Alias : LeProduit
    SetField
      Name : Qte_Produit
      Value : [Qte_Produit]+[var_Calcul_Mouvement]
  End EditRecord

Un rapide test après constitution d'un jeu d'essais permet d'établir le bon fonctionnement du Trigger :

Contenu de la table tbl_Produit avant insertion d'un mouvement :

Insertion d'un mouvement dans la table tbl_mouvement :

Contenu de la table tbl_Produit après insertion du mouvement :

La procédure suivante permet de mettre en évidence le comportement du moteur de la base de données lors de l'annulation d'une transaction :

Sub insertion_transaction()
On Error GoTo err:
Dim oWs As DAO.Workspace
Dim oDb As DAO.Database
Dim oRst As DAO.Recordset

Set oWs = DBEngine.Workspaces(0)
Set oDb = oWs.Databases(0)

'Démarre la transaction
oWs.BeginTrans
'Lance une insertion via recordset
Set oRst = oDb.OpenRecordset("tbl_Mouvement")
With oRst
    .AddNew
    .Fields("Type_Mouvement") = 1
    .Fields("Date_Mouvement") = Now
    .Fields("Produit_Mouvement") = 1
    .Fields("Qte_Mouvement") = 4
    .Update
End With
oWs.Rollback
Exit Sub

'Annule la transaction
err:
    oWs.Rollback
    MsgBox err.Number


End Sub

Après exécution depuis l'éditeur VBA, un rapide retour à la table tbl_Mouvement permet de s'assurer que, comme souhaité, son contenu n'a pas été mis à jour.

Bien entendu, une question subsiste : le contenu de la table tbl_Mouvement a-t-il été affecté par l'évènement de table pendant la transaction puis annulé (comportement logique et attendu dans une transaction) ou bien les évènements de table ne se lancent-il qu'une fois la transaction validée par CommitTrans ?

Le code suivant apporte la réponse :

Sub insertion_transaction()
On Error GoTo err:
Dim oWs As DAO.Workspace
Dim oDb As DAO.Database
Dim oRst As DAO.Recordset
Dim oRstMouvement As DAO.Recordset
Set oWs = DBEngine.Workspaces(0)
Set oDb = oWs.Databases(0)

'Démarre la transaction
oWs.BeginTrans
'Lance une insertion via recordset
Set oRst = oDb.OpenRecordset("tbl_Mouvement")
With oRst
    .AddNew
    .Fields("Type_Mouvement") = 1
    .Fields("Date_Mouvement") = Now
    .Fields("Produit_Mouvement") = 1
    .Fields("Qte_Mouvement") = 4
    .Update
End With

'Récupère le stock du produit 1 dans la transaction
Set oRstMouvement = oDb.OpenRecordset("SELECT Qte_Produit FROM tbl_Produit WHERE ID_Produit=1")
Debug.Print "Etat du stock pendant la transaction : " & oRstMouvement.Fields(0).Value
oRstMouvement.Close
'Annule la transaction
oWs.Rollback

Exit Sub

'Annule la transaction
err:
    oWs.Rollback
    MsgBox err.Number


End Sub

Contenu de la fenêtre d'exécution :

Etat du stock pendant la transaction : 15

Le résultat est rassurant, le comportement du moteur de base de données est celui escompté.

Dans l'exemple ci-dessus, nous avons volontairement annulé la transaction à l'aide d'un appel systématique à RollBack. Habituellement, c'est l'inverse qui se produit : le Rollback n'intervient qu'en cas d'erreur. Cette erreur peut être technique (erreur d'exécution, erreur de verrouillage, .) ou conditionnelle (une règle de gestion n'est pas respectée). Dans le cas de notre gestion des stocks, une cause possible d'annulation serait la saisie d'un mouvement bien trop conséquent sachant que la zone de stockage ne peut abriter plus de 1000 produits, ou bien encore une erreur imprévue de la base de données.

Dans les évènements de table et les DataMacros, lorsqu'une erreur survient, elle n'est pas remontée à l'instruction source mais fait simplement l'objet d'un rapport dans la table USysApplicationLog. Il en va de même pour les méthodes RaiseErroretOnError dans les évènemens AfterInsert, AfterDeleteetAfterUpdate. Il convient donc de valider les règles de gestion en amont à l'aide du déclencheur Validate Change en ce qui concerne les insertions et les mises à jour (la méthode IsInsert permet de différencier les deux) et à l'aide du déclencheur Validate Delete pour les suppressions.

Exemple de DataMacro Validate Change :

OnError
  Go To Fail
If [IsInsert] And [Qte_Mouvement]>1000 Then
  RaiseError
    ErrorNumber 1001
    ErrorDescription Le nombre de produits est trop important
End if

Seul petit bémol, l'objet ErrObject piégé par l'instruction On Errror dans le cas d'un traitement VBA ne mentionne pas le numéro d'erreur de la macro mais un numéro universel : 3949.

Sub insertion_transaction()
On Error GoTo err:

    Dim oWs As DAO.Workspace
    Dim oDb As DAO.Database
    Dim oRst As DAO.Recordset
    Dim oRstMouvement As DAO.Recordset
    
    Set oWs = DBEngine.Workspaces(0)
    Set oDb = oWs.Databases(0)
    
    
    
    'Démarre la transaction
    oWs.BeginTrans
    
    
    'Lance une insertion via recordset
    Set oRst = oDb.OpenRecordset("tbl_Mouvement")
    With oRst
        .AddNew
        .Fields("Type_Mouvement") = 1
        .Fields("Date_Mouvement") = Now
        .Fields("Produit_Mouvement") = 1
        .Fields("Qte_Mouvement") = 4000
        .Update
    End With
    
    
    'Valide la transaction
    oWs.CommitTrans
    
    
fin:
    Exit Sub

'Annule la transaction
err:
    oWs.Rollback
    MsgBox err.Description
    Resume fin

End Sub

Que les professionnels du développement sous Access soient rassurés : contrairement à ce que l'on peut lire actuellement sur MSDN, les évènements de table sont tout à fait compatibles avec une application à base de transactions. Les modifications qu'ils apportent au contenu de la base de données ont bien lieu au sein de la même transaction que leur instruction d'origine. Les évènements de type Validate permettent de vérifier en amont que le jeu de données est prêt à être inséré avec succès dans la base et une éventuelle erreur peut être remontée au programme appelant afin d'invalider la transaction. Seule la gestion des erreurs inattendues au sein des évènements After* fait un peu défaut. On aurait aimé que l'instruction On Error Goto Fail ne se contente pas d'un simple rapport dans la table USysApplicationLog mais avertisse le bloc d'instructions d'origine, en sachant cependant qu'une erreur inattendue serait le fruit d'une défaillance de la base et qu'il y a fort à parier que bien qu'avertis de l'erreur nous serions incapables de procéder à un quelconque Rollback.

Bref : les évènements de table constituent selon moi LA nouvelle fonctionnalité à ne pas manquer, un pas obligé pour un développement encore plus professionnel que jamais.