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).