bai 2.8-> 2.12

--2.8

create trigger TG_DatHang_NCC on Chi_Tiet_Dat_Hang
for insert, update as
begin
declare @MaMH int
select @MaMH=MaMH from Inserted
if(@Mamh in(select cu.MaMH from Nha_Cung_Cap ncc,Cung_ung cu,Chi_Tiet_Dat_Hang ctdh,
Dat_Hang dh where ncc.MaNCC=cu.MaNCC and ncc.MaNCC=dh.MaNCC and dh.SoDH=ctdh.SoDH))
begin
print N'dat hang thanh cong'
commit tran

end
else
begin
print N'chi co the dat hang neu co nha cung ung'
rollback tran
end
end

drop trigger TG_DatHang_NCC
select * from cung_ung
select * from CHI_TIET_DAT_HANG
insert into CHI_TIET_DAT_HANG values (1,4,15,25000)
insert into MAT_HANG values(4,N'Bia Sài Gòn',N'Chai',N'Thùng',240);
update cung_ung set mancc=1 where mancc=1 and mamh=3
select distinct ncc.mancc,ncc.tenncc,cu.mamh
from chi_tiet_dat_hang ctdh,cung_ung cu,dat_hang dh,nha_cung_cap ncc
where ctdh.sodh=dh.sodh and dh.mancc=ncc.mancc and cu.mancc=ncc.mancc


--2.9
create trigger TG_Giao_Dat on Chi_Tiet_Giao_Hang
for insert, update as
begin
declare @MaMH int
select @MaMH=MaMH from Inserted
if(exists(select * from Chi_Tiet_Dat_Hang where mamh=@mamh))
begin
print N'thanh cong'
commit tran

end
else
begin
print N'chua dat'
rollback tran
end
end
drop trigger TG_Giao_Dat
select *from chi_tiet_dat_hang
select *from chi_Tiet_giao_Hang
update chi_tiet_Giao_hang set mamh=3 where sogh=1 and mamh=2
--2.10
create trigger TG_Giao_Dat_SL on Chi_Tiet_Giao_Hang
for insert, update as
begin
declare @MaMH int,@SlDat int
select @MaMH=MaMH from Inserted
select @sldat=sldat from chi_tiet_dat_hang where mamh=@mamh
if(@sldat>=(select sum(slgiao)from chi_tiet_giao_hang where mamh=@mamh))
begin
print N'thanh cong'
commit tran

end
else
begin
print N'chua dat'
rollback tran
end
end


select * from chi_tiet_giao_hang
select * from chi_tiet_dat_hang
update chi_tiet_giao_hang set slgiao=33 where sogh=1 and mamh=1
---------------------------------------
--2.11
create trigger TG_MatHang_Dat_SL on Chi_Tiet_Dat_Hang
for insert, update as
begin
declare @sodh int,@SlDat int
select @sodh=sodh from Inserted
select @sldat=slmathang from dat_hang where sodh=@sodh
if(@sldat>=(select count(*)from chi_tiet_dat_hang where sodh=@sodh))
begin
print N'thanh cong'
commit tran

end
else
begin
print N'chua dat'
rollback tran
end
end

drop trigger TG_MatHang_Dat_SL
select * from Dat_hang
select * from chi_tiet_dat_hang

insert into Dat_hang values(2,1,2,10000)
update Dat_hang values(1,1,2,10000)
insert into chi_tiet_dat_hang values(1,1,2,10000)
update chi_tiet_dat_hang set sldat=333 where sodh=1 and mamh=1
delete chi_tiet_dat_hang where sodh=1 and mamh=2

----------------------------------------------------
--2.12
create trigger TG_MatHang_SLton on Chi_Tiet_giao_Hang
for insert, update as
begin
declare @mamh int,@Slton int,@slgiao int,@tam int
select @mamh=mamh from Inserted
select @slgiao=slgiao from Inserted
select @slton=slton from mat_hang where mamh=@mamh
if(exists(select*from mat_hang where mamh=@mamh))
begin
print N'thanh cong'
update mat_hang set slton=@slton-@slgiao where mamh=@mamh
commit tran

end
else
begin
print N'that bai'
rollback tran
end
end

drop trigger TG_MatHang_SLton
select * from mat_hang
select * from Chi_Tiet_giao_Hang
update Chi_Tiet_giao_Hang set slgiao=27where mamh=2 and sogh=1