[SOLVED] How to prevent tree having circular references

How to prevent tree having circular references

original table is more complicated but.. i got a table which stores great many trees inside like; what im looking is a nontrigger mothod like constraint or a trigger which decides to rollback very fast..

create table myTreeTable ( 
    id int not null identity(1,1),
    node varchar(80),
    parent varchar(80),
    constraint PK_mytable primary key nonclustered (node)
)

insert into myTreeTable values ('a', null)
insert into myTreeTable values ('b', 'a')
insert into myTreeTable values ('c', 'a')
insert into myTreeTable values ('d', 'a')
insert into myTreeTable values ('e', 'b')
insert into myTreeTable values ('f', 'b')
insert into myTreeTable values ('g', 'd')
insert into myTreeTable values ('h', 'f')
insert into myTreeTable values ('i', 'g')
insert into myTreeTable values ('j', 'g')

insert into myTreeTable values ('aa', null)
insert into myTreeTable values ('bb', 'aa')
insert into myTreeTable values ('cc', 'aa')
insert into myTreeTable values ('dd', 'aa')
insert into myTreeTable values ('ee', 'bb')
insert into myTreeTable values ('ff', 'bb')
insert into myTreeTable values ('gg', 'dd')
insert into myTreeTable values ('hh', 'ff')
insert into myTreeTable values ('ii', 'gg')
insert into myTreeTable values ('jj', 'gg')

what i need to prevent is an update like

update myTreeTable set parent='j' where ID=1

or an insert like

insert into myTreeTable values ('aaa', 'bbb'),('bbb', 'ccc'),('ccc','aaa')

Solution

this is how i solved my problem for good.

CREATE TRIGGER PreventCycle 
ON myTreeTable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM inserted)
        RETURN;

    ;WITH ChangedRows AS (
        SELECT i.node, i.parent
        FROM inserted i
        LEFT JOIN deleted d ON i.node = d.node
        WHERE d.node IS NULL 
            OR i.parent <> d.parent 
            OR (i.parent IS NULL AND d.parent IS NOT NULL) 
            OR (i.parent IS NOT NULL AND d.parent IS NULL) 
    )

    ;WITH CurrentGraph AS (
        SELECT node, parent 
        FROM myTreeTable 
        WHERE node NOT IN (SELECT node FROM inserted) 
        UNION ALL
        SELECT node, parent FROM ChangedRows 
    )

    , Paths AS (
        SELECT 
            cr.node AS StartNode, 
            cr.parent AS CurrentNode
        FROM ChangedRows cr
        WHERE cr.parent IS NOT NULL 
        UNION ALL
        SELECT 
            p.StartNode, 
            cg.parent 
        FROM CurrentGraph cg
        INNER JOIN Paths p 
            ON p.CurrentNode = cg.node 
        WHERE cg.parent IS NOT NULL 
    )

    IF EXISTS (
        SELECT * 
        FROM Paths 
        WHERE CurrentNode = StartNode
    )
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50000, 'Cycle detected.', 1;
    END;
END;