77范文网 - 专业文章范例文档资料分享平台

医院药品进销存系统(6)

来源:网络收集 时间:2019-04-22 下载这篇文档 手机版
说明:文章内容仅供预览,部分内容可能不全,需要完整文档或者需要复制内容,请下载word后使用。下载word有问题请添加微信号:或QQ: 处理(尽可能给您提供完整文档),感谢您的支持与谅解。点击这里给我发消息

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

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读,免费范文网,提供经典小说综合文库医院药品进销存系统(6)在线全文阅读。

医院药品进销存系统(6).doc 将本文的Word文档下载到电脑,方便复制、编辑、收藏和打印 下载失败或者文档不完整,请联系客服人员解决!
本文链接:https://www.77cn.com.cn/wenku/zonghe/617203.html(转载请注明文章来源)
Copyright © 2008-2022 免费范文网 版权所有
声明 :本网站尊重并保护知识产权,根据《信息网络传播权保护条例》,如果我们转载的作品侵犯了您的权利,请在一个月内通知我们,我们会及时删除。
客服QQ: 邮箱:tiandhx2@hotmail.com
苏ICP备16052595号-18
× 注册会员免费下载(下载后可以自由复制和排版)
注册会员下载
全站内容免费自由复制
注册会员下载
全站内容免费自由复制
注:下载文档有可能“只有目录或者内容不全”等情况,请下载之前注意辨别,如果您已付费且无法下载或内容有问题,请联系我们协助你处理。
微信: QQ: