/*把5位货号的普通商品的货号前面加8888*/
SET QUOTED_IDENTIFIER OFF
go

if exists(select 1
  from item a
 inner join item b on b.item_no = right('8888' + rtrim(a.item_no), 9) and len(rtrim(b.item_no)) = 9
 where len(rtrim(a.item_no)) = 5 and a.weight_flag='0'
)
begin
  print '不能搞，搞了货号会重复，重复商品如下:'
  select a.item_no, a.item_name, b.item_no, b.item_name
  from item a
 inner join item b on b.item_no = right('8888' + rtrim(a.item_no), 9) and len(rtrim(b.item_no)) = 9
 where len(rtrim(a.item_no)) = 5 and a.weight_flag='0'
  return
end

declare @table_name varchar(50), @sql varchar(500)
declare table_name_cursor cursor local fast_forward for
select o.name
  from sysobjects o
  join syscolumns c on o.id=c.id and c.name='item_no'
 where o.xtype='U' and o.name <> 'item'
 order by o.name
open table_name_cursor
fetch next from table_name_cursor into @table_name
while @@fetch_status=0
begin
  set @sql='alter table '+@table_name+' disable trigger all' + char(13) + char(10)
  set @sql=@sql + "update x set item_no = '8888' + rtrim(x.item_no)  from "+@table_name+ " x inner join item bi on bi.item_no = x.item_no where len(rtrim(bi.item_no)) = 5   and bi.weight_flag = '0' " + char(13) + char(10)
  set @sql=@sql + 'alter table '+@table_name+' enable trigger all'
  execute(@sql)
--  print @sql
  fetch next from table_name_cursor into @table_name
end
close table_name_cursor
deallocate table_name_cursor

alter table item disable trigger all
update x set item_son_no = right('8888' + rtrim(x.item_son_no), 9)
  from item x
 inner join item bi on bi.item_no = x.item_son_no
 where len(rtrim(bi.item_no)) = 5   and bi.weight_flag = '0'

update item set item_no= right('8888' + rtrim(item_no), 9)
 where len(rtrim(item.item_no)) = 5
   and item.weight_flag = '0'
alter table item enable trigger all