Après avoir commenté dans quelques-unes des réponses ici, je me sens obligé de montrer un exemple de comment les déclencheurs peuvent être utilisés pour faire respecter des règles qui vont au-delà de la RI de base fournie par les SGBDR.
Dans notre propre système, nous avons plusieurs tables qui ont toutes des clés étrangères pointant vers la même table ("MasterTable"). Au fil du temps, les lignes de ces tables seront supprimées ; une fois que la dernière ligne enfant a été supprimée, nous voulons supprimer la ligne parent et prendre des mesures. Nous avons imposé cette règle en créant une colonne "ChildCount" sur la table parent, qui indique le nombre de lignes qui se réfèrent à celle-ci (en d'autres termes, un décompte de références).
Dans les déclencheurs d'insertion de toutes les différentes tables enfant, nous avons du code qui ressemble à ceci :
SELECT ChildCount
INTO numrows
FROM MasterTable mt
WHERE :new.MasterTableId = MasterTable.MasterTableId
FOR UPDATE;
UPDATE MasterTable
SET ChildCount = ChildCount + 1
WHERE :new.MasterTableId = MasterTable.MasterTableId;
Dans les déclencheurs de suppression des tables enfant, nous avons ceci :
SELECT ChildCount
INTO numrows
FROM MasterTable
WHERE :old.MasterTableId = MasterTable.MasterTableId
FOR UPDATE;
DELETE MasterTable
WHERE ChildCount = 1
AND :old.MasterTableId = MasterTable.MasterTableId;
IF Sql%RowCount = 0 THEN
UPDATE MasterTable
SET ChildCount = ChildCount - 1
WHERE :old.MasterTableId = MasterTable.MasterTableId;
END IF;
Les déclencheurs de mise à jour contiennent les deux morceaux de code.
La partie clé de cette logique est l'utilisation d'une déclaration select séparée avec la clause FOR UPDATE, au lieu de simplement mettre à jour la colonne avec une seule déclaration. Cela garantit que les transactions simultanées seront sérialisées correctement.
Étant donné que MasterTable a déjà déclaré des règles de suppression en cascade vers toutes les tables enfant, le code ci-dessus provoquera des erreurs ORA-04091 (table en mutation) lorsqu'il s'exécutera dans le contexte de la suppression d'une ligne MasterTable qui a des enfants existants, donc ces déclarations sont faites dans le contexte d'un bloc d'EXCEPTION qui intercepte et ignore cette erreur.
Enfin, le code ci-dessus est généré automatiquement pour nous à partir de l'outil CASE que nous utilisons pour la modélisation des données (ERWin). ERWin vous permet de créer des "propriétés définies par l'utilisateur" (UDP), et il dispose d'un langage de macro qui peut être utilisé pour générer virtuellement n'importe quel code dont vous avez besoin en fonction de votre schéma, donc tout ce que nous avons à faire pour activer cette fonctionnalité est d'ajouter la colonne ChildCount à la table parent appropriée et de définir l'UDP pour "Relation avec comptage de références" sur true.
Comme je l'ai souligné dans les commentaires ci-dessus, les déclencheurs ne peuvent pas être utilisés pour remplacer complètement la RI déclarée, puisque vous ne pouvez pas utiliser FOR UPDATE pour faire fonctionner correctement les règles de suppression en cascade. Mais c'est génial pour des règles supplémentaires comme celles-ci.
Remarque : Ce code est en production depuis maintenant 11 ans - il a été conçu à l'époque où nous utilisions encore Oracle 7. Si quelqu'un a une façon plus moderne de faire cela en utilisant les fonctionnalités intégrées d'Oracle, je serais intéressé de l'entendre.
