Detecting Circular References in SQL

Detecting circular references in SQL

To check for circular references i have used a trigger and recursive 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

Now, we can run some 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 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

Results:

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

For the second test, you can see how this trigger has canceled (ROLLBACK TRANSACTION) the transaction and, after UPDATE, nothing has been executed (in current batch).

How to find circular dependent table in sql server

You don't really need to buy a tool to find these references.

SELECT 
  OBJECT_SCHEMA_NAME(fk1.parent_object_id)
   + '.' + OBJECT_NAME(fk1.parent_object_id), 
  OBJECT_SCHEMA_NAME(fk2.parent_object_id)
   + '.' + OBJECT_NAME(fk2.parent_object_id)
FROM sys.foreign_keys AS fk1
INNER JOIN sys.foreign_keys AS fk2
ON fk1.parent_object_id = fk2.referenced_object_id
AND fk2.parent_object_id = fk1.referenced_object_id;

How to Find Circular References in CTE between Parent/Child tables

Your CTE already has a hierarchy with the ItemID path concatenated. How about using that to determine if the item has already been seen?

Add a new column to the anchor portion of your CTE, HasCycle = Convert(bit, 0).

Then in the recursive portion of your CTE, add the column and a condition in the WHERE clause like so:

...
UNION ALL
SELECT
   ... other columns,
   HasCycle = Convert(bit,
      CASE
          WHEN ':' + parent.HierarchyItem + ':' LIKE
             '%:' + Convert(varchar(100), child.ItemID) + ':%'
          THEN 1
          ELSE 0
      END)
FROM
   ...
WHERE
   ...
   AND parent.HasCycle = 0 --terminate after cycle is found
;

Then you can select from the recursive CTE WHERE HasCycle = 1 and see all the rows that begin a cycle and their exact path upwards in the HierarchyItem.

How to identify and list Circular Reference elements in Postgres

So all references:

{28397468N,88518119N,87606705N,28397468N}
{87606705N,28397468N,88518119N,87606705N}
{88518119N,87606705N,28397468N,88518119N}

are correct but just start from different element.

I need a simple answer like this: 28397468N>88518119N>87606705N>28397468N

So what's needed is a filter for the same circle refs.

Let's do that in a way:

• sort distinct items in arrays
• aggregate them back - so for all references it will be '{28397468N,87606705N,88518119N}'
• use produced value for DISTINCT FIRST_VALUE

WITH D (circle_ref ) AS (
VALUES
('{28397468N,88518119N,87606705N,28397468N}'::text[]),
('{87606705N,28397468N,88518119N,87606705N}'::text[]),
('{88518119N,87606705N,28397468N,88518119N}'::text[])
), ordered AS (
SELECT 
        D.circle_ref,
        (SELECT ARRAY_AGG(DISTINCT el ORDER BY el) FROM  UNNEST(D.circle_ref) AS el ) AS ordered_circle
    FROM 
        D
)
SELECT DISTINCT
        FIRST_VALUE (circle_ref) OVER (PARTITION BY ordered_circle ORDER BY circle_ref) AS circle_ref 
    FROM 
        ordered;

---