触发器问题扩展，关联更新的问题

Question

很抱歉，我现在总共就只有64分了
关于昨天的问题，蓉儿姐姐给了我一个非常好的答案，地址：
http://topic.csdn.net/u/20081117/08/0e00b973-68da-4442-8f8d-1a73a96674c0.html
现在我在表A中加入一个字段parentid,如下：表A： 
id int IDENTITY (1, 1) NOT NULL ,
parentid int NOT NULL ,
childrenStr varchar (1000) COLLATE Chinese_PRC_CI_AS NOT NULL
CONSTRAINT A_parentid DEFAULT (0) FOR parentid,
CONSTRAINT A_childrenStr DEFAULT (0) FOR childrenStr
id parentid childrenStr 
--------------------
1  0        1,2
2  1        3
3  2        4,5
4  1        6
5  2        5
6  4        8
表B； 
id bigint
classid int
id   classid
--------------
1    1
2    1
3    2
4    3
5    3
6    4
7    5
8    6
当childrenStr有新增时，同时插入到parent行中（如在表B中新插入一条数据并指定classid=6，我想要同时更新6的parent行:id=4,1）应该怎么实现啊

Answer 1

为方便大家了解，我将昨天的相关内容转帖过来：
－－－－－－－－－－－－转帖问题－－－－－－－－－－－－－－－－
表A： 
id int
childrenStr varchar(1000)
id childrenStr
--------------------
1       1,2
2       3
3       4,5
表B； 
id bigint
classid int
id   classid
--------------
1    1
2    1
3    2
4    3
5    3
我想加入几个触发器 
要在表B insert,update,delete的时候分别对应修改表A的childrenStr 
例如： 
1、向表B中插入一条记录并绑定classid=1的时候，假设新记录id为6，则表A中id=1的childrenStr=1,2,6 
2、将表B的id=2的记录的classid更改为2,则表A中id=1的childrenStr=1,6 而id=2的childrenStr=3,2 
3、删除表B的id=4的记录,则表A中id=3的childrenStr=5 
我不知道如何获update 表B时B表中正在操作的记录信息,请各位高手帮忙写一下,谢谢,100分送上!
－－－－－－－－－－－－转帖已采纳答案－－－－－－－－－－－－－－
SQL codeif object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[childrenstr] varchar(20))
insert [ta]
select 1,'1,2' union all
select 2,'3' union all
select 3,'4,5'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[classid] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,3 union all
select 5,3select * from [ta]
select * from [tb]
go
create trigger tri_tb on tb
for insert,update,delete
as
if exists(select 1 from deleted) --update or delete
begin
    update ta
    set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
    from deleted d
    where ta.id=d.classid
end
if exists(select 1 from inserted) --update or insert
begin
    update ta
    set childrenstr=childrenstr+','+rtrim(i.id)
    from inserted i
    where ta.id=i.classid
end
goinsert tb values(6,1)
select * from [ta]
/*
1    1,2,6
2    3
3    4,5
*/update tb set classid=2 where id=2
select * from [ta]
/*
1    1,6
2    3,2
3    4,5
*/
delete tb where id=4
select * from [ta]
/*
1    1,6
2    3,2
3    5
*/

Answer 2

只是当子类新增的时候才加入到父类
我是这样想的，当表B新增内容（假设为B.id=8）时，首先我会让它绑定一个类（classid=6），就把它加入到类中(update A set ChildrenStr=ChildrenStr+','+8 where id=6)，而这个类中虽然有了，但它的父类（A.id=4,A.id=1）中并没有，所以想通过这个新增动作同时加入到它的父类(A.id=4)和父类的父类(A.id=1)中，当我要从这个类(A.id=6)移除这条内容时，可能在其它类中有关联到这条内容，所以不对其它类和表B进行操作，而当表B删除该内容（B.id=8）时,则从A表中所有包含该内容的类中移除对它的关联

Answer 3

if object_id('tri_tb') is not null drop trigger tri_tb
go 
create trigger tri_tb on tb 
for insert,update,delete 
as 
if exists(select 1 from deleted) --update or delete 
begin 
    update ta 
    set childrenstr=substring(replace(','+childrenstr+',',','+rtrim(d.id)+',',','),2,len(replace(','+childrenstr+',',','+rtrim(d.id)+',',','))-2) 
    from deleted d 
    where charindex(','+rtrim(d.id)+',',','+ta.childrenstr+',')>0
end 
if exists(select 1 from inserted) --update or insert 
begin 
   with cte as
   (
    select a.id,a.parentid from ta a join inserted i on a.id=i.classid
    union all
    select a.id,a.parentid from ta a join cte c on a.id=c.parentid
    )
    update ta 
    set childrenstr=childrenstr+','+rtrim(i.id) 
    from cte 
    where ta.id=cte.id 
end 
go 正在重装系统，用记事本写了个大概，没法帮你测

Answer 4

另外说一下，原解决方案中的： set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'') 
这一句不够严密，应改为类似：set childrenstr=substring(replace(','+childrenstr+',',','+rtrim(d.id)+',',','),2,len(replace(','+childrenstr+',',','+rtrim(d.id)+',',','))-2)

Answer 5

id parentid childrenStr 
--------------------
1  0        1,2
2  1        3
3  2        4,5
4  1        6
5  2        5
6  4        8
表B； 
id bigint
classid int
id   classid
--------------
1    1
2    1
3    2
4    3
5    3
6    4
7    5---应该是5    5吧
8    6你的数据是不是有点问题？

