批号主档空格控制触发器

这个只能挡住批号主档的进入，如果到时批号文本有内容，但批号内码为0，这个触发器是没效果的

适合版本：云星空 V7.5之前版本，V7.6系统已添加控制

代码来源：金蝶总部研发工程师

create trigger trg_lotErrChar

on T_BD_LOTmaster for insert

as

begin

declare @l as nvarchar(511),@t as nvarchar(511),@i nvarchar(255)

if exists (select 1 from inserted where  fnumber like '% ')

begin

select @l= fnumber from inserted where  fnumber like '% '

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID  where  i.fnumber like '% '

select @t='批号不能在尾部有TAB键(空格)，请清除尾部TAB键(空格)再保存！物料代码：'+ @i + ' 异常批号是： '+ @l

raiserror  (@t ,16,1)

end

if exists (select 1 from inserted where  fnumber like ' %')

begin

select @l= fnumber from inserted where  fnumber like ' %'

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID  where  i.fnumber like ' %'

select @t='批号不能在头部有TAB键(空格)，请清除头部制TAB键(空格)再保存！物料代码：'+ @i + ' 异常批号是：'+ @l

raiserror  (@t ,16,1)

end

if exists (select 1 from inserted where  fnumber like '% ')

begin

select @l= fnumber from inserted where  fnumber like '% '

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID  where  i.fnumber like '% '

select @t='批号不能在尾部有空格，请清除尾部空格再保存！物料代码：'+ @i + ' 异常批号是：'+ @l

raiserror  (@t ,16,1)

end

if exists (select 1 from inserted where  fnumber like ' %')

begin

select @l= fnumber from inserted where  fnumber like  ' %'

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID  where  i.fnumber like ' %'

select @t='批号不能以空格开头，请清除头部空格再保存！物料代码：'+ @i + ' 异常批号是：'+ @l

raiserror  (@t ,16,1)

end

if  exists (SELECT 1 FROM inserted  where  CHARINDEX(char(13),fnumber)>0 )

begin

select @l= fnumber from inserted where  CHARINDEX(char(13),fnumber)>0

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID where  CHARINDEX(char(13),i.fnumber)>0

select  @t='批号不能含有回车符，请检查并清除！物料代码：'+ @i + ' 异常批号是：' + @l

raiserror  (@t,16,1)

end

if  exists (SELECT 1 FROM inserted  where  CHARINDEX(char(10),fnumber)>0)

begin

select @l= fnumber from inserted where  CHARINDEX(char(10),fnumber)>0

select @i= m.fnumber from T_BD_MATERIAL m join inserted i on i.FMATERIALID =m.FMATERIALID  where  CHARINDEX(char(10),i.fnumber)>0

select  @t='批号不能含有换行符，请检查并清除！物料代码：'+ @i + ' 异常批号是：' + @l

raiserror  (@t,16,1)

end

end