SQL Server Training (Jour 3) - Intégrité et Trigger

Voila, je vais vite profiter d'une petite pause pour publier le résumé d'avant hier.
Je commence vraiment à crouler sous l'information :-)

Intégrité des données
Aujourd'hui cours sur la gestion de l'intégrité dans les DB.
Il y a trois façons de gérer l'intégrité:

1. Domain Integrity: Contrainte sur les colonnes, restreindre les valeurs dans le colonnes.
2. Entity Integrity: intégrité des enregistrements. Chaque record doit être identifié de façon unique (utilisation de Primary Key)
3. Referential Integrity: L'information contenue dans une colonne doit correspondre au contenu d'une clé primaire d'une autre table.
   

Options pour renforcer l'intégrité des données:

• Data types.
• Rules:  utilisé pour définir les valeurs acceptables dans une colonne. (OBSOLETE, not Ansi compliant).
• Default Values: permet de définir la valeur par défaut de certains champs. (OBSOLETE, not Ansi compliant).
• Xml Schema: Permet de renforcer la validation des données dans les champs XML.
• Triggers: Permet l'execution de code au moment de la sauvegarde/mise-à-jour/effacement d'enregistrement. Les triggers permettent un controle poussé de l'intégrité et du flux de donnée.
• Constraints: Ansi compliant. Definit comment la moteur DB doit renforcer l'intégrité des données.
  Il existe plusieurs type de contraintes:
  
  
  1. Primary Key
  2. Default
  3. Check
  4. Unique - La valeur contenue dans la colonne (ou combinaison de colonnes) doit être unique pour toute la table.
  5. Foreign Key  
     

Limite des contraintes:

1. Ne peut pas extraire des données depuis une autre table.
2. Ne peut pas faire appel à une stored procedure.
   

Check Constraint
Cette contraite permet de faire des vérifications sur les données (sur base d'expression) lorsqu'elles sont modifiées.
Cette contrainte est l'une des plus mal estimée alors qu'elle est la plus simple et fournit l'une des plus grande valeur ajoutée.
Plusieurs Check Constraint peuvent exister sur un même champ.
Un Check Constraint peut faire référence à un autre champs de la table (mais pas à une autre table).
Les "Check Constraint" ne peuvent pas contenir de sous query (voir Trigger).
Avantages:
Permet la modification d'information depuis une application tiers (Access) ou l'administrateur (SQL Statement) sans mettre l'intégrité des données en danger.
Note:
Il est possible de désactiver des "Check Constraint" pour accélérer le traitement de longues opérations (update, reload).

Foreign Key Constraint
Cette contrainte permet de s'assurer que la valeur d'une colonne correspond bien au contenu de la clé primaire (unique) d'une autre table.
C'est une contrainte d'intégrité référentielle.
Pour un champs soumis à une foreign key, il est impossible d'y introduire une valeur illégale. Bien que protégeant bien l'intégrité des données, les Foreign Keys présentent quelques désavantages les rendant peu populaire.
C'est ainsi que beaucoup de sociétés préfère ne pas implémenter ce genre de contraintes en production (leur préférant les triggers).

Actions:

Lors de la définition des "Foreign Key" constrainte, il est possible de préciser une clause CASCADE (update or delete). Cette dernière clause permet d'indiquer quel operation doit être exécuté sur le record lorsque la référence est modifiée ou effacée.
Par default, l'action est NO ACTION dans les deux cas.
Il est possible d'utiliser CASCADE qui est vraiment dangereux lors de l'effacement de la référence car il efface également les dependences.
Si orderDetails.ProductID reference la table product avec DELETE Cascade, alors l'effacement d'un produit efface également toutes rows orderDetails (where orderDetails.ProductID=product.productID). OUPS!
Il existe également des options SET NULL ou SET DEFAULT (voir 5-21). 

Désavantages:

• Limite les operations de modification de schéma (renommer des colonnes).
• Consomme des ressources (il faut faire un choix entre performance et sécurité).
• Peut se montrer lents lors que la clé primaire de référence appartient à une table très grande.
• Nécessaire de désactiver ce type de contrainte lors du rebuild des indexes.
• Représente des contraintes d'utilisation vraiment excessives pour les DB de reporting. Il est en effet courant d'effacer et de recharger de nouvelles données en masse dans ce genre de DB. Dans ce cas, les foreign key représentent des contraintes non nécessaire puisqu'il n'est pas possible de tronquer la table... et la mise à jour d'information en masse est fortement ralentie par la vérification de la contrainte.
  

Default Constraint
Vraiment utile pour s'assurer que certains champs soient tooujours correctement initialisés.
Sont utilisation est recommandée. 

Triggers
Les triggers permettent d'activer du code (actions) lors d'un événement d'insertion/update/delete de records sur une table.
Le trigger est appelé une seule fois par opération (quelque soit le scope de l'opération). Qu'une requête sql modifie ou efface 1 ou 2000 enregistrements (en fonction de sa where clause), le trigger ne sera activé qu'une seule fois pour la modification entière.
Les triggers sont toujours executés dans la même transaction que l'événement.
Il est par conséquent possible de faire un rollback pour annuler l'événement d'origine.
Mais en contre partie, il faut veiller à avoir un trigger aussi court que possible afin de limiter le temps de transaction.

Il y a deux type de triggers:

1. After Trigger: Trigger qui sont exécutés après l'événement sur la table. Ce qui permet par exemple de vérifier des contraintes d'intégrité ou de maintenir des tables d'audit. 
2. Instead Of Trigger: Trigger qui remplace l'événement. Ce qui permet de modifier le comportement standard de SQL Server. Eg: remplacer un effacement physique par un effacement logique ou encore répartir l'information au sauvegarder entre plusieurs tables (utile lorsque le trigger est placé sur une vue).
   

Recommandation d'usage:

1. Récuperer les records insérés ou mis-à-jour depuis la table virtuelle "udpated".
2. Récuperer les records effacés depuis la table virtuelle "deleted".
3. Le trigger doit être écrit de façon fonctionner avec une table deleted et updated contenant plusieurs enregistrement (erreur courante).
4. En cas d'effacement, toujours faire un count(*) from deteled pour s'assurer qu'il y a des informations à effacer.
5. Par default, un trigger n'est pas récursif sur sa propre table.
6. Un trigger peut déclencher un trigger sur un autre table (cascading firing)
   

Informations diverses:

• Pour tester si un champ a été modifié, utiliser la fonction Update(FieldName)
• Pour stopper une trigger (rollback), utilser RaiseError( N'Message', 10, 1 )
• Lorsqu'un trigger insert des records dans une autre table, utiliser le mot clé OUTPUT pour récupérer les valeurs des champs identity dans une variable de type table (voir autre article à venir)
  

Cas d'utilisations:

• Effectuer un effacement logique (active=0) lors d'une instruction DELETE FROM.
• Eviter les effacements physique en les interdisants. Meme si la sécurité est mal configuré, il ne sera pas possible d'effacer les records.
• Propagation d'état. Par exemple, la désactivation d'une catégorie de produit peut egalement désactiver tous les produits et ajouter un commentaire aux commandes en cours sur ces produits.
• Permettre d'effectuer des vérifications complexes et de retourner des messages d'erreurs préçis.
• Remplacement de Foreign Key constraint... car les trigger peuvent être désactivés et réactivé pour accélérer des opérations de maintenance. La contrainte d'intégrité implémenté dans le trigger ne sera revérifiée que lors d'une nouvelle modification de l'enregistrement.