更新ParentId时，自动更新子级的ParentIds

 1 --====================================
 2 --  更新ParentId时，自动更新子级的ParentIds
 3 --====================================
 4 
 5 ALTER TRIGGER UpdateParentIds ON S_User 
 6     FOR update
 7 AS 
 8 
 9 DECLARE @id int                      --当前用户的ID
10 DECLARE @parentId int                --当前用户的ParentId
11 DECLARE @parentIds varchar(500)      --更新后的ParentIds
12 DECLARE @count int                   --当前用户的子级数量
13 SELECT @parentId =i.ParentId,@id=i.ID FROM inserted i    --记录下当前用户的ID及ParentId
14 PRINT '@parentId:'+convert(varchar,@parentId)
15 PRINT '@id:'+convert(varchar,@id)
16 SELECT @parentIds =convert(varchar,u.ID)+','+ u.ParentIds FROM S_User u where u.ID=@parentId  --根据ParentId 拼接最新的ParentIds
17 PRINT '@parentIds:'+convert(varchar,@parentIds)
18 SET  XACT_ABORT   ON
19 IF UPDATE(ParentId)
20 BEGIN
21    PRINT '更新beigin'
22    UPDATE S_User SET ParentIds = @parentIds WHERE ID=@id
23    SELECT @count=COUNT(*) FROM S_User WHERE ParentId = @id
24    IF(@count>0)
25    BEGIN
26     UPDATE S_User SET ParentId = @id WHERE id in (SELECT id FROM S_User WHERE ParentId = @id)
27    END
28    PRINT '更新end'
29 END
30 GO