--创建编号
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
--创建编号
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
view plaincopy to clipboardprint?
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 [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
view plaincopy to clipboardprint?
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
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/myk_666888/archive/2010/07/21/5753124.aspx
联系客服