Gprice float not null, Gshelf date)
create table Employer(
Empnumber varchar(10) primary key, Empname varchar(10), Empkind varchar(10))
create table Hourse(
Hounumber varchar(10) primary key, Houkind varchar(10), Houaddr varchar(20))
create table Customer(
Cusnumber varchar(10) primary key, Cusname varchar(10) not null,
Cussex varchar(2) check(Cussex in('男', '女')), Cusphone varchar(20))
create table Buylist(
Buynumber varchar(10) primary key, Supnumber varchar(10), Gname varchar(30),
Empnumber varchar(10), Buyliang int not null, Buydate date,
foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Sendlist(
Sendnumber varchar(10) primary key, Supnumber varchar(10), Gname varchar(30),
Empnumber varchar(10), Sendliang int not null, Senddate date,
foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Sbacklist(
Sbacknumber varchar(10) primary key, Supnumber varchar(10),
Gname varchar(30),
Empnumber varchar(10), Sbackling int not null, Sbackreas varchar(30),
foreign key (Supnumber) references Supplier(Supnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Dyaolist(
Dynumber varchar(10) primary key, Gname varchar(30), Cusmumber varchar(10), Empnumber varchar(10), Dyliang int not null, Dydate date,
foreign key (Cusmumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Salelist(
Salenumber varchar(10) primary key, Gname varchar(30), Cusmumber varchar(10), Empnumber varchar(10), Saleliang int not null, Saledate date,
foreign key (Cusmumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Cbacklist(
Cbacknumber varchar(10) primary key, Gname varchar(30), Cusnumber varchar(10), Empnumber varchar(10), Cbackliang int not null, Cbackreas varchar(30),
foreign key (Cusnumber) references Customer(Cusnumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Inlist(
Innumer varchar(10) primary key, Gname varchar(30),
Hounnumber varchar(10), Empnumber varchar(10), Inliang int not null, Indate date,
foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Outlist(
Outnumber varchar(10) primary key, Gname varchar(30),
Hounnumber varchar(10), Empnumber varchar(10), Outliang int not null, Outdate date,
foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Lostlist(
Lostnumber varchar(10) primary key, Gname varchar(30),
Hounnumber varchar(10), Empnumber varchar(10), Lostliang int not null, Lostreas varchar(30),
foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
create table Panclist(
Pancnumber varchar(10) primary key, Gname varchar(30),
Hounnumber varchar(10), Empnumber varchar(10), Pancliang int not null,
foreign key (Hounnumber) references Hourse(Hounumber), foreign key (Gname) references Goods(Gname),
foreign key (Empnumber) references Employer(Empnumber))
--视图
create view Vi_supplier as
select *
from Supplier
create view Vi_goods as
select * from Goods
create view Vi_sale as
select Gname,Saleliang from Salelist
create view Vi_baosun as
select Gname,Lostliang from Lostlist
create view Vi_pancun as
select Gname,Pancliang from Panclist
--索引
create nonclustered index In_Supname on Supplier(SupName) --触发器
--限制删除C-02客户的退货记录 create trigger tri_C02 on Cbacklist
instead of delete as begin if exists( select *
from deleted
where Cusnumber='C-02' )
print '删除记录' end --测试 delete
from Cbacklist
where Cusnumber='C-02'
--限制某一药品入库量不小于400 create trigger tri_Acc on Inlist
instead of insert,update as declare
@Gname char(30), @Inliang int select
@Gname=Gname, @Inliang=Inliang from inserted if @Inliang<400
print '入库量小于400' --测试 insert into Inlist
values('I-25','牛黄解毒片','H-01','E-16',300,'2011-4-15')
--限制更新的药品价格不小于1元，小于1元的改为1元 create trigger tri_gprice on Goods
instead of insert,update as
declare
@Gname varchar(30), @Gprice float select
@Gname=Gname, @Gprice=Gprice from inserted if
@Gprice<1 update Goods set Gprice=1
where Gname=@Gname
--测试
insert into Goods
values('三黄片','处方药','普通片',0.5,'2012-9-1')
--保质期到2010年的不允许插入 create trigger tri_gshelf