lib.sql

此程序配合sql server 2000一起使用.若没装数据库服务器,则无法正常运行本程序. 在sql server的查询分析器里输入程序自带的LIB.sql里的语句.

use master
go

--若存在，则DROP
if exists (select * from dbo.sysdatabases where name = 'LIB') 
drop database LIB
GO

--创建数据库名为LIB
CREATE DATABASE LIB
GO

use LIB
GO

--同上，若存在，则删除
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bookinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Bookinfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lendinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Lendinfo]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Manage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[manage]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO


--创建基本信息
CREATE TABLE BookInfo
( 
	ID varchar(10) ,
	Name varchar(100) ,
	Sort varchar(100),
	Counts int check(counts>=0),--存书数量
	PubName varchar(100) ,
	Author varchar(50) ,
	ISBN varchar(50) ,
	PubDate smalldatetime ,
	Price float(8) ,
	Barcode varchar(50), --条形码的值
	Info varchar(200), --书的信息或简要说明
	primary key(ID)

)
GO
insert into bookinfo values('100001','计算机操作系统','计算机类',10,'清华大学出版社','无名','3940234','2002-2-10',50,'30723095','计算机学习类图书')
insert into bookinfo values('100002','数据结构','计算机类',10,'清华大学出版社','无名','3975774','2008-10-10',30,'30723454','计算机学习类图书')
insert into bookinfo values('100003','c语言','计算机类',10,'清华大学出版社','无名','39353344','2005-2-10',50,'30725875','计算机学习类图书')
GO


CREATE TABLE Users
(
	ID varchar(10)  ,
	Name varchar(20) not null,
	Sex char(2) check(sex in('男','女','F','M')),
	Password varchar(10) ,
	Email varchar(50) ,
	Countable int check(countable<=20 and countable>=0) default 5,--可借书的数量
	Forfeit int check(Forfeit in(0,1)) default 0,
	Primary key(ID)
)
GO
insert into users values('10001','李华','F','123354','[e-mail]',10,0)
insert into users values('10002','孙四','m','123456','[e-mail]',10,0)
GO

CREATE Table Manager
(
	ID varchar(10) ,
	Name varchar(20) not null unique,
	Sex varchar(4),
	Password varchar(10) not null,
	primary key(ID)
)
Go
insert into manager values('10001','root','F','root')

GO

CREATE TABLE LendInfo
(
	bookID varchar(10) NOT NULL ,
	UserID varchar(10) NOT NULL ,
	LendDate smalldatetime not null,
	ReturnDate smalldatetime ,
	IsBack char(1) check(IsBack in('Y','N')),
	primary key(BookID,UserID),
	check(ReturnDate>=LendDate),
	foreign key(bookid)references Bookinfo(ID),
	foreign key(userid) references users(ID)
)
GO

use LIB

GO
CREATE TRIGGER BookidChange
ON BOOKINFO
AFTER UPDATE
AS
IF UPDATE(ID)
BEGIN
DECLARE @book_id as varchar
DECLARE @old_book_id as varchar
SELECT @book_id=(select Id from inserted)
SELECT @old_book_id =(select id from deleted)
Update LENDINFO
SET LENDINFO.bookid=@book_id
WHERE LENDINFO.bookid =@old_book_id
END
GO


CREATE TRIGGER UserIdChanges
ON USERS
AFTER UPDATE
AS
IF UPDATE(id)
BEGIN
DECLARE @user_id as char
DECLARE @old_user_id as char
SELECT @user_id =(select id from inserted)
SELECT @old_user_id =(select id from deleted)
Update LENDINFO
SET LENDINFO.userid =@user_id
WHERE LENDINFO.userid =@old_user_id
END
GO

create trigger setstatue
on lendinfo for insert,update
as
update users set Forfeit=1 where id in (select userID from lendinfo where Isback='N' and datediff(day,lenddate,getdate())>60)
GO

create trigger relendinfo
on lendinfo instead of insert
as
if exists (select * from lendinfo a join inserted b on a.userid=b.userid and a.bookid=b.bookid and a.IsBack='Y')
delete from lendinfo where userid in (select userid from inserted) and bookid in (select bookid from inserted)
insert into lendinfo select * from inserted
GO


create trigger deletebook
on bookinfo instead of delete
as
delete from lendinfo where userid in (select ID from deleted)
delete from bookinfo where id in (select ID from deleted)
GO

create trigger deleteuser
on users instead of delete
as
if exists (select * from lendinfo where userid in(select ID from deleted) and IsBack = 'N')
begin
	print'有末还图书,请还清图书后再注消用户'
end
else
begin 
	delete from lendinfo where userid in (select ID from deleted)
	delete from users where id in(select ID from deleted)
end
go

create trigger usercountable --借书后用户的可错数量减一
on lendinfo after insert
as
update users set countable=countable-1 where ID in (select userid from inserted)
GO


--在master下建立一个存储过程.用来恢复数据库的时候断开所有与要恢复的数据库的连接
use master 
GO
create proc killspid (@dbname varchar(20))        
as        
begin        
declare @sql   nvarchar(500)        
declare @spid  int        
set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'        
exec (@sql)        
open getspid        
fetch next from getspid into @spid        
while @@fetch_status <> -1        
begin        
exec('kill '+@spid)        
fetch next from getspid into @spid        
end        
close getspid        
deallocate getspid        
end
go  
use LIB
go