使用触发器实现编码

在做项目的时候，经常需要给某个东西编码，譬如说合同编码、项目编码，这个编码得唯一、得有意义。

以下的几个，是在insert或者update的时候，使用触发器根据日期自动编码。

测试环境：

数据库：MS SQL Server 2005

1、实现效果：

编码格式：’FK‘+填表日期的年份+当前是第几个，譬如：FK2014001。

SQL：

CREATE TRIGGER [CreateYJCode]
   ON  [dbo].[table25]
   AFTER INSERT
AS 
BEGIN
	declare @field1 int --联络单序号
	declare @field15 datetime --填写日期
	declare @code varchar(50)--编码
	declare @count int --末三位数据
	declare @countvar varchar(50)
	declare @result varchar(50)
	select @field1=field1,@field15=field15 from INSERTED;--获取当前联络单序号
	set @code='FK'+cast(year(@field15) as varchar(5));--拼接CODE，目前的形式是：FK2014
	--获取符合条件的编码末三位的最大值，因为总长度是9位，前6位是FK2014，所以要从7开始截取，截取到最后
        select @count=max(cast(substring(field2,7,len(field2)) as int)) from table25 where field2 is not null and year(field15)=year(@field15);
        --当第一次生成编码的时候，@count就可能是空，这样的话，就从0开始
	if(@count = '' or @count is null) begin set @count = 0 end
        --@countvar接收@count加1后的值，来实现递增
	set @countvar = cast(@count+1 as varchar(5))
        --这个编码默认是后面带三位数字的，所以当@countvar只有1位的时候，需要补两个0
	if (len(@countvar)=1) begin set @result ='00' end
	if (len(@countvar)=2) begin set @result ='0' end
	if (len(@countvar)=3) begin set @result ='' end
        --将三个关键数据拼接起来：FK2014+00+1（假设是2014年第一次生成）
	set @code = @code+@result +@countvar;
        --更新相应的字段
	update table25 set field2=@code where field1=@field1;
END

简单解释一下：这是给反馈意见联络单做的一个编码，编码格式是：’FK‘+填表日期的年份+当前是第几个，譬如：FK2014001。其中Field2就是那个编码字段。

2、实现效果：

编码格式：年+月+当前是第几个（三位），譬如：201501001

SQL如下：

CREATE TRIGGER [CreateInComeCode]
   ON  [dbo].[table2]
   AFTER INSERT,UPDATE
AS 
BEGIN
	declare @field1 int --序号
	declare @field16 datetime --日期
	declare @code varchar(50)--编码
	declare @count int --末两位数据
	declare @countvar varchar(50)
	declare @result varchar(50)
	
	if not exists(select * from DELETED)--新增
	begin
		select @field1=field1,@field16=field16 from INSERTED;--获取刚刚添加的序号
	end
	--修改
	else  if(select count(*) from DELETED)>0 and (select count(*) from INSERTED)>0
	begin
		if update(field16)
		begin
			declare @f16_old datetime;--旧的时间
			declare @f16_new datetime;--新的时间
			select @f16_old = field16 from DELETED;--从DELETED里面获取老的时间
			select @f16_new = field16 from INSERTED;--从INSERTED里面获取新的时间
			if @f16_old<>@f16_new --两个时间不相同
			begin
				select @field1=field1,@field16=field16 from INSERTED;--获取刚刚添加的序号
			end
			else --否则不需要修改
				return ;
		end
	end
	--别的什么，一律不处理
	else
		return ;

	--进行处理
	declare @month varchar(10);--月份字符串
	set @month = cast(month(@field16) as varchar(3));
	if month(@field16)<10  set @month = '0'+@month;--要是只有一位，则需要补零
	set @code=cast(year(@field16) as varchar(5))+@month;--获取年份、月份
	
	--获取当天当前编号的最大数(不能使用count(),因为存在删除的情况，一旦删除编号就有可能相同)
	select @count=isnull(max(cast(substring(field19,7,len(field19)) as int)),0) from table2 
	where 
	field1<>@field1 --不是当前记录（这个很关键，一定要加上去，不然计数时永远都会多1，多了当前记录）
	AND LEN(field19)>0 --编号存在
	AND DATEDIFF(MONTH,field16,@field16)=0;--同一个月
	
	--在原来的基础上加1
	set @countvar = cast(@count+1 as varchar(5))
	
        --因为尾数为3位，所以当@countvar只有1、2位时，需要补零
	if (len(@countvar)=1) begin set @result ='00' end
	if (len(@countvar)=2) begin set @result ='0' end
	if (len(@countvar)=3) begin set @result ='' end
	--拼接最后的编码
        set @code = @code+@result+@countvar;
	
	--更新编码
	update table2 set field19=@code where field1=@field1;

END

 

3、实现效果：

编码格式：年+月+日+当前第几个(两位)，譬如：2015031701。

SQL如下：

CREATE TRIGGER [CreateCode]
   ON  [dbo].[table1]
   AFTER INSERT,UPDATE
AS 
BEGIN
declare @field1 int --序号
declare @field9 datetime --日期
declare @code varchar(50)--编码
declare @count int --末两位数据
declare @countvar varchar(50)
declare @result varchar(50)
	
if not exists(select * from DELETED)--新增
begin
	select @field1=field1,@field9=field9 from INSERTED;--获取刚刚添加的基本信息
end
--修改
else  if(select count(*) from DELETED)>0 and (select count(*) from INSERTED)>0
begin
	if update(field9)--如果修改了日期
	begin
		declare @f9_old datetime;--旧的时间
		declare @f9_new datetime;--新的时间
		select @f9_old = field9 from DELETED;--从DELETED里面获取老的时间
		select @f9_new = field9 from INSERTED;--从INSERTED里面获取新的时间
		if @f9_old<>@f9_new --两个时间不相同
		begin
                     --从INSERTED表中获取新的基本信息 
                      select @field1=field1,@field9=field9 from INSERTED;
		end
		else --时间没有发生，不需要修改
			return ;
	end
end
--别的什么，一律不处理
else
    return ;

--进行处理
declare @month varchar(10);--月份字符串
declare @day varchar(10);--日 字符串
set @month = cast(month(@field9) as varchar(3));--获取月份
set @day = cast(day(@field9) as varchar(3));--获取日
if month(@field9)<10  set @month = '0'+@month;--要是只有一位，则需要补零
if day(@field9)<10 set @day = '0'+@day;
set @code=cast(year(@field9) as varchar(5))+@month+@day;--拼接年份、月份、天
	
--获取当天当前编号的最大数(不能使用count(),因为存在删除的情况，一旦删除编号就有可能相同)
select @count=isnull(max(cast(substring(field2,9,len(field2)) as int)),0) from table1 
where 
field1<>@field1 --不是当前记录（这个很关键，一定要加上去，不然计数时永远都会多1，多了当前记录）
AND LEN(field2)>0 --编号存在
AND DATEDIFF(DAY,field9,@field9)=0;--同一天
--在原来编号的基础上加1
set @countvar = cast(@count+1 as varchar(5))
	
--因为只有两位，所以只需要补一个零
if (len(@countvar)=1) begin set @result ='0' end
if (len(@countvar)=2) begin set @result ='' end
set @code = @code+@result+@countvar;--拼接最后的编码
	
--更新编号
update table1 set field2=@code where field1=@field1;

END