有表Aid parentid depth
1 null 0
2 1 1
3 2 2
4 3 3
5 1 1我想做个触发器比如当ID=2的depth更改成2的时候,他下面parentid=2的depth更改成depth+1
以此类推 他子分类下的DEPTH都+1这个触发器怎么弄哦。要不要用游标呢?我自己写了一个CREATE TRIGGER [dbo].[表A_Update]
ON [dbo].[表A]
AFTER UPDATE
AS
BEGIN
if not update(Depth) return
update 表A set depth = inserted.depth+1 from 表A
inner join inserted on 表A.ParentID=inserted.id
END总与几条记录没触发。唉
1 null 0
2 1 1
3 2 2
4 3 3
5 1 1我想做个触发器比如当ID=2的depth更改成2的时候,他下面parentid=2的depth更改成depth+1
以此类推 他子分类下的DEPTH都+1这个触发器怎么弄哦。要不要用游标呢?我自己写了一个CREATE TRIGGER [dbo].[表A_Update]
ON [dbo].[表A]
AFTER UPDATE
AS
BEGIN
if not update(Depth) return
update 表A set depth = inserted.depth+1 from 表A
inner join inserted on 表A.ParentID=inserted.id
END总与几条记录没触发。唉
CREATE TRIGGER [dbo].[表A_Update]
ON [dbo].[表A]
AFTER UPDATE
AS
BEGIN
IF UPDATE(id)
BEGINDECLARE @pid INTSELECT @pid = parentid FROM INSERTEDUPDATE tbA SET depth= depth + 1
WHERE parentid = @pid END;
END;
create table A(id int,parentid int,depth int)
insert A select 1,null,0
insert A select 2,1,1
insert A select 3,2,2
insert A select 4,3,3
insert A select 5,2,1create trigger trigger_test on A
after update
as
begin
if not update(depth) return
declare @id int
select @id=deleted.id from deleted
;with test as(
select id,parentid,depth from A where id=@id
union all
select c.id,c.parentid,c.depth from test p inner join A c
on p.id=c.parentid
)
update A set A.depth=A.depth+1 from A inner join test on A.id=test.id
where A.id<>@id
endupdate A set depth=3 where id=2
select * from Aid parentid depth
----------- ----------- -----------
1 NULL 0
2 1 3
3 2 3
4 3 4
5 2 2
这个结果有错误update A set depth=3 where id=2
select * from Aid parentid depth
----------- ----------- -----------
1 NULL 0
2 1 3
3 2 3
4 3 4
5 2 2
更新了ID=2的DEPTH=3 那ID=3的DEPTH应该是4 为何还是3???
after update
as
begin
if not update(depth) return
declare @id int
select @id=deleted.id from deleted
;with test as(
select id,parentid,depth from A where id=@id
union all
select c.id,c.parentid,depth=p.depth + 1 from test p inner join A c
on p.id=c.parentid
)
update A set depth=test.depth from A inner join test on A.id=test.id
where A.id<>@id
end
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665