员工自动编号sql insert 触发器解决方法

员工自动编号sql insert 触发器
要求字段MasterNum开头为员工名字首字母，中间几个0加ID,比如插入的一条数据是：23(ID),李连杰(员工名)
那这条数据中的字段MasterNum(自动编号)应为'LLJ000023'

这是我现在写的，怎么取名字的首字母，求助
create trigger T_update_Masters
on Masters
for insert
as
begin

update Masters
set MasterNum = 'A'+right('0000000'+cast(MastersId as varchar(10)),7) where MastersId in (select MastersId from inserted)

end

------解决方案--------------------
参照方法
http://blog.****.net/roy_88/article/details/1424370
------解决方案--------------------
--下面函数用来获取中文名首字母, 从网上找的, 给你参考吧

SQL code


create function getPY(@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

select dbo.GetPY('李连杰')

员工自动编号sql insert 触发器解决方法

相关推荐