Détection de references circulaires dans SQL

J'ai le tableau suivant:

CREATE TABLE X ( A SOMETYPE NOT NULL, B SOMETYPE NOT NULL, C SOMETYPE NULL, PRIMARY KEY (A,B), FOREIGN KEY (A,C) REFERENCES X (A,B) ); 

Les entités stockées dans X sont organisées hiérarchiquement: Si une ligne (A1,B1,C1) existe et que C1 IS NOT NULL n'est C1 IS NOT NULL alors il est considéré comme un «enfant» de (A1,C1,C2) quel que soit C2 . Puisqu'un object ne peut pas descendre de lui-même, je voudrais rendre illégale l'existence de séquences hiérarchiques circulaires:

 -- legal INSERT INTO X (A1,B1,NULL); INSERT INTO X (A1,B2,B1); INSERT INTO X (A1,B3,B2); INSERT INTO X (A1,B4,B2); -- currently legal, but I want to make it illegal UPDATE X SET C = B1 WHERE B = B1; /* B1-B1 */ UPDATE X SET C = B2 WHERE B = B1; /* B1-B2-B1 */ UPDATE X SET C = B3 WHERE B = B1; /* B1-B2-B3-B1 */ UPDATE X SET C = B4 WHERE B = B1; /* B1-B2-B4-B1 */ UPDATE X SET C = B2 WHERE B = B2; /* B2-B2 */ UPDATE X SET C = B3 WHERE B = B2; /* B2-B3-B2 */ UPDATE X SET C = B4 WHERE B = B2; /* B2-B4-B2 */ UPDATE X SET C = B3 WHERE B = B3; /* B3-B3 */ UPDATE X SET C = B4 WHERE B = B4; /* B4-B4 */ 

Comment puis-je faire cela?


Alternativement, je pourrais append un champ représentant le "niveau" dans la hiérarchie à la table:

 CREATE TABLE X ( A SOMETYPE NOT NULL, B SOMETYPE NOT NULL, C SOMETYPE NULL, LEVEL INT NOT NULL, PRIMARY KEY (A,B), FOREIGN KEY (A,C) REFERENCES X (A,B) ); 

Ensuite, je voudrais requestr que LEVEL soit 0 lorsque C IS NULL , et parent's LEVEL + 1 sinon.


J'utilise SQL Server 2008 R2.

Pour vérifier les references circulaires, j'ai utilisé un triggersur et un CTE récursif:

 CREATE TRIGGER trgIU_X_CheckCircularReferences ON dbo.X AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Results TABLE ([Exists] BIT); WITH CteHierarchy AS ( SELECT xA, xB, XC, 1 AS [Type] FROM inserted i JOIN X x ON iA = xA AND iC = xB UNION ALL SELECT xA, xB, XC, 2 AS [Type] FROM CteHierarchy i JOIN X x ON iA = xA AND iC = xB WHERE NOT EXISTS ( SELECT * FROM inserted a WHERE aA = xA AND aB = xB ) ) INSERT @Results ([Exists]) SELECT TOP(1) 1 FROM CteHierarchy h JOIN X x ON hA = xA AND hC = xB OPTION(MAXRECURSION 1000); IF EXISTS(SELECT * FROM @Results) BEGIN ROLLBACK; RAISERROR('Circular references detected', 16, 1); END END GO 

Maintenant, nous pouvons faire quelques tests:

 --Test 1 - OK PRINT '*****Test 1 - OK*****'; SELECT * FROM X; BEGIN TRANSACTION; UPDATE X SET C = 'B1' WHERE B = 'B4'; SELECT * FROM X; --This transaction can be commited without problems --but I will cancel all modification so we can run the second test ROLLBACK TRANSACTION; PRINT '*****End of test 1*****'; GO --Test 2 - NOT OK PRINT '*****Test 2 - NOT OK*****'; SELECT * FROM X; BEGIN TRANSACTION; UPDATE X SET C = 'B1' WHERE B = 'B1'; --Useless in this case (test 2 & test 3) --Read section [If a ROLLBACK TRANSACTION is issued in a sortinggger] from http://msdn.microsoft.com/en-us/library/ms181299.aspx SELECT * FROM X; --Useless ROLLBACK TRANSACTION; --Useless PRINT '*****End of test 2*****'; GO PRINT '*****Test 3 - NOT OK*****'; SELECT * FROM X; BEGIN TRANSACTION; UPDATE X SET C = 'B4' WHERE B = 'B1'; GO 

Résultats:

 *****Test 1 - OK***** (4 row(s) affected) (0 row(s) affected) (1 row(s) affected) (4 row(s) affected) *****End of test 1***** *****Test 2 - NOT OK***** (4 row(s) affected) (1 row(s) affected) Msg 50000, Level 16, State 1, Procedure trgIU_X_CheckCircularReferences, Line 34 Circular references detected Msg 3609, Level 16, State 1, Line 8 The transaction ended in the sortinggger. The batch has been aborted. *****Test 3 - NOT OK***** (4 row(s) affected) (1 row(s) affected) Msg 50000, Level 16, State 1, Procedure trgIU_X_CheckCircularReferences, Line 34 Circular references detected Msg 3609, Level 16, State 1, Line 7 The transaction ended in the sortinggger. The batch has been aborted. 

Pour le deuxième test, vous pouvez voir comment ce triggersur a annulé ( ROLLBACK TRANSACTION ) la transaction et, après UPDATE, rien n'a été exécuté (dans le lot actuel).