Created
May 22, 2016 15:20
-
-
Save danhuynhdev/af2404ec752745f0fee29852855ccba2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop table ctnhapsuat; | |
drop table phieunhapsuat; | |
drop table vattu; | |
drop table khachhang; | |
create table khachhang( | |
makh char(5) primary key, | |
ten varchar(50), | |
diachi varchar(50), | |
dienthoai varchar(20) | |
); | |
create table vattu( | |
mavt char(5) primary key, | |
tenvt varchar(50), | |
dongiahh numeric(20), | |
slton numeric(20) | |
); | |
create table phieunhapsuat( | |
maphieu char(5) primary key, | |
ngay date, | |
makh char(5) references khachhang(makh), | |
loaiphieu varchar(20) | |
); | |
create table ctnhapsuat( | |
maphieu char(5) references , | |
mavt char(5), | |
soluong numeric(20), | |
dongia numeric(20), | |
constraint pk primary key (maphieu, mavt) | |
); | |
delete from khachhang; | |
insert into khachhang(makh,ten,diachi,dienthoai) | |
values | |
('kh01', 'le van a', '', '0512345678'), | |
('kh02', 'le van b', '', '0512345678'), | |
('kh03', 'le van c', '', '0512345678'), | |
('kh04', 'le van d', '', '0512345678'), | |
('kh05', 'le van e', '', '0512345678'); | |
insert into vattu(mavt,tenvt,dongiahh,slton) | |
values | |
('vt01', 'vat tu 1', 100000, 100), | |
('vt02', 'vat tu 2', 100000, 100), | |
('vt03', 'vat tu 3', 100000, 100), | |
('vt04', 'vat tu 4', 100000, 100), | |
('vt05', 'vat tu 5', 100000, 100); | |
insert into phieunhapsuat(maphieu,ngay,makh,loaiphieu) | |
values | |
('ph01', '18-11-2005', 'kh01', 'N'), | |
('ph02', '18-11-2005', 'kh02', 'X'), | |
('ph03', '18-11-2005', 'kh03', 'X'), | |
('ph04', '18-11-2005', 'kh04', 'N'), | |
('ph05', '1-1-2013', 'kh05', 'N'); | |
insert into ctnhapsuat(maphieu,mavt,soluong,dongia) | |
values | |
('ph01', 'vt01', 10, 100000), | |
('ph02', 'vt02', 10, 100000), | |
('ph03', 'vt03', 10, 100000), | |
('ph04', 'vt04', 10, 100000), | |
('ph05', 'AS14', 10, 100000); | |
select ten,diachi | |
from khachhang | |
where makh in ( | |
select makh | |
from | |
phieunhapsuat p, | |
ctnhapsuat c | |
where | |
p.maphieu = c.maphieu | |
and c.mavt = 'AS14' | |
and p.ngay = '1-1-2013' | |
); | |
-- sql server | |
create proc vtxuat @maphieu char(5) | |
as | |
begin | |
select * from phieunhapsuat | |
where maphieu = @maphieu m and loaiphieu = 'X'; | |
end | |
create trigger xuatvt on ctnhapsuat | |
for insert | |
as | |
declare @mavt char(5); | |
declare @soluong numeric(50); | |
declare @slton numeric(50); | |
select @mavt=i.mavt, @soluong=i.soluong, @slton=v.slton | |
from inserted i, vattu v | |
where i.mavt = v.mavt; | |
if @slton < 0 | |
begin | |
print 'Ko con hang'; | |
rollback tran; | |
end; | |
if @slton < @soluong | |
begin | |
print 'Ko du hang'; | |
rollback tran; | |
end; | |
update vattu set slton = @slton - @soluong; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment