建表的时候 create table temp( id int identity(1,1) primary key not null, ....... ) id是自增列不会重复的,新增的时候id就不用添加了
触发器,生成唯一的值USE [AprilShop] GO /****** Object: Trigger [dbo].[trigger_AutoSerialNumber] Script Date: 12/06/2011 16:45:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER trigger [dbo].[trigger_AutoSerialNumber] on [dbo].[Orders] after insert AS declare @OrderId int declare @OrderNo varchar(20) declare @strDate varchar(20) declare @Numlen int declare @num int declare @strNum varchar(20) --获取当前日期 set @strDate=substring(convert(varchar(10),getdate(),112),1,8) set @Numlen = 4
select @OrderNo=max(OrderNo) from Orders where OrderNo like @strDate + '%'
if @OrderNo is null set @num=0 else set @num=cast(replace(@OrderNo,@strDate ,'') as int)
set @num = @num + 1 set @strNum = cast(@num as varchar(10))
while(len(@strNum)<@Numlen) set @strNum = '0' + @strNum set @OrderNo=@strDate + @strNum
select @OrderId=OrderID from inserted
update Orders set OrderNo=@OrderNo where OrderID=@OrderId 可发参考下
create table temp(
id int identity(1,1) primary key not null,
.......
)
id是自增列不会重复的,新增的时候id就不用添加了
GO
/****** Object: Trigger [dbo].[trigger_AutoSerialNumber] Script Date: 12/06/2011 16:45:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER trigger [dbo].[trigger_AutoSerialNumber]
on [dbo].[Orders]
after insert
AS
declare @OrderId int
declare @OrderNo varchar(20)
declare @strDate varchar(20)
declare @Numlen int
declare @num int
declare @strNum varchar(20)
--获取当前日期
set @strDate=substring(convert(varchar(10),getdate(),112),1,8)
set @Numlen = 4
select @OrderNo=max(OrderNo)
from Orders
where OrderNo like @strDate + '%'
if @OrderNo is null
set @num=0
else
set @num=cast(replace(@OrderNo,@strDate ,'') as int)
set @num = @num + 1
set @strNum = cast(@num as varchar(10))
while(len(@strNum)<@Numlen)
set @strNum = '0' + @strNum
set @OrderNo=@strDate + @strNum
select @OrderId=OrderID from inserted
update Orders set OrderNo=@OrderNo where OrderID=@OrderId
可发参考下