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

不是要思路，是要具体的SQL语句。

大意如此,不記得050624的格式參數是几了,細節你再去優化吧CREATE TRIGGER [TR] ON [dbo].[test]
FOR INSERT
ASbegin
declare @str varchar(12),@I INTEGER
SELECT @I=COUNT(*) FROM TEST WHERE YEAR(GETDATE())=SUBSTRING(TEMPID,1,2) AND MONTH(GETDATE())=SUBSTRING(TEMPID,3,2) AND DAY(GETDATE())=SUBSTRING(TEMPID,5,2)
CREATE TABLE #1 (ROWID INTEGER IDENTITY(1,1) ,TEMPNAME VARCHAR(10))
INSERT #1 SELECT TEMPNAME FROM INSERTED
INSERT TEST SELECT LTRIM(STR(YEAR(GETDATE())))+LTRIM(STR(MONTH(GETDATE())))+LTRIM(STR(DAY(GETDATE())))+LTRIM(STR(REPLICATE('0',4-LEN(LTRIM(RTRIM(STR(ROWID)))))))+LTRIM(STR(ROWID)) ,TEMPNAME FROM #1
end

declare @temp varchar(50)
select @temp=convert(varchar(50),getdate(),12)
这句可以得到050624

謝謝樓上的.問題不大花了我不少時間,可要多給分CREATE TRIGGER [TR] ON dbo.test
FOR INSERT
ASbegin
declare @str varchar(12),@I INTEGER
SELECT @I=COUNT(*) FROM TEST WHERE YEAR(GETDATE())=YEAR(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6))) AND MONTH(GETDATE())=MONTH(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6))) AND DAY(GETDATE())=DAY(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6)))
CREATE TABLE #1 (ROWID INTEGER IDENTITY(1,1) ,TEMPNAME VARCHAR(10))
INSERT #1 SELECT TEMPNAME FROM INSERTED
DELETE A FROM TEST A INNER JOIN INSERTED B ON A.TEMPID=B.TEMPID
INSERT TEST SELECT convert(varchar(10),getdate(),12)+REPLICATE('0',4-LEN(LTRIM(RTRIM(STR(ROWID+ISNULL(@I,0))))))+LTRIM(STR(ROWID+ISNULL(@I,0))) ,TEMPNAME FROM #1end

SELECT @I=COUNT(*) FROM TEST WHERE YEAR(GETDATE())=SUBSTRING(TEMPID,1,2) AND MONTH(GETDATE())=SUBSTRING(TEMPID,3,2) AND DAY(GETDATE())=SUBSTRING(TEMPID,5,2)这一句是什么意思不太明白。

后面又改過的你看一下,是查詢在插入之前已有多少筆當天的資料,再加上現在插入(包括多筆插入)的rowid,才是正確的編號.
五樓的答案還有點不准確,插入tempid是null 時不能去掉原來插入的資料,所以改為:
CREATE TRIGGER [TR] ON dbo.test
FOR INSERT
ASbegin
declare @str varchar(12),@I INTEGER
SELECT @I=COUNT(*) FROM TEST WHERE YEAR(GETDATE())=YEAR(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6))) AND MONTH(GETDATE())=MONTH(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6))) AND DAY(GETDATE())=DAY(CONVERT(DATETIME,SUBSTRING(TEMPID,1,6)))
CREATE TABLE #1 (ROWID INTEGER IDENTITY(1,1) ,TEMPNAME VARCHAR(10))
INSERT #1 SELECT TEMPNAME FROM INSERTED
DELETE A FROM TEST A INNER JOIN INSERTED B ON isnull(A.TEMPID,'')=isnull(B.TEMPID,'')
INSERT TEST SELECT convert(varchar(10),getdate(),12)+REPLICATE('0',4-LEN(LTRIM(RTRIM(STR(ROWID+ISNULL(@I,0))))))+LTRIM(STR(ROWID+ISNULL(@I,0))) ,TEMPNAME FROM #1end

ALTER trigger tr_test on test
for insert
as
declare @today varchar(6)
declare @count int
declare @testid varchar(10)
declare @testname varchar(50)
set @today=(select convert(varchar(6),getdate(),12))
set @count=(select count(testid) from test where left(testid,6)=@today)
set @testname=(select testname from inserted)
if @count=0
begin
set @testid= @today+'0001'
end
else
begin
set @testid=@today+right('0000'+cast(@count+1 as varchar(4)),4)
end
delete from test where testid is null
insert into test(testid,testname)
select @testid,@testname

