用友_存货代码自动改为助计码的存储过程
在用友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