thi.sql

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