如何防止树具有循环引用 [英] 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

或类似

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

推荐答案

您可以通过下面的查询来检查是否有循环引用：

You can check if circular references or not by bellow query:

create trigger check_circular_ref_tgr on myTreeTable for insert, update as
begin

declare @new_node varchar(80), @new_parent varchar(80)

select @new_node=node, @new_parent=parent from inserted

with p(id) as (
select parent from myTreeTable where node = @new_parent
union all 
select parent from myTreeTable inner join p on myTreeTable.node=p.id where parent is not null)

if exists(select id from p where id=@new_node)
  raiseerror(N'circular reference error', 10, 1)

end

这篇关于如何防止树具有循环引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！