我设计了两个表ISBN_Book和Book,其中Book表中的书号Book_id是由ISBN_Book表中的ISBN_id加两位编码实现的。例如ISBN_id为: 9787115221063
馆藏数量:Book_count: 12
则Book表中ISBN号为9787115221063的书有12本,其书号分别为:978711522106301,978711522106302,978711522106303....978711522106312我设计了一个触发器,当往ISBN_Book里添加记录时,Book表自动生成记录,但是测试的时候,总是说有溢出,请各位好心人帮帮忙,找出我的问题,不胜感激~~create trigger Insert_book
on ISBN_Book
after insert
as
begin
declare @Iid char(13),@num smallint,@Bid char(15)--,@bid bigint
select @Iid=ISBN_id,@num=Book_count from inserted
--set @bid=convert(bigint,@Iid)*100
set @bid=(cast(@Iid as bigint))*100
while @num>0
begin
set @bid=@bid+1
set @Bid=convert(char(15),@bid)
insert into Book(ISBN_id,Book_id) values(@Iid,@Bid)
set @num=@num-1
end
endinsert into ISBN_Book values('9787115221063','数据库','SQL Server2008实战','JosephSack','人民邮电出版社',2010-2-1,34.00,3,3,'本书实用、高效、技巧性强,适用于专业人员,也适合初学者使用');消息 248,级别 16,状态 1,过程 Insert_book,第 12 行
转换 varchar 值 '978711522106300' 时溢出了整数列。
语句已终止。
馆藏数量:Book_count: 12
则Book表中ISBN号为9787115221063的书有12本,其书号分别为:978711522106301,978711522106302,978711522106303....978711522106312我设计了一个触发器,当往ISBN_Book里添加记录时,Book表自动生成记录,但是测试的时候,总是说有溢出,请各位好心人帮帮忙,找出我的问题,不胜感激~~create trigger Insert_book
on ISBN_Book
after insert
as
begin
declare @Iid char(13),@num smallint,@Bid char(15)--,@bid bigint
select @Iid=ISBN_id,@num=Book_count from inserted
--set @bid=convert(bigint,@Iid)*100
set @bid=(cast(@Iid as bigint))*100
while @num>0
begin
set @bid=@bid+1
set @Bid=convert(char(15),@bid)
insert into Book(ISBN_id,Book_id) values(@Iid,@Bid)
set @num=@num-1
end
endinsert into ISBN_Book values('9787115221063','数据库','SQL Server2008实战','JosephSack','人民邮电出版社',2010-2-1,34.00,3,3,'本书实用、高效、技巧性强,适用于专业人员,也适合初学者使用');消息 248,级别 16,状态 1,过程 Insert_book,第 12 行
转换 varchar 值 '978711522106300' 时溢出了整数列。
语句已终止。
转换 varchar 值 '978711522106300' 时溢出了整数列。
语句已终止。
转换成BIGINT啊
create table ISBN_book(ISBN_id varchar(13),class nvarchar(10),bookname nvarchar(200),Book_count int)
create table book(isbn_id varchar(13),book_id varchar(16)) --建议后加三位甚至更长,如果有上百本相同的书,两位不够
go
create trigger Insert_book
on ISBN_Book
for insert
as
begin
declare @Iid varchar(13),@Num smallint,@bid smallint
select @Iid=ISBN_id,@Num=book_count from inserted
set @bid=1
while @bid<=@Num
begin
--以下按加三位处理
insert into book(isbn_id,book_id)values(@Iid,@Iid+right('00'+ltrim(@bid),3))
set @bid=@bid+1
end
end
GO
insert into ISBN_Book values('9787115221063','数据库','SQL Server2008实战',12) --忽略其他列
select * from book
/*
isbn_id book_id
------------- ----------------
9787115221063 9787115221063001
9787115221063 9787115221063002
9787115221063 9787115221063003
9787115221063 9787115221063004
9787115221063 9787115221063005
9787115221063 9787115221063006
9787115221063 9787115221063007
9787115221063 9787115221063008
9787115221063 9787115221063009
9787115221063 9787115221063010
9787115221063 9787115221063011
9787115221063 9787115221063012(12 行受影响)*/
go
DROP TABLE ISBN_book,book
create table ISBN_book(ISBN_id varchar(13),class nvarchar(10),bookname nvarchar(200),Book_count int)
create table book(isbn_id varchar(13),book_id varchar(16)) --建议后加三位甚至更长,如果有上百本相同的书,两位不够
go
create trigger Insert_book
on ISBN_Book
for insert
as
begin
insert into book
select a.isbn_id,a.isbn_id+right('000'+ltrim(b.number+1),3)
from inserted a inner join master..spt_values b on a.book_count>b.number
where b.type='p'end
GO
insert into ISBN_Book
select * from(
select '9787115221063' a,'数据库' b,'SQL Server2008实战' c,12 d
union all
select '8374848859374','编程','C++编程',18
)t
select * from book
/*
isbn_id book_id
------------- ----------------
8374848859374 8374848859374001
8374848859374 8374848859374002
8374848859374 8374848859374003
8374848859374 8374848859374004
8374848859374 8374848859374005
8374848859374 8374848859374006
8374848859374 8374848859374007
8374848859374 8374848859374008
8374848859374 8374848859374009
8374848859374 8374848859374010
8374848859374 8374848859374011
8374848859374 8374848859374012
8374848859374 8374848859374013
8374848859374 8374848859374014
8374848859374 8374848859374015
8374848859374 8374848859374016
8374848859374 8374848859374017
8374848859374 8374848859374018
9787115221063 9787115221063001
9787115221063 9787115221063002
9787115221063 9787115221063003
9787115221063 9787115221063004
9787115221063 9787115221063005
9787115221063 9787115221063006
9787115221063 9787115221063007
9787115221063 9787115221063008
9787115221063 9787115221063009
9787115221063 9787115221063010
9787115221063 9787115221063011
9787115221063 9787115221063012(30 行受影响)
*/
go
DROP TABLE ISBN_book,book