sql.sql

用Jsp实现的图书管理系统代码

create table [user]
(
	id int identity(1,1),
	name varchar(20) not null,
	sex char(2) not null,
	age int,
	department varchar(20),
	borrowID varchar(20) not null,
	primary key(borrowID)
)
go
create table book
(
	id int identity(1,1),
	title varchar(100) not null,
	bookID varchar(20) not null,
	type varchar(20) not null,
	quantity int,
	primary key(bookID)
)
go

create table borrowBook
(
	id int identity(1,1),
	borrowID varchar(20) not null,
	beginTime datetime not null,
	endTime as (beginTime+30),
	bookID varchar(20) not null,
	foreign key(borrowID) references [user](borrowID),
	foreign key(bookId) references book(bookid)	
)
s
CREATE trigger borrow
on borrowBook
for insert
as
begin
	if (select max(quantity) from book where bookid=(select top 1 bookid from inserted))>0
	begin
		update book set quantity=quantity-1 where bookID=(select top 1 bookid from inserted)
	end
	else
		rollback
end