用友_存货代码自动改为助计码的存储过程

在用友T6 U8系列中不会自动生成助计码

   下面代码在数据库中执行一次就OK

脚本为 为存货档案表增加了一个存储过程[存货代码自动生成]

  通过存储过程自行修改存货代码为助计码

code:

/*    存货代码自动生成存储过程                                */
/*    执行更新和插入时发生                                    */
/*    会增加一个f_GetPy函数 用做取拼音之用                    */
/*    code by 张朋 e-mial:zh3305@gmail.com QQ:786745989       */
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[存货代码自动生成] 
   ON [dbo].[Inventory] 
 FOR INSERT, UPDATE 
AS 
BEGIN
IF  not EXISTS (SELECT * FROM SYSOBJECTS  WHERE NAME ='f_GetPy' AND Xtype='FN')
begin
exec ('create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1)) 
insert into @t(chr,letter)
  select ''吖'',''A'' union all select ''八'',''B'' union all 
  select ''嚓'',''C'' union all select ''咑'',''D'' union all 
  select ''妸'',''E'' union all select ''发'',''F'' union all 
  select ''旮'',''G'' union all select ''铪'',''H'' union all 
  select ''丌'',''J'' union all select ''咔'',''K'' union all 
  select ''垃'',''L'' union all select ''嘸'',''M'' union all 
  select ''拏'',''N'' union all select ''噢'',''O'' union all 
  select ''妑'',''P'' union all select ''七'',''Q'' union all 
  select ''呥'',''R'' union all select ''仨'',''S'' union all 
  select ''他'',''T'' union all select ''屲'',''W'' union all 
  select ''夕'',''X'' union all select ''丫'',''Y'' union all 
  select ''帀'',''Z''
  select @strlen=len(@str) ,
   @re=''''
  while @strlen>0
  begin
    select top 1 @re=letter+@re,@strlen=@strlen-1
      from @t a where chr<=substring(@str,@strlen,1)
      order by chr desc
    if @@rowcount=0
      select @re=substring(@str,@strlen,1)+@re,
       @strlen=@strlen-1
  end
  return(@re)
end')
end 
declare @strlen nvarchar(1000)
declare @cInvCode  nvarchar(1000)  ,@cInvName  nvarchar(1000)   
declare In_Prdetail_Cursor cursor for Select cInvCode ,cInvName  from INSERTED
open In_Prdetail_Cursor
Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
While @@fetch_status=0  
begin
exec('update dbo.Inventory set cInvAddCode = dbo.f_GetPy('''+@cInvName+''') where cInvCode= '+@cInvCode) ;
Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
end
close In_Prdetail_Cursor
deallocate In_Prdetail_Cursor 
END