syxt.sql

jsp写的一个简单的售药系统(系统需求分析).采用sql2000数据库,通过odbc连接

create DATABASE syxt
ON primary (name=syxt,
Filename='d:\售药系统.mdf',
Size=2MB,
Maxsize=unlimited,
Filegrowth=10%)
Log on
(name=syxt_log,
filename='d:\售药系统.ldf',
size=1MB,
maxsize=50MB,
filegrowth=1)
go

use syxt;
go

create table khzl
(khbh int identity(1,1) primary key,
khmc varchar(50),
khdh varchar(30),
khdz varchar(50));

go

create table ypms
(ypbh int identity(1,1) primary key,
pm varchar(30),
gg varchar(20),
dw varchar(8),
cbdj float,
xsdj float,
yxq varchar(20));

go


create table xsd
(djh int identity(1,1) primary key,
khbh int,
jhrq datetime,
kdrq datetime default getdate(),
zdr varchar(10),
ywy varchar(10),
fhy varchar(10),
qsr varchar(10),
foreign key (khbh) references khzl(khbh));

go


create table xsdxz
(djh int,
xzh int,
ypbh int,
ph varchar(10),
sl int,
je float,
primary key (djh,xzh),
foreign key (djh) references xsd(djh),
foreign key (ypbh) references ypms(ypbh));

go


create table thd
(thdbh int identity(1,1) primary key,
xsdbh int,
kdrq datetime default getdate(),
zdr varchar(10),
ywy varchar(10),
jsy varchar(10),
foreign key (xsdbh) references xsd(djh));

go


create table thdxz
(thdbh int,
xzh int,
ypbh int,
ph varchar(10),
sl int,
je float,
primary key (thdbh,xzh),
foreign key (thdbh) references thd(thdbh),
foreign key (ypbh) references ypms(ypbh));

go


create table yszk
(djh int,
khbh int,
ysje float,
zfrq datetime default getdate(),
zfje float,
primary key (djh,khbh),
foreign key (djh) references xsd(djh),
foreign key (khbh) references khzl(khbh));

go


create table kc
(ypbh int,
ph int,
scrq datetime default getdate(),
sl int,
primary key (ypbh,ph),
foreign key (ypbh) references ypms(ypbh));

go












sp_dboption syxt,'recursive triggers','false' ;
go 


create trigger xsd_xz 
on xsdxz
INSTEAD OF insert 
as begin
IF not exists(select * from xsdxz,inserted where xsdxz.djh=inserted.djh) begin
insert into xsdxz(djh,xzh,ypbh,ph,sl,je) 
select djh,1,ypbh,ph,sl,je from inserted end
else insert into xsdxz(djh,xzh,ypbh,ph,sl,je) 
select djh,1+(select max(xzh) from xsdxz where xsdxz.djh=inserted.djh),ypbh,ph,sl,je from inserted
end

go


create trigger xsdxz_del
on xsdxz
for delete
as begin
update xsdxz set xsdxz.xzh=xsdxz.xzh-1
from xsdxz,deleted
where xsdxz.xzh>deleted.xzh
end

go



create trigger thd_xz
on thdxz
INSTEAD OF insert 
as begin
IF not exists(select * from thdxz,inserted where thdxz.thdbh=inserted.thdbh) begin
insert into thdxz(thdbh,xzh,ypbh,ph,sl,je) 
select thdbh,1,ypbh,ph,sl,je from inserted end
else insert into thdxz(thdbh,xzh,ypbh,ph,sl,je) 
select thdbh,1+(select max(xzh) from thdxz where thdxz.thdbh=inserted.thdbh),ypbh,ph,sl,je from inserted
end

go


create trigger thdxz_del
on thdxz
for delete
as begin
update thdxz set thdxz.xzh=thdxz.xzh-1
from thdxz,deleted
where thdxz.xzh>deleted.xzh
end

go



