--创建编号 alter TRIGGER syttyzNumberCreate ON syttyz after INSERT AS DECLARE @id nvarchar(100) DECLARE @now nvarchar(100) DECLARE @syttyzSizeTemp int DECLARE @syttyzSizeStr nvarchar(100) DECLARE @syttyzSize nvarchar(50) declare @lastInsertTime nvarchar(100) declare @year nvarchar(100) SELECT @id=inserted.id,@now=year(insert_time) from inserted --如果数据库中还不存在记录 if not exists (select * from syttyz where id<>@id) begin select @syttyzSizeTemp = 1 select @syttyzSize = @now+'_'+cast(1 as nvarchar(100)) select @year = @now; end else begin --查出新增记录以前最后的insert_time select @lastInsertTime=year(max(insert_time)),@syttyzSize=max(syttyz_size) from syttyz where id <> @id if (@now>@lastInsertTime)--新的一年开始,编号从1开始 begin select @syttyzSizeTemp = 1 select @syttyzSize = @now+'_'+cast(1 as nvarchar(100)) select @year = @now; end else--不是新的一年,在原来编号的基础上加1 begin select @year= substring(@syttyzSize,0,charindex('_',@syttyzSize)); select @syttyzSizeTemp = cast(substring(@syttyzSize,charindex('_',@syttyzSize)+1,len(@syttyzSize)) as int)+1; select @syttyzSize = @year+'_'+cast(@syttyzSizeTemp as nvarchar(100)) end end select @syttyzSizeStr=cast(@syttyzSizeTemp as nvarchar(100)) --把数字凑够5位 while(len(@syttyzSizeStr)<5) begin select @syttyzSizeStr='0'+@syttyzSizeStr; end --设置编号 update syttyz set sytt_yz_number = sytt_yz_number+'['+@year+']'+'第 '+@syttyzSizeStr+' 号',syttyz_size=@syttyzSize where id = @id set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go --创建水域滩涂养殖编号 ALTER TRIGGER [syttyzNumberUpdate] ON [dbo].[syttyz] after UPDATE AS DECLARE @id nvarchar(100) DECLARE @now nvarchar(100) DECLARE @syttyzSizeStr nvarchar(100) DECLARE @syttyzSize int declare @lastInsertTime nvarchar(100) declare @year nvarchar(100) declare @isdraftinsert int declare @isdraftdel int declare @count1 int declare @count2 int declare @districtCode nvarchar(20) SELECT @isdraftdel=is_draft from deleted SELECT @id=inserted.id,@now=year(insert_time),@isdraftinsert=is_draft,@districtCode=district_code from inserted --如果是草稿页面 并且点击提交按钮 才生成编号 --修改页面不重新生成编号 if(@isdraftdel=1 and @isdraftinsert=0) begin --如果数据库中还不存在记录 select @count1=count(*) from syttyz; if(@count1=0) begin select @syttyzSize = 1; end -- if not exists (select * from syttyz where id<>@id) -- begin -- select @syttyzSize = 1; -- end else begin --如果该地区还没有记录,就从1开始 select @count2=count(*) from syttyz where id <> @id and district_code = @districtCode and is_draft <> 1 if(@count2=0) begin select @syttyzSize = 1; end else begin --查出新增记录以前最后的insert_time select @lastInsertTime=year(max(insert_time)),@syttyzSize=max(syttyz_size) from syttyz where id <> @id and district_code = @districtCode and is_draft <> 1 if (@now>@lastInsertTime)--新的一年开始,编号从1开始 begin select @syttyzSize = 1; end else--不是新的一年,在原来编号的基础上加1 begin select @syttyzSize=@syttyzSize+1; end end end select @syttyzSizeStr = cast(@syttyzSize as nvarchar(100)) --把数字凑够5位 while(len(@syttyzSizeStr)<5) begin select @syttyzSizeStr='0'+@syttyzSizeStr; end --设置养殖编号 update syttyz set sytt_dj_number=@now+@syttyzSizeStr,sytt_yz_number = sytt_yz_number+'['+@now+']'+'第 '+@syttyzSizeStr+' 号',syttyz_size=@syttyzSize where id = @id end set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go --创建水域滩涂养殖编号 ALTER TRIGGER [syttyzNumberInsert] ON [dbo].[syttyz] after insert AS DECLARE @id nvarchar(100) DECLARE @now nvarchar(100) DECLARE @syttyzSizeStr nvarchar(100) DECLARE @syttyzSize int declare @lastInsertTime nvarchar(100) declare @year nvarchar(100) declare @isdraftinsert int declare @isdraftdel int declare @count1 int declare @count2 int declare @districtCode nvarchar(20) SELECT @id=inserted.id,@now=year(insert_time),@isdraftinsert=is_draft,@districtCode=district_code from inserted --申请页面点提交按钮才生成编号,点存草稿按钮不生成编号 if(@isdraftinsert=0) begin --如果数据库中还不存在记录 if(@count1=0) begin select @syttyzSize = 1; end else begin --如果该地区还没有记录,就从1开始 select @count2=count(*) from syttyz where id <> @id and district_code=@districtCode and is_draft <> 1 if(@count2=0) begin select @syttyzSize = 1; end else begin --查出新增记录以前最后的insert_time select @lastInsertTime=year(max(insert_time)),@syttyzSize=max(syttyz_size) from syttyz where id <> @id and district_code=@districtCode and is_draft <> 1 if (@now>@lastInsertTime)--新的一年开始,编号从1开始 begin select @syttyzSize = 1; end else--不是新的一年,在原来编号的基础上加1 begin select @syttyzSize=@syttyzSize+1; end end end select @syttyzSizeStr = cast(@syttyzSize as nvarchar(100)) --把数字凑够5位 while(len(@syttyzSizeStr)<5) begin select @syttyzSizeStr='0'+@syttyzSizeStr; end --设置养殖编号 update syttyz set sytt_dj_number=@now+@syttyzSizeStr,sytt_yz_number = sytt_yz_number+'['+@now+']'+'第 '+@syttyzSizeStr+' 号',syttyz_size=@syttyzSize where id = @id end