k3:增加触发器，当外协单和报料单新增时，更新生产任务单的“说明”栏

外协单新增时

CREATE TRIGGER [dbo].[t_BOS257800018Entry2_update]ON  [dbo].[t_BOS257800018Entry2]AFTER insert
AS 
BEGINSET NOCOUNT ON;
------实现当外协时，生产任务单的说明有标识（240731 BY WK）
declare @fid_souce as int;
declare @xm as varchar(255);--获取源单内码
select @fid_souce=fid_src from inserted--判断当源单内码不为空时执行语句if @fid_souce<>0 or @fid_souce<>''  begin--获取生产任务单的“说明”栏select @xm=tt.fheadselfj01108 from icmo tt where tt.FInterID=@fid_souce--实现当“说明”有内容时，增加"分号+日期+外协工序"，没内容时加"日期+外协工序 " if len(@xm)>0 beginupdate  tt set tt.fheadselfj01108=@xm+';'+right('00'+cast(month(isnull(t1.fdate2,t2.FDate)) as varchar(2)),2)+right('00'+cast(day(isnull(t1.fdate2,t2.FDate)) as varchar(2)),2) +'WX'+t3.FNamefrom   icmo ttinner join t_BOS257800018Entry2 T1 on t1.fid_src=tt.finteridinner join t_BOS257800018 t2 on t2.FID=t1.FIDINNER JOIN  t_SubMessage t3 on t1.FBase5=t3.FInterIDendelsebeginupdate  tt set tt.fheadselfj01108=right('00'+cast(month(isnull(t1.fdate2,t2.FDate)) as varchar(2)),2)+right('00'+cast(day(isnull(t1.fdate2,t2.FDate)) as varchar(2)),2) +'WX'+t3.FName from   icmo ttinner join t_BOS257800018Entry2 T1 on t1.fid_src=tt.finteridinner join t_BOS257800018 t2 on t2.FID=t1.FIDINNER JOIN  t_SubMessage t3 on t1.FBase5=t3.FInterIDend  end   end 

原料报料单新增时

create TRIGGER [dbo].[t_BOS257800035Entry2_insert]ON  [dbo].[t_BOS257800035Entry2]AFTER INSERT
AS 
BEGINSET NOCOUNT ON;
------实现当报料时，生产任务单的说明有标识（240731 BY WK）
declare @fid_souce as int;
declare @xm as varchar(255);
--获取源单内码
select @fid_souce=fid_src from inserted--判断当源单内码不为空时执行语句if @fid_souce<>0 or @fid_souce<>''  begin--获取生产任务单的“说明”栏select @xm=tt.fheadselfj01108 from icmo tt where tt.FInterID=@fid_souce--实现当“说明”有内容时，增加"分号+日期+供应商"，没内容时加"日期+供应商 " if len(@xm)>0 beginupdate tt set tt.fheadselfj01108=@xm+';'+right('00'+cast(month(t2.FDate) as varchar(2)),2)+right('00'+cast(day(t2.FDate) as varchar(2)),2) +'（'+case whent2.FSupplier=27540 then 'JN报料'when t2.FSupplier=27571 then 'HQ报料'else '其他报料'end +')',tt.fheadselfj01106=84047  --外协方式改为“报料”from icmo tt inner join inserted t1 on t1.FID_SRC=tt.FInterIDinner join t_BOS257800035 t2 on  t2.FID=t1.FID;endelsebeginupdate tt set tt.fheadselfj01108= right('00'+cast(month(t2.FDate) as varchar(2)),2)+right('00'+cast(day(t2.FDate) as varchar(2)),2) +'（'+case whent2.FSupplier=27540 then 'JN报料'when t2.FSupplier=27571 then 'HQ报料'else '其他报料'end +')',tt.fheadselfj01106=84047 --外协方式改为“报料”from icmo tt inner join inserted t1 on t1.FID_SRC=tt.FInterIDinner join t_BOS257800035 t2 on  t2.FID=t1.FIDend  end   end 

重点是其中SQLSERVER对于时间只取月份和日期并都设置成两位数的处理