在SQL中检测循环引用

在SQL中检测循环引用

为了检查循环引用，我使用了触发器和递归CTE：

CREATE TRIGGER trgIU_X_CheckCircularReferencesON dbo.XAFTER INSERT, UPDATe ASBEGIN       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);    ENDENDGO

现在，我们可以运行一些测试：

--Test 1 - OKPRINT '*****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 testROLLBACK TRANSACTION;PRINT '*****End of test 1*****';    GO--Test 2 - NOT OKPRINT '*****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.aspxSELECt * FROM X;--UselessROLLBACK TRANSACTION;--UselessPRINT '*****End of test 2*****';        GOPRINT '*****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 34Circular references detectedMsg 3609, Level 16, State 1, Line 8The 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 34Circular references detectedMsg 3609, Level 16, State 1, Line 7The transaction ended in the trigger. The batch has been aborted.

对于第二个测试，您可以看到此触发器如何取消（

ROLLBACK TRANSACTION

）事务，并且在UPDATE之后，什么都没有执行（在当前 批次中
）。