Solution:
**************************************************************************************
--create table
create table test
(
testID char(11), -- 10——> 11 for len('05062400001')=11
testname varchar(50)
)
select top 10 * from test
go
--create trigger
if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[trigger_testID]'))
drop trigger [dbo].[trigger_testID]
go
create trigger trigger_testID
on test
instead of insert
as
--get maxcount of existed records today, NULL if not founded
declare @lstr varchar(6), @rstr varchar(12), @maxcount int
set @lstr = convert(char(6),getdate(),12)
select @maxcount = convert(int,right(rtrim(max(testID)),5) ) from test where left(testID,6)=@lstr
--create temp table #1 to serialize waiting-insert records
create table #1 (RowID int Identity(1,1), tempname varchar(50) )
insert into #1 select testname from inserted
--delete from inserted
--insert into test instead of db operation
insert into test
select
@lstr
+ replicate('0',5-len(ltrim(rtrim(convert( varchar(5) , isnull(@maxcount,0)+ RowID ) ))) )
+ convert( varchar(5) , isnull(@maxcount,0)+ RowID )
, tempname from #1
--drop temp table
drop table #1
go
Test data:
*****************************************************************************************
--insert an individual record
insert into test select null,'testname1'
select * from test order by testID
go
--insert several records at one time
insert into test
select null,'testname2'
union all select null,'testname3'
select * from test order by testID
go
--insert a record after an existed record deleted
delete from test where testname='testname2'
insert into test select null,'testname4'
select * from test order by testID
go
Checkpoints:
****************************************************************************************
1 Although to insert multi records at one time, only once the trigger executes.
2 If existed records deleted, count(*) != max(testID), and max(testID) is right.
3 choose 'instead of' trigger other than 'for or after'
> testname is not the pk, so we cannot update records after db insert-operation
> It's forbidden to insert,update,delete to logical object(table) inserted and deleted.
4 not neccessary to deal with null valued existed testID records in test
Records with null valued testID are not forbidden to insert into test.
CREATE TRIGGER trgNewRec ON dbo.Table1
FOR INSERT
AS
DECLARE @MaxID char(10),@nID smallint
DECLARE @vMax char(10),@vName varchar(50)
SELECT @MaxID=MAX(testid) FROM table1 WHERE LEFT(testid,6)=CONVERT(char(6),getdate(),12)
IF @MaxID IS NULL
BEGIN
SET @nID=1
DECLARE one CURSOR FORWARD_ONLY FOR SELECT * FROM inserted
OPEN one
FETCH NEXT FROM one INTO @vMax,@vName
WHILE @@FETCH_STATUS=0
BEGIN
SET @MaxID=CONVERT(char(6),getdate(),12)+REPLACE(STR(@nID,4),' ','0')
INSERT table1 VALUES (@MaxID,@vName)
SET @nID=@nID+1
FETCH NEXT FROM one INTO @vMax,@vName
END
CLOSE one
DEALLOCATE one
END
ELSE
BEGIN
SET @nID=CONVERT(smallint,RIGHT(@MaxID,4))+1
DECLARE two CURSOR FORWARD_ONLY FOR SELECT * FROM inserted
OPEN two
FETCH NEXT FROM two INTO @vMax,@vName
WHILE @@FETCH_STATUS=0
BEGIN
SET @MaxID=LEFT(@MaxID,6)+REPLACE(STR(@nID,4),' ','0')
INSERT table1 VALUES (@MaxID,@vName)
SET @nID=@nID+1
FETCH NEXT FROM two INTO @vMax,@vName
END
CLOSE two
DEALLOCATE two
END
DELETE FROM table1 WHERE testid IS NULL
如果日期后面的序号累计的话请用一下语句
create trigger tr_insert on test
instead of insert
as
declare @dt varchar(10),@dstr varchar(12) , @a int
set @dt= convert(varchar(10),getdate(),12)
select * into #tb from inserted
select @a = 1000001+isnull(max(cast(right(a.testID,5) as int)),0)
from test a
set @dstr = @dt+right(@a,5)
update #tb set testID= @dstr
insert into test select * from #tb
create trigger tr_insert on test
instead of insert
as
declare @dt varchar(10),@dstr varchar(12) , @a int
set @dt= convert(varchar(10),getdate(),12)
select * into #tb from inserted
select @a = 1000001+isnull(max(cast(right(a.testID,5) as int)),0)
from test a
where testID Like Ltrim(Rtrim(@dt)) + '%'
set @dstr = @dt+right(@a,5)
update #tb set testID= @dstr
insert into test select * from #tb
create trigger tr_test_insert on test
after insert
as
declare @leftstr char(6),@insertstr char(10),@rigthstr char(4)
declare @ID char(4)
set @ID='0001'
select @leftstr=convert(varchar(10),getdate(),12)
select @rigthstr=right(max(right(testID,4))+10001,4) from test
where left(testID,6)=@leftstr
大家好像都忽略了一个问题,大家都用SELECT COUNT(*)计算他的ID,但是会有一个问题。
如果050801一共有10条,那就是说现在的ID号已经是0508010010了,但如果你现在删除2条,
那么就是说只剩8条了。现在要插入一条,你想想会得到什么样的结果。所以说取ID不能用这种算法。
SELECT TOP 1 testID FROM test WHERE LEFT(testID,6)=CONVERT(VARCHAR(10),GETDATE(),12) ORDER BY testID DESC
OR
SELECT MAX(testID)FROM test WHERE LEFT(testID,6)=CONVERT(VARCHAR(10),GETDATE(),12)
create trigger ly on test for insert as
declare @a varchar(10) declare @b varchar(10) set @b='01'
if exists(select * from test where testID='20'+convert(varchar(10),getdate(),12)+'01')
select @a=max(testID)+1 from test where charindex('20'+convert(varchar(10),getdate(),12),testID)<>0
else begin
select @a='20'+convert(varchar(10),getdate(),12)+@b
end
update hgz_dj set dj01=@a from test a ,inserted b where a.testID=b.testID