sql server触发器自动生成编号

--创建编号  
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