| 解决方案： |
因客户很急，补丁又找不到，临时用触发器+自定义项解决。代码如下：--drop function zdxje
--go
--创建函数,根据客户要求对金额进行转换
create function zdxje(@cNum varchar(60)) returns varchar(60) as
begin
declare @cChar char(1),@iLen int,@dxje varchar(60),@fs int--new
set @fs=0 --new
set @iLen=0
set @dxje=''
set @cChar=''
if charindex('-',@cNum)>0 --new
begin
set @fs=1
set @cNum=substring(@cNum,charindex('-',@cNum)+1,len(@cNum)-1)
end
set @iLen=len(rtrim(ltrim(@cNum)))
while @ilen<9
begin
set @cNum='X'+@cNum
set @ilen=@ilen+1
end
while @iLen>0
begin
set @cChar=substring(@cNum,@iLen,1)
if @cChar<>'.'
begin
set @dxje= (case @cChar
when '1' then '壹 '
when '2' then '贰 '
when '3' then '叁 '
when '4' then '肆 '
when '5' then '伍 '
when '6' then '陆 '
when '7' then '柒 '
when '8' then '捌 '
when '9' then '玖 '
when '0' then '零 '
when 'X' then 'X '
end)+@dxje
end
set @iLen=@iLen-1
end
if @fs=1 --new
set @dxje='(负)'+@dxje
return @dxje
end
go
--创建函数,用于对金额进行格式化,保留小数位二位.
create function blxsw(@iNum float) returns varchar(60)
as
begin
declare @cNum varchar(60),@mNum money
set @mNum=convert(money,round(@iNum,2),0)
set @cNum=convert(varchar(60),@mNum)
set @cNum=substring(@cNum,1,charindex('.',@cNum)+2)
return @cNum
end
go
--更新之前的发票
update a set a.cdefine10=[dbo].[zdxje]([dbo].[blxsw](b.hj))
from salebillvouch a ,(select sbvid ,convert(float,sum(isum)) as hj from salebillvouchs group by sbvid) b
where a.sbvid=b.sbvid
go
--建触发器
CREATE TRIGGER TTzdxje ON [dbo].[SaleBillVouchs]
FOR INSERT, UPDATE
AS
update a set a.cdefine10=[dbo].[zdxje]([dbo].[blxsw](b.hj))
from salebillvouch a ,(select sbvid ,convert(float,sum(isum)) as hj from salebillvouchs group by sbvid) b
where a.sbvid=b.sbvid and a.sbvid in (select sbvid from inserted)
|