TO：楼主
按楼主题目的意思，估计TESTID是不能为主键了。用触发器最后插入数据时要考虑一点，
如果表中没有主键，我估计TESTNAME也不是唯一值。
所以在最后用触发器插入数据时，要考虑你先前只是插入了一列TESTNAME=AA，那么对应该的TESTID值是NULL，所以还要考虑对，NULL，AA这样一条记录的处理。
所以最后可以用插入也可以修改。
如果用修改的话上面最后三句可以改成：
UPDATE TEST SET TESTID=@testid WHERE TESTID IS NULL
我这样写主要是考虑到TESTNAME有可能不唯一。
而TESTID 是NULL的应该是唯一的，只要每次触发器都触发的话。

不知道大家考虑过没有如果开始时表里一条记录都没有，也就是说找不到上条记录的rowid怎么处理？

我不知道我说的对不对
其实，问题可以这样解决(我一测试过了，可能性能不好，还请指教：））
首先建一test表
create  trigger insert_trigger
on test
after insert
as
begin
     declare @today datetime
     declare @strYear char(4),@strMonth char(2),@strDay char(2),@strYMD  char(8)
     declare @strTotal char(15)
     declare @i int
     set @today=getDate()
     set @strYear=year(@today)
     set @strMonth=month(@today)
     set @strDay=day(@today)
     set @strTotal=@strYear+'0'+rtrim(@strMonth)+ltrim(@strDay)
     print @strTotal
     select @i=count(*) from test
     print cast(@i as char(100))
     if(@i>=0 and @i<=9)
        begin
           update test set testID= rtrim(@strTotal) + '0000' + ltrim(cast(@i as char(1)))
            where testID is null
        end
     else if(@i>=10 and @i<=99)
         begin
          update test set testID= rtrim(@strTotal) + '000' + ltrim(cast(@i as char(2)))
          where testID is null
        end
     else if(@i>=100 and @i<=999)
          update test set testID= rtrim(@strTotal) + '00' + ltrim(cast(@i as char(3)))
          where testID is null
     else if(@i>=1000 and @i<9999)
          update test set testID= rtrim(@strTotal) + '0' + ltrim(cast(@i as char(4)))
          where testID is null
    else if(@i>=10000 and @i<=99999)
          update test set testID= rtrim(@strTotal)  + ltrim(cast(@i as char(5)))
          where testID is null
end
go

CREATE TRIGGER [TR] ON dbo.test
FOR INSERT after insert
ASbegin
declare @today varchar(6)
declare @count int
declare @testid varchar(10)
declare @testname varchar(50)
set @today=(select convert(varchar(6),getdate(),12))
set @count=(select count(testid) from test where left(testid,6)=@today)
set @testname=(select testname from inserted)
if @count=0
begin
set @testid= @today+'0001'
end
else
begin
set @testid=@today+right('0000'+cast(@count+1 as varchar(4)),4)
endCREATE TABLE #1 (ROWID INTEGER IDENTITY(1,1) ,TEMPNAME VARCHAR(10))
INSERT #1 SELECT TEMPNAME FROM INSERTED
DELETE A FROM TEST A INNER JOIN INSERTED B ON isnull(A.TEMPID,'')=isnull(B.TEMPID,'')
insert into test(testid,testname)
select @testid,@testnameend

insert into test(testid,testname)
select @testid,@testname你一次插入几筆試一下,如 insert into test select '001','aaa' union select '002','bbb',將只能插入一筆,這個問題是很多剛寫觸發器的人容易犯的錯誤

select * from test
declare @count int
select @count = right(testID,5) + 1 from test
select Convert(varchar(6),getdate(),12) + replace(space(5-len(@count)),' ','0') + Convert(varchar(5),@count) from test

回复人： yifan600(一剑飘雪) ( ) 信誉：100  2005-6-24 14:25:28  得分: 0


ALTER   trigger tr_test on test
for insert
as
declare @today varchar(6)
declare @count int
declare @testid varchar(10)
declare @testname varchar(50)
set @today=(select convert(varchar(6),getdate(),12))
set @count=(select count(testid) from test where left(testid,6)=@today)
set @testname=(select testname from inserted)
if @count=0
begin
set @testid= @today+'0001'
end
else
begin
set @testid=@today+right('0000'+cast(@count+1 as varchar(4)),4)
end
delete from test where testid is null
insert into test(testid,testname)
select @testid,@testname经过测试上面的是正确的，不过如果testid设置成主键就不行了。有没有人可以改进的？？测试环境if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GOCREATE TABLE [dbo].[test] (
[testID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[testname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into test (testId,testname) values('122','aa')

上面大家讲的都是不是关键字的时候,这个应该很容易写了,关键是如果TESTID是关键字时,该怎么处理,关注

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

这个触发器经多条数据插入正常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

大家好像都忽略了一个问题，大家都用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 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
)
goinsert into test(testName) values('asfa')
select * from testdelete from testdrop table test

按你的需求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

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

首先做两个假设：
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
如果上面的两个假设不能满足，还得另想办法。

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

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
goTest 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
goCheckpoints:
****************************************************************************************
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.

调试易

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

解决方案 »