create trigger k_c
on kc
INSTEAD OF insert 
as begin
IF not exists(select * from kc,inserted where kc.ypbh=inserted.ypbh) begin
insert into kc(ypbh,ph,scrq,sl) 
select ypbh,1,scrq,sl from inserted end
else insert into kc(ypbh,ph,scrq,sl)  
select ypbh,1+(select max(ph) from kc where kc.ypbh=inserted.ypbh),scrq,sl from inserted 
end

go








insert into khzl(khmc,khdh,khdz) values('广州市','020','th');
insert into khzl(khmc,khdh,khdz) values('珠海市','0756','th');
insert into khzl(khmc,khdh,khdz) values('BJ','010','th');
insert into khzl(khmc,khdh,khdz) values('Tj','021','th');


go

insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('蛇胆川贝胶囊','12粒*1盒','盒',1.9,1.9,'两年');

insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('消炎利胆片','100片*1瓶','瓶',1.45,1.45,'两年');

insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('抗骨增生片','100片*1瓶','瓶',1.8,1.8,'两年');

insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('复方甘草片','100片*1瓶','瓶',3.8,3.8,'两年');

go
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('复方甘草片','100片*1瓶','瓶',3.8,3.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('复方甘草片','100片*1瓶','瓶',3.8,3.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('阿奇霉素分散片','50片*1瓶','瓶',4.8,4.8,'三年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('安乃近片','60片*1瓶','瓶',8.8,8.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('板蓝根颗粒','20袋*1包','包',10.8,10.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('鼻舒适片','10片*1瓶','瓶',3.0,3.0,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('补中益气丸','70片*1瓶','瓶',3.0,3.0,'三年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('川贝枇杷糖浆','10小包*1包','瓶',13.8,13.8,'四年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('穿心莲片','100片*1瓶','瓶',2.8,2.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('防芷鼻炎片','100片*1瓶','瓶',3.8,3.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('复方丹参片','10片*1盒','盒',30.0,30.0,'一年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('复方虎杖片','100片*1瓶','瓶',4.8,4.8,'三年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('感冒清片','10片*1盒','盒',8.8,8.8,'一年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('蛤蚧大补丸','10片*1盒','盒',40.8,40.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('肌苷片','10片*1瓶','瓶',6.8,6.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('利福平胶囊','10片*1盒','盒',5.8,5.8,'三年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('千柏鼻炎片','10片*1盒','盒',7.8,7.8,'四年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('蛇胆川贝胶囊','20片*1盒','盒',3.0,3.0,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('三七胶囊','15片*1盒','盒',6.8,6.8,'两年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq)
values('珍珠末','10片*1瓶','瓶',83.0,83.0,'五年');
insert into ypms(pm,gg,dw,cbdj,xsdj,yxq) 
values('紫雪','10片*1瓶','瓶',3.1,3.1,'两年');
go




insert into kc(ypbh,ph,scrq,sl)
values(1,2,2008-03-01,1000);

insert into kc(ypbh,ph,scrq,sl)
values(1,2,2008-05-01,3000);

insert into kc(ypbh,ph,scrq,sl)
values(1,2,2008-09-01,1000);

insert into kc(ypbh,ph,scrq,sl)
values(1,2,2008-10-01,1000);

insert into kc(ypbh,ph,scrq,sl)
values(1,2,2009-01-01,1000);

insert into kc(ypbh,ph,scrq,sl)
values(2,2,2008-03-01,800);

insert into kc(ypbh,ph,scrq,sl)
values(2,2,2008-08-01,800);

insert into kc(ypbh,ph,scrq,sl)
values(3,2,2008-03-01,8000);

go

insert into xsd(khbh,jhrq,zdr,ywy)
values(2,2008-03-03,'zzz','fff');
go


insert into xsdxz(djh,xzh,ypbh,ph,sl,je)
values(1,1,1,2,55,105);

insert into xsdxz(djh,xzh,ypbh,ph,sl,je)
values(1,1,1,3,55,105);

insert into xsdxz(djh,xzh,ypbh,ph,sl,je)
values(1,1,2,1,100,145);
go


insert into xsd(khbh,jhrq,zdr,ywy)
values(1,2009-03-03,'java','c++');
go