SQL 触发器、游标、for xml、Transact-SQL（to整理）

为什么80%的码农都做不了架构师？>>>   

select * from edu_act_list


--alter table edu_act_list add DeptIds varchar(max)
--drop trigger triggerEdu_Act_List
create trigger triggerEdu_Act_List
On Edu_Act_List
for insert,update
as
begin
  declare @uids varchar(max)
  declare @key varchar(36)
  declare @deptids varchar(max)
  set @deptids=''
  select @key=Pri_Key,@uids=UserIds from inserted
 
  /*
  消息 6819，级别 16，状态 3，过程 triggerEdu_Act_List，第 11 行
  不允许 ASSIGNMENT 语句中包含 FOR XML 子句。
 
  select @deptids=sys_30_col_60 from
  (select distinct sys_30_col_60 from sys_30 where charindex(','+ltrim(sys_30_col_10)+',',','+@uids+',') > 0 ) a
  for xml path
 
  set @deptids =replace(replace(replace(@deptids,'</sys_30_col_60><sys_30_col_60>',','),'</sys_30_col_60>',''),'<sys_30_col_60>','')
  */
 
  /*
  一直转...
  declare cursor1 cursor for
    select sys_30_col_60 from sys_30 where charindex(','+ltrim(sys_30_col_10)+',',','+@uids+',') > 0
    
  declare @deptid int
  open cursor1
  fetch next from cursor1 into @deptid
  while @@fetch_status=0
  begin
     if @deptids=''
       set @deptids=cast(@deptid as varchar)
     else
       set @deptids=@deptids+','+cast(@deptid as varchar)
  end
 
  close cursor1
  deallocate cursor1
  */
 
  declare @deptid int
  select @deptid=min(sys_30_col_60) from
    (select distinct sys_30_col_60 from sys_30 where charindex(','+ltrim(sys_30_col_10)+',',','+@uids+',') > 0) a
 
  while @deptid is not null
  begin
    if(@deptids='')
       set @deptids=cast(@deptid as varchar)
     else
       set @deptids=@deptids+','+cast(@deptid as varchar)
    select @deptid = min( sys_30_col_60 ) from
    (select distinct sys_30_col_60 from sys_30 where charindex(','+ltrim(sys_30_col_10)+',',','+@uids+',') > 0) a where a.sys_30_col_60 > @deptid
  end

 
  update Edu_Act_List set DeptIds=@deptids where Pri_Key=@key
end

--declare @uids varchar(max)
--set @uids='1,2'
--select sys_30_col_60 from (select distinct sys_30_col_60 from sys_30
--where charindex(','+ltrim(sys_30_col_10)+',',','+@uids+',') > 0 ) a for xml path('')

--update Edu_Act_List set DeptIds=1 where pri_key='57c58ae6-4796-4e07-9c21-eed817d0819f'

转载于:https://my.oschina.net/ind/blog/368249