我有下表:
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) );
存储在其中的实体X是按层次结构组织的:如果(A1,B1,C1)存在一行,C1 IS NOT NULL则认为该行是(A1,C1,C2)任何C2内容的“子项” 。由于项目不能从其自身衍生而来,因此我想将存在循环层次序列的行为定为非法:
X
(A1,B1,C1)
C1 IS NOT NULL
(A1,C1,C2)
C2
-- 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 */
我该怎么做呢?
另外,我可以在表中添加一个表示层次结构中“级别”的字段:
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) );
然后,我要求将其设置LEVEL为0when C IS NULL,parent's LEVEL + 1否则。
LEVEL
0
C IS NULL
parent's LEVEL + 1
我正在使用SQL Server 2008 R2。
为了检查循环引用,我使用了触发器和递归CTE:
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 x.A, x.B, X.C, 1 AS [Type] FROM inserted i JOIN X x ON i.A = x.A AND i.C = x.B UNION ALL SELECT x.A, x.B, X.C, 2 AS [Type] FROM CteHierarchy i JOIN X x ON i.A = x.A AND i.C = x.B WHERE NOT EXISTS ( SELECT * FROM inserted a WHERE a.A = x.A AND a.B = x.B ) ) INSERT @Results ([Exists]) SELECT TOP(1) 1 FROM CteHierarchy h JOIN X x ON h.A = x.A AND h.C = x.B OPTION(MAXRECURSION 1000); IF EXISTS(SELECT * FROM @Results) BEGIN ROLLBACK; RAISERROR('Circular references detected', 16, 1); END END GO
现在,我们可以运行一些测试:
--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 trigger] 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
结果:
*****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 trigger. 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 trigger. The batch has been aborted.
对于第二个测试,您可以看到此触发器如何取消(ROLLBACK TRANSACTION)事务,并且在UPDATE之后,什么都没有执行(在当前 批次中 )。
ROLLBACK TRANSACTION