在SQL中检测循环引用

我有下表：

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不是NULL,那么无论C2是什么,它都被认为是(A1,C1,C2)的“子”.由于项目不能自行下降,我想将循环分层序列存在为非法：

-- legal
INSERT INTO X (A1,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,LEVEL INT NOT NULL,B)
);

然后我想要求当C IS为NULL时LEVEL为0,否则为父级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,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,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,Line 7
The transaction ended in the trigger. The batch has been aborted.

对于第二个测试,您可以看到此触发器如何取消(ROLLBACK TRANSACTION)事务,并且在UPDATE之后,没有执行任何操作(在当前批处理中).

在SQL中检测循环引用

解决方法

猜你在找的MsSQL相关文章