Answer 6

id parentid childrenStr 
-------------------- 
1  0        1,2 
2  1        3 
3  2        4,5 
4  1        6    ---4的parentid上一级的父节点好像是3
5  2        5    ---5的parentid上一级的父节点好像是3  ，如果只要找到它的上一级父节点 的话，那就是3，
6  4        8 先看看你的数据吧。

Answer 7

我改成这样，提示我错误
服务器: 消息 141，级别 15，状态 1，过程 tri_tb，行 22
向变量赋值的 SELECT 语句不能与数据检索操作结合使用。if object_id('tri_tb') is not null drop trigger tri_tb
go 
create trigger tri_tb on tb 
for insert,update,delete 
as 
if exists(select 1 from deleted) --update or delete 
begin 
    update ta 
    set childrenstr=substring(replace(','+childrenstr+',',','+rtrim(d.id)+',',','),2,len(replace(','+childrenstr+',',','+rtrim(d.id)+',',','))-2) 
    from deleted d 
    where charindex(','+rtrim(d.id)+',',','+ta.childrenstr+',')>0
end 
if exists(select 1 from inserted) --update or insert 
begin
  declare @re_ids varchar(100)
  declare @tempid int
  set @re_ids = ''
  set @tempid = 0
  select @tempid=classid, @re_ids = convert(varchar(8),classid) from inserted
  while exists (select 1 from ta where id = @tempid and parentid <> 0)
    begin
      select tempid = a2.id , @re_ids =convert(varchar(8),a2.id) + ',' + @re_ids from ta a1 , ta a2 where a1.id = @tempid and a1.ParentId = a2.id    end    update ta 
    set childrenstr=childrenstr+','+rtrim(inserted.id) 
    from ta 
    where charindex(','+rtrim(id)+',',','+@re_ids+',')>0
end 
go 

Answer 8

create trigger tri_tb on tb 
for insert,update,delete 
as 
if exists(select 1 from deleted) --update or delete 
begin 
    update ta 
    set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'') 
    from deleted d 
    where ta.id=d.classid 
end 
if exists(select 1 from inserted) --update or insert 
begin 
    update ta 
    set childrenstr=childrenstr+','+rtrim(i.id) ,
        parentid=(select tb.classid from tb join inserted on tb.id=inserted.classid)--加这一行
    from inserted i 
    where ta.id=i.classid 
end 
go 

Answer 9

多谢关注，我不知道你是如何判定我id=4时的parentid=3，id=5时parentid=3的
不过tb和ta关联上确实我写错了,ta.childrenstr里面确实应该包含的是tb.id=7,不过今天比昨天多几行数据只是为了新加的parentid这个字段,让ta内多几个级别这样容易看出来效果.说白了,我这就好像是一个权限模块,在新加入一个插件后,就将这个插件指定给一个用户组,但是指定给的这个用户组可能只是低权限用户,比如说op,这个时候我需要让op leader也同样拥有这个权限,当然administrator肯定也得有这个权限,不能说我加给了op,opleader反而没有这个权限.若然,operator比leader的权限还大了.:)

Answer 10

还有就是当tb中某一行删除时,就从ta中包含tb.id的行中移除,我觉得这里该用到一个循环,但是该怎么循环我搞不懂,我对数据库不是很熟悉,唉.搞这个累死我了,放到程序里我5分钟就搞定了

Answer 11

if object_id('tri_tb') is not null drop trigger tri_tb
go 
create trigger tri_tb on tb 
for insert,update,delete 
as 
if exists(select 1 from deleted) --update or delete 
begin 
    update ta 
    set childrenstr=substring(replace(','+childrenstr+',',','+rtrim(d.id)+',',','),2,len(replace(','+childrenstr+',',','+rtrim(d.id)+',',','))-2) 
    from deleted d 
    where charindex(','+rtrim(d.id)+',',','+ta.childrenstr+',')>0
end 
if exists(select 1 from inserted) --update or insert 
begin 
create table #t1(id int,parentid int)
create table #t2(id int,parentid int)
insert #t1 select id,parentid from inserted 
insert #t2 select id,parentid from inserted while exists(select 1 from #t1)
begin
    update ta 
    set childrenstr=childrenstr+','+rtrim(t.id) 
    from #t1 t
    where ta.id=t.id     delete #t1
    insert #t1 select id,parentid from ta a join #t2 b on a.id=b.parentid    delete #t2
    insert #t2 select * from #t1
end
drop table #t1,#t2
end 
go 

Answer 12

我用以下语句测试没有通过:insert into tb (classid) values (6)
insert into tb (classid) values (5)
go
select * from ta
go
delete from tb where id=8
go
select * from ta
go我已经将ta和tb设置id为主键并自动递增,ta.parentid和tb.classid都设置为不为空,默认为0了

Answer 13

提示:
服务器: 消息 207，级别 16，状态 3，过程 tri_tb，行 15
列名 'parentid' 无效。
服务器: 消息 207，级别 16，状态 1，过程 tri_tb，行 16
列名 'parentid' 无效。
服务器: 消息 209，级别 16，状态 1，过程 tri_tb，行 26
列名 'id' 不明确。
服务器: 消息 209，级别 16，状态 1，过程 tri_tb，行 26
列名 'parentid' 不明确。（所影响的行数为 6 行）服务器: 消息 536，级别 16，状态 3，过程 tri_tb，行 6
向 substring 函数传递了无效的 length 参数。
语句已终止。