这是一个SQL Server 2000的数据库问题,现有一张表--Book(图书表) 表中的字段如下:
bookID(int 自增 步长为1 设为主键) 非空
bookName(书名)char(20)非空
bookISBN(ISBN号) char(20)非空
bookPrice(单价) float 非空
bookAuthor(作者) char(20)非空
publisher(出版社) char(20)非空
publishTime(出版时间) datetime非空
bookAmount(数量) int
具体要求是:当我向这张表中添加一本书,如果bookAmount没有写,那么bookAmount自增1,以保存书的数量!
如果bookAmount写的话,那就保存bookAmount的值
例如:原来表中没有任何书籍,第一次我添加5本《Java2核心技术》,那么bookAmount变为5,
第二次添加《Java2核心技术》,但是这次没有给bookAmount赋值,那么数量就默认为1,
所以bookAmount变为6,即总共有6本《Java2核心技术》不知哪位大侠能够实现这个功能,最好能把运行正确的代码贴出来,小弟非常感谢!
住大家学习工作愉快啊!呵呵……
bookID(int 自增 步长为1 设为主键) 非空
bookName(书名)char(20)非空
bookISBN(ISBN号) char(20)非空
bookPrice(单价) float 非空
bookAuthor(作者) char(20)非空
publisher(出版社) char(20)非空
publishTime(出版时间) datetime非空
bookAmount(数量) int
具体要求是:当我向这张表中添加一本书,如果bookAmount没有写,那么bookAmount自增1,以保存书的数量!
如果bookAmount写的话,那就保存bookAmount的值
例如:原来表中没有任何书籍,第一次我添加5本《Java2核心技术》,那么bookAmount变为5,
第二次添加《Java2核心技术》,但是这次没有给bookAmount赋值,那么数量就默认为1,
所以bookAmount变为6,即总共有6本《Java2核心技术》不知哪位大侠能够实现这个功能,最好能把运行正确的代码贴出来,小弟非常感谢!
住大家学习工作愉快啊!呵呵……
CREATE TRIGGER TRIBOOK ON BOOK FOR INSERT
AS
BEGIN
IF EXISTS(SELECT bookAmount FROM INSERTED WHERE bookAmount IS NULL OR bookAmount='' OR bookAmount=0)
UPDATE BOOK SET bookAmount=1 WHERE BOOKID IN(SELECT BOOKID FROM INSERTED)
END
试试?
-- 如果书名存在, 刚累加
update Book set
bookAmount = bookAmount + 新增数量
where bookName = 'LZ的书名'-- 如果书句不存在, 则插入
if( @@rowcount = 0 )
begin
insert into Book
select 'LZ的书名'.....
end
(bookID int identity(1,1),
bookName char(20),
bookISBN char(20),
bookPrice float,
bookAuthor char(20),
publisher char(20),
publishTime datetime,
bookAmount int)
gocreate trigger tri_book on book instead of insert
as
update book set bookAmount=a.bookAmount+case isnull(b.bookAmount,0) when 0 then 1 else b.bookamount end
from book a,inserted b where a.bookname=b.bookname insert into book select b.bookname,b.bookisbn,b.bookprice,b.bookauthor,b.publisher,b.publishTime,isnull(b.bookAmount,1)
from inserted b where not exists(select 1 from book where bookname=b.bookname)
go
CREATE TRIGGER TRIBOOK ON BOOK FOR INSERT
AS
BEGIN
IF EXISTS(SELECT bookAmount FROM INSERTED WHERE bookAmount IS NULL OR bookAmount='' OR bookAmount=0)
UPDATE BOOK SET bookAmount=1 WHERE BOOKID IN(SELECT BOOKID FROM INSERTED)
END
INSERT INTO Book(bookName,bookISBN,bookPrice,bookAuthor,publisher,publishTime,bookAmount)VALUES('AAAAA','AAAAA',60.0,'AAAAA','AAAAA','2008-8-8',10)
INSERT INTO Book(bookName,bookISBN,bookPrice,bookAuthor,publisher,publishTime,bookAmount)VALUES('AAAAA','AAAAA',60.0,'AAAAA','AAAAA','2008-8-8',8)运行结果为:
6 AAAAA AAAAA 60.0 AAAAA AAAAA 2008-08-08 00:00:00.000 10
7 AAAAA AAAAA 60.0 AAAAA AAAAA 2008-08-08 00:00:00.000 8
一样的书啊,应该是18本啊!可是怎么会这样呢?
运行情况如下:INSERT INTO Book(bookName,bookISBN,bookPrice,bookAuthor,publisher,publishTime)VALUES('BBBBB','BBBBB',80.0,'BBBBB','BBBBB','2007-7-7')
INSERT INTO Book(bookName,bookISBN,bookPrice,bookAuthor,publisher,publishTime,bookAmount)VALUES('BBBBB','BBBBB',80.0,'BBBBB','BBBBB','2007-7-7',8)============10 BBBBB BBBBB 80.0 BBBBB BBBBB 2007-07-07 00:00:00.000 9谢谢啊!