sql server 2000触发器：根据类型自动生成编码
/*
sql server 2000触发器：根据类型自动生成编码！
此触发器的作用是根据类型字段的值自动生成编码。
编码的生成的规则是：根据类型找出编码中该类型的最大值。然后在该最大的编码上加1，作为当前插入的编码值。
只要修改一下表名和表的两个字段，就可以实现通用。
type:sql server 2000 trigger
author:kfy0228@163.com
createDate: 2008-05-25
*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--根据插入的queryType值只自动生成queryCode的值。
ALTER trigger tri_querySave_add
on dbo.t_comm_querySaveTable
for insert
as
declare @queryType varchar(8)
declare @queryCode varchar(12)
declare @id int
select @id=id,@queryType=queryType,@queryCode=queryCode from inserted
DECLARE tree_cursor CURSOR
FOR select queryCode from t_comm_querySaveTable where queryType=@queryType and queryCode is not null order by
queryCode
OPEN tree_cursor
declare @code varchar(12)
declare @i bigint
declare @query varchar(12)
set @query=@queryType+'0001'
update t_comm_querySaveTable set queryCode=@query where id=@id
FETCH NEXT FROM tree_cursor into @code
set @i=cast(@code as bigint)
WHILE @@FETCH_STATUS = 0
BEGIN
if(@i<cast(@code as bigint))
begin
update t_comm_querySaveTable set queryCode=right('0000000000'+cast(@i as varchar(12)),len(@code)) where id=@id
break
end
else
begin
update t_comm_querySaveTable set queryCode=right('0000000000'+cast(cast(@code as bigint)+1 as varchar(12)),len
(@code)) where id=@id
end
set @i=@i+1
FETCH NEXT FROM tree_cursor INTO @code
END
CLOSE tree_cursor
DEALLOCATE tree_cursor
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
