Tableaux d'historique pros, contre et gotchas – utilisant des sortingggers, sproc ou au niveau de l'application

Je suis actuellement en train de jouer avec l'idée d'avoir des tables d'historique pour certaines de mes tables dans ma database. Fondamentalement, j'ai la table principale et une copy de cette table avec une date modifiée et une colonne d'action pour stocker ce que l'action a été préformé, par exemple Update, Delete et Insert.

Jusqu'à présent, je peux penser à trois endroits différents que vous pouvez faire le travail de la table d'histoire.

Déclencheurs sur la table principale pour la mise à jour, insert et supprimer. (Base de données)
stored procedures. (Base de données)
Couche d'application. (Application)

Ma principale question est, quels sont les avantages, les inconvénients et les pièges de faire le travail dans chacune de ces couches.

Un avantage auquel je peux penser en utilisant la méthode des triggersurs est que l'intégrité est toujours maintenue quel que soit le programme implanté en haut de la database.

Je dirais de cette façon:

Processus stockés: ils sont ignorés si vous modifiez la table directement. La security sur la database peut contrôler cela
Application: même affaire. De même, si vous avez plusieurs applications, éventuellement dans des langues différentes, il est nécessaire de les implémenter dans chaque stack, ce qui est quelque peu redondant; et
Triggers: transparent à l'application et capturera tous les changements. C'est ma méthode préférée.

Les triggersurs sont le moyen le plus rapide et le plus facile de réaliser une histoire simple. Les informations suivantes supposent un exemple plus complexe où le traitement de l'historique peut inclure certaines règles métier et peut nécessiter des informations de journalisation non trouvées dans la table faisant l'object d'un suivi.

Pour ceux qui pensent que les triggersurs sont plus sûrs que les sprocs parce qu'ils ne peuvent pas être contournés, je leur callbackle qu'ils font l'hypothèse suivante:

Il existe des permissions qui empêchent les users d'exécuter DISABLE TRIGGER [mais des permissions peuvent également exister pour limiter l'access à la database sauf pour EXECUTE sur sprocs, ce qui est un model courant pour les applications d'entreprise]. triggersurs en termes de security et de capacité à être contourné

!) Selon la database, il peut être possible d'exécuter des instructions de mise à jour qui ne triggersnt pas de triggersurs. Je pourrais tirer parti de la connaissance de la profondeur d'exécution du sortinggger nested pour contourner un sortinggger. La seule solution sûre inclut la security dans la database et la limitation de l'access aux données en utilisant uniquement des mécanismes approuvés – qu'il s'agisse de triggersurs, de sprocs ou de couches d'access aux données.

Je pense que les choix sont clairs ici. Si datatables sont accédées par plusieurs applications, vous voulez contrôler l'historique à partir de la couche commune la plus basse et cela signifiera la database.

Suivant la logique ci-dessus, le choix des triggersurs ou des procédures stockées dépend à nouveau si la procédure stockée est la couche commune la plus basse. Vous devriez préférer le sproc sur le triggersur car vous pouvez contrôler les performances, et les effets secondaires mieux et le code est plus facile à maintenir.

Les triggersurs sont acceptables, mais essayez de vous assurer que vous n'augmentez pas les verrous en lisant datatables en dehors des tables en cours de mise à jour. Limitez les triggersurs aux insertions dans les tables de journalisation, n'enregistrez que ce dont vous avez besoin.

Si l'application utilise une couche d'access logique commune et qu'il est peu probable que cela change avec le time, je préférerais implémenter la logique ici. Utilisez un model Chaîne de responsabilité et une architecture de plug-in pour générer toutes sortes de traitements dans votre module d'historique, y compris la connection à des technologies complètement différentes, des bases de données différentes, un service d'historique ou toute autre chose que vous pourrait imaginer.

Nous utilisons l'approche basée sur les triggersurs depuis des années et cela a vraiment bien fonctionné pour nous, mais alors vous avez les points suivants à méditer:

Les triggersurs sur une application SaaS (par exemple, une application à plusieurs locataires) peuvent être extrêmement coûteux
Dans certains scénarios, quelques champs peuvent être redondants. Les triggersurs ne sont bons que lorsque vous êtes parfaitement clair sur les champs à save; si vous utilisez une application, vous pouvez avoir une couche d'intercepteur qui pourrait vous aider à save certains champs en fonction de la "configuration"; mais avec sa propre part de frais généraux
Sans contrôle adéquat de la database, une personne pourrait facilement désactiver les triggersurs, modifier datatables et activer les triggersurs; le tout sans triggersr d'alarmes
Dans le cas d'applications Web, où les connections sont établies à partir d'un pool, le suivi des users réels qui ont effectué les modifications peut être fastidieux. Une solution possible serait d'avoir le champ "EditedBy" dans chaque table de transaction.

En retard, mais il ajoute quelques options supplémentaires qui peuvent être considérées.

Modifier la capture de données: cette fonctionnalité est disponible dans SQL Server 2008 R2 + mais uniquement dans l'édition d'entreprise. Il vous permet de sélectionner les tables que vous voulez suivre et SQL Server fera le travail pour vous. Il fonctionne en lisant le journal des transactions et en remplissant les tables d'historique avec des données.

Lecture du journal des transactions: Si la database est en mode de récupération complète, le journal des transactions peut être lu et des détails sur presque toutes les transactions peuvent être trouvés.

L'inconvénient est que ce n'est pas pris en charge par défaut. Les options sont de lire le journal des transactions en utilisant des fonctions non documentées comme fn_dblog ou des outils tiers tels que ApexSQL Log .

Triggers: Fonctionne très bien pour un petit nombre de tables où il n'y a pas trop de sortingggers à gérer. Si vous avez beaucoup de tables que vous souhaitez auditer, vous devriez envisager un outil tiers pour cela.

Tout cela fonctionne au niveau de la database et est complètement transparent pour l'application.

Les triggersurs sont le seul moyen fiable de capturer les changements. Si vous le faites dans Stored Procs ou l'application, vous pouvez toujours entrer et supprimer une modification pour laquelle vous n'avez pas de journal (par inadvertance). Bien sûr, quelqu'un qui ne veut pas laisser un journal peut désactiver les triggersurs. Mais vous préférez forcer quelqu'un à désactiver la journalisation plutôt que d'espérer qu'ils se souviennent de l'inclure.

Habituellement, si vous choisissez la couche d'application, vous pouvez concevoir votre code d'application pour faire la journalisation en un seul point, qui traitera de manière cohérente toute votre table historique. Les triggersurs différemment sont une approche plus compliquée à maintenir parce qu'ils sont (selon la technologie de DB) reproduits pour chaque table: dans le cas de la centaine de tables la quantité de code pour le triggersur peut être un problème.

si vous avez une organisation de support qui maintiendra le code que vous écrivez maintenant, et vous ne savez pas qui maintiendra votre code (typique pour les grandes indussortinges) vous ne pouvez pas supposer quel est le niveau de compétence de la personne qui fera réparer votre code application, dans ce cas, il est préférable à mon avis de rendre le principe de fonctionnement de la table historique aussi simple que possible, et la couche d'application est probablement le meilleur endroit à cet effet.