* *
name=Book_Log,
filename='g:\\SQL\\图书销售管理系统.ldf', size=5, maxsize=25, filegrowth=5)
use 图书销售管理系统
create table Bookadd ( )
create table Booksale
bookno char(12) primary key not null, bookname varchar(20), bookAuhtor varchar(20), CategoryID int not null, bookPrice numeric(7,2), booksum int,
bPublisher varchar(40), stockNumber int
* *
( )
create table Bookuser ( )
create table CategoryInfo ( )
CategoryID int not null, CategoryName varchar(20) username varchar(20) not null, password varchar(20), Quanxian varchar(20)
saleno char(12) primary key not null, bookno char(12) not null, bookname varchar(20), saleNumber int, salePrice numeric(7,2), saleDate datetime
go
create view 图书查询 as
bookno,bookname,bookAuhtor,CategoryID,bookPrice,bPublisher
from Bookadd
go go
create view 分类查询
as select BookName,CategoryName from Bookadd,CategoryInfo
where Bookadd.CategoryID =CategoryInfo.CategoryID
go go
create view 库存查询
as select bookno,bookname,stockNumber
from Bookadd
go
* *
select
* *
go
create view 销售查询
as select saleno,bookno,bookname,saleNumber,salePrice
from Booksale
go
select saleDate as 销售日期,bookname as 书名,sum(saleNumber) as 月销售数量
into salesum from Booksale group by bookno
go
create trigger insert_bookadd on Bookadd after insert as
if exists
( select * from inserted where bookno in (select bookname from
Bookadd)
)
* *
print '添加成功！' else begin
print '图书信息表中没有该图书的信息。拒绝插入！' rollback transaction
end
go
drop trigger insert_bookadd
insert into Bookadd values(7,'c#','fff',7,35,200,'FFF',100) go
create trigger del_book on Bookadd after delete as
delete from Booksale where Booksale.bookno in (select bookno from deleted)
go
drop trigger del_book
图书销售管理组织系统
