一道面试题，希望高手能解答

[54989875]

有一个表名字为test 两个字段testID 类型char（10） 和 testname 类型 varchar（50）

要求写一个触发器在插入新的数据之前自动为testID赋值

规则如 当前的日期为2005年6月24日则testID为 05062400001 第二条记录就为05062400002，第二条记录就为05062400003……

前6位字符随日期变动，后五位为增长的。

请高手指教？

这个触发器如何写？

[linmin200]

问题有个矛盾，当对表进行insert , update , delete操作时应该不能进行select操作，所以不能select出上一次的id是多少!!

[pisces007]

呵呵，不可能是主键吧
如果要成为主键需要用函数

[54989875]

没有人会吗？应该是主键

[Ncaidexiaoniao]

UP

[zhuheyang]

这个触发器经多条数据插入正常

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

[brother2605]

大家好像都忽略了一个问题，大家都用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)

[phantomMan]

create table test(
testID char(10),
testName varchar(50)
)

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 @insertstr=@leftstr+isnull(@rigthstr,'0001')

update test set testID=@insertstr
where testID is null and testName=(
select top 1 testName from inserted
)
go

insert into test(testName) values('asfa')
select * from test

delete from test

drop table test

[54989875]

up

[huohw]

up 一下，希望更多高手看看，讨论一下

[gis1984]

日期后面的变化是日期一变又从1开始增加还是在以前记录的数目上增加啊？不是很清楚

[ywqxtx]

gz呀

[54989875]

up
日期一变又从1开始计数

[jzdmyjzd]

按你的需求testID 超过了10个字符,我将它改为12个字符
create table test(
testID char(12),
testName varchar(50)
)

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

[jzdmyjzd]

如果日期后面的序号累计的话请用一下语句
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

[XLYT]

首先做两个假设：
1.testid不能插入指定值；
2.testid允许NULL。

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


如果上面的两个假设不能满足，还得另想办法。

[Ozzman]

我讲方法，不是给你代码，不用给分，重在交流
我想考你的人是想知道你怎么处理这个唯一值testID
在证券交易系统中是不用IDENTITY，而是用一个小表来控制唯一值，其中的关键是要用到一个更新锁（updlock），这个是方法是当时微软的人告诉我的，当然这个历史有点久远了，不知道现在是否有更好的方法

[54989875]

up

[XLYT]

为什么要触发器？把一个函数放到列的默认值里面，很简单就搞定了。出这个题好象没什么意义。

[nosame_in_21th]

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.

[54989875]

好像解决不了。