以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  请LXL老师检查一下代码。  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=5206)
--  作者：菜鸟foxtable
--  发布时间：2009/11/29 21:30:00
--  请LXL老师检查一下代码。

请教LXL老师，下述触发器代码是否有误？特别是红色部分。


CREATE trigger 住院信息_设置拼音码 ON 住院信息

FOR INSERT, UPDATE

AS

  UPDATE 住院信息

  SET 拼音码=dbo.GetPinyinInitials(a.姓名, 10)

  FROM 住院信息 a JOIN inserted b ON a.序号=b.序号 AND a.姓名=b.姓名
--  作者：lxl
--  发布时间：2009/11/29 22:05:00
--  

语法没错。红字是自定义函数吧，必须看代码才知道对不对。
如果序号是主键的话
a.序号=b.序号 就够了
没必要a.姓名=b.姓名
--  作者：菜鸟foxtable
--  发布时间：2009/11/29 23:41:00
--  
谢谢。图片点击可在新窗口打开查看请看看有没有错？这是红色部分的函数

 

create function GetPinyinInitials (@string varchar(32), @max_length smallint)
returns varchar(16)
as
begin

    declare @result varchar(16),
            @char varchar(2),
            @letter char(1),
            @length smallint,
            @ix smallint
            
    set @length=len(@string)
    if @length>@max_length
        set @length=@max_length  
    set @ix=1
    set @result=\'\'
    while @ix<=@length
    begin
        set @char=substring(@string, @ix, 1)
        set @letter=null
        select @letter=substring(读音_1, 1, 1) from 汉字读音表 where 汉字=@char
        if @letter is not null
          set @result=@result+@letter
        set @ix=@ix+1
    end
    return(@result)
end

 

[此贴子已经被作者于2009-11-30 0:13:12编辑过]
--  作者：菜鸟foxtable
--  发布时间：2009/11/29 23:48:00
--  
下面是在网上找的SQL自定义函数：由汉字生成拼音首字母
 

 


/****** Object: UserDefinedFunction [dbo].[fun_getPY_jianpin]    Script Date: 10/21/2009 13:50:30 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N\'[dbo].[fun_getPY_jianpin]\') AND xtype in (N\'FN\', N\'IF\', N\'TF\'))
DROP FUNCTION [dbo].[fun_getPY_jianpin]
GO

/****** Object: UserDefinedFunction [dbo].[fun_getPY_jianpin]    Script Date: 10/21/2009 13:50:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create function [dbo].[fun_getPY_jianpin](@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=\'\'
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select \'A\' as PY,N\'驁\' as word
union all select \'B\',N\'簿\'
union all select \'C\',N\'錯\'
union all select \'D\',N\'鵽\'
union all select \'E\',N\'樲\'
union all select \'F\',N\'鰒\'
union all select \'G\',N\'腂\'
union all select \'H\',N\'夻\'
union all select \'J\',N\'攈\'
union all select \'K\',N\'穒\'
union all select \'L\',N\'鱳\'
union all select \'M\',N\'旀\'
union all select \'N\',N\'桛\'
union all select \'O\',N\'漚\'
union all select \'P\',N\'曝\'
union all select \'Q\',N\'囕\'
union all select \'R\',N\'鶸\'
union all select \'S\',N\'蜶\'
union all select \'T\',N\'籜\'
union all select \'W\',N\'鶩\'
union all select \'X\',N\'鑂\'
union all select \'Y\',N\'韻\'
union all select \'Z\',N\'咗\'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

GO