ADD CONSTRAINT DEF_DT DEFAULT 'CHUA CO' FOR DIENTHOAI

ALTER TABLE DONDH

ADD CONSTRAINT

ALTER TABLE CTDONDH

ADD CONSTRAINT CHK_SLDAT CHECK(SLDAT>0)

ALTER TABLE CTPNHAP

ADD CONSTRAINT CHK_SLNHAP CHECK(SLNHAP>0)

ALTER TABLE CTPNHAP

ADD CONSTRAINT CHK_DGNHAP CHECK(DGNHAP>0)

ALTER TABLE CTPXUAT

ADD CONSTRAINT CHK_SLXUAT CHECK(SLXUAT>0)

ALTER TABLE CTPXUAT

ADD CONSTRAINT CHK_DGXUAT CHECK(DGXUAT>0)

ALTER TABLE TONKHO

ADD CONSTRAINT CHK_SLDAU CHECK(SLDAU>=0)

ALTER TABLE TONKHO

ADD CONSTRAINT CHK_TONGSLX CHECK(TONGSLX>=0)

ALTER TABLE TONKHO

ADD CONSTRAINT CHK_TONGSLN CHECK(TONGSLN>=0)

-----------------------------------------------------------

// 3.2

alter table DONDH

ADD CONSTRAINT FK_MANHACC FOREIGN KEY(MANHACC)REFERENCES NHACC(MANHACC)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTDONDH

ADD CONSTRAINT FK_MAVTU FOREIGN KEY(MAVTU) REFERENCES VATTU(MAVTU)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTDONDH

ADD CONSTRAINT FK_SODH FOREIGN KEY(SODH) REFERENCES DONDH(SODH)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE PNHAP

ADD CONSTRAINT FK_SODH1 FOREIGN KEY(SODH) REFERENCES DONDH(SODH)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTPNHAP

ADD CONSTRAINT FK_MAVTU1 FOREIGN KEY(MAVTU) REFERENCES VATTU(MAVTU)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTPNHAP

ADD CONSTRAINT FK_SOPN FOREIGN KEY(SOPN) REFERENCES PNHAP(SOPN)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTPXUAT

ADD CONSTRAINT FK_MAVTU2 FOREIGN KEY(MAVTU) REFERENCES VATTU(MAVTU)

ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE CTPXUAT

ADD CONSTRAINT FK_SOPX FOREIGN KEY(SOPX) REFERENCES PXUAT(SOPX)

ON DELETE CASCADE ON UPDATE CASCADE

----------------------------------------------------------------

//3.4 a

create view vw_dmvt(mavtu,tenvtu)

as

select mavtu,tenvtu from vattu

--------------------------------------------------------------

create view vw_ctpxuat

as

select sopx,mavtu,slxuat,dgxuat, slxuat*dgxuat as thanhtien

from ctpxuat

// su dung view vw_ctpxuat

select * from vw_ctpxuat

----------------------------------

3.4 b

create view vw_dondh_tongslnhap

as

select sodh,tongsln

from dondh,tonkho

// su dung vew

select * from vw_dondh_tongslnhap

........

exec sp_helptext vw_dondh_tongslnhap

----------------------------------------

// 3.4 c

create view vw_dondh_tongsldatnhap(sodh,tongsldat,tongslnhap)

as

select pnhap.sodh,

(select sum(sldat) from ctdondh

where ctdondh.sodh=pnhap.sodh

group by sodh) as 'tongsldat',

sum(slnhap) as'tongslnhap'

from pnhap,ctpnhap

where ctpnhap.sopn=pnhap.sopn

group by sodh

//su dung

select * from vw_dondh_tongsldatnhap

------------------------------------------

// 3.4 d

create view vw_dondh_danhapdu(sodh,thongbao)

as

select sodh,thongbao=case

when tongsldat=tongslnhap then'da nhap du' else 'chua nhap du'

end

from vw_dondh_tongsldatnhap

//su dung

select * from vw_dondh_danhapdu

-------------------------------------------

//3.4 e

create view vw_tongnhap(namthang,mavtu,tongslnhap)

as

select (right(convert(char(10),ngaynhap,101),4)+'-'+left(convert(char(10),ngaynhap,101),2)),mavtu,

sum(slnhap) from ctpnhap,pnhap

where ctpnhap.sopn=pnhap.sopn

group by ngaynhap,mavtu

//xem view

select * from vw_tongnhap

---------------------------------

//3.4 f

create view vw_tongxuat(namthang,mavtu,tongslxuat)

as

select(right(convert(char(10),ngayxuat,101),4)+'-'+left(convert(char(10),ngayxuat,101),2)),mavtu,

sum(slxuat) from ctpxuat,pxuat

where ctpxuat.sopx=pxuat.sopx

group by ngayxuat,mavtu

//xem view

select* from vw_tongxuat

-----------------------------

//3.4 g

create view vw_dondh_mavtu_tongslnhap

as

select dondh.sodh,ngaydh,mavtu,sldat,

(select sum(slnhap)from ctpnhap

where ctpnhap.mavtu=ctdondh.mavtu group by mavtu)as tongslnhap

from dondh,ctdondh

where dondh.sodh=ctdondh.sodh

// xem view

select * from vw_dondh_mavtu_tongslnhap

----------------------------------

//3.5 a

select* from dondh

where sodh not in (select sodh from ctdondh)

--------------------------------------

//3.5 b

select* from vattu

where mavtu not in(select mavtu from ctdondh)

----------------------------------------

//3.5 c

select manhacc,count(manhacc)as tongsodh

from dondh group by manhacc

having count(manhacc)>=all(select count(manhacc)

from dondh group by manhacc)

-----------------------------------------------

//3.5 d

select top 1 mavtu,sum(slxuat) as tongslxuat

from ctpxuat group by mavtu

order by sum(slxuat) desc

-----------------------------------------------

//3.5 e

select top 1 sodh,count(mavtu) as tongmathang

from ctdondh group by sodh

order by count(mavtu) desc

----------------------------------------------

//3.5 f

select ngaynhap,vattu.mavtu,ngayxuat,tenvtu,sum(slnhap)as tongslnhap,sum(slxuat) as tongslxuat

from pnhap,pxuat,vattu,ctpxuat,ctpnhap

where pnhap.sopn=ctpnhap.sopn and pxuat.sopx=ctpxuat.sopx and ctpxuat.mavtu=vattu.mavtu and ctpnhap.mavtu=vattu.mavtu

group by ngaynhap,vattu.mavtu,ngayxuat,tenvtu

----------------------------------------

//3.5 g

select dondh.sodh,ctpnhap.mavtu,sldat,sum(slnhap) as tongslnhap

from dondh,ctdondh,ctpnhap

where ctpnhap.mavtu=ctdondh.mavtu and ctdondh.sodh=dondh.sodh

group by dondh.sodh,ctpnhap.mavtu,sldat

--------------------------------------------

//3.5 h

select ngaydh,vattu.mavtu,vattu.tenvtu,sum(sldat)as tongsldat

from vattu,dondh,ctdondh

where ctdondh.mavtu=vattu.mavtu and ctdondh.sodh=dondh.sodh

group by ngaydh,vattu.mavtu,vattu.tenvtu

-------------------------------------------

//3.5 i

select (right(convert(char(10),ngaydh,101),4)+'-'+left(convert(char(10),ngaydh,101),2))

as namthang,vattu.mavtu,tenvtu,sum(sldat)

from dondh,ctdondh,vattu

where dondh.sodh=ctdondh.sodh and vattu.mavtu=ctdondh.mavtu

group by ngaydh,vattu.mavtu,tenvtu

------------------------------------------

//3.5 j

select sodh,thongbao='chua nhap du'

from vw_dondh_tongsldatnhap

where tongsldat!=tongslnhap

-------------------------------------------

* *

*

-------------------------------------------

//4.1 a

select * from vattu

order by tenvtu desc

----------------------------

//4.1 b

select * from nhacc

where diachi like '%Q1 HCM%'

order by tennhacc asc

---------------------------

4.1 c

select sopn,mavtu,slnhap,dgnhap,thanhten=slnhap*dgnhap

from ctpnhap

------------------------------

4.1 d

select pnhap.sopn,ngaynhap,sodh,trigia=sum(slnhap*dgnhap)

from pnhap,ctpnhap

where ctpnhap.sopn=pnhap.sopn

group by pnhap.sopn,ngaynhap,sodh

-----------------------------------

4.1 e

select manhacc,tennhacc

from nhacc

where manhacc not in(select manhacc from dondh)

-----------------------------------

4.2 f

select pxuat.sopx,trigia=sum(slxuat*dgxuat)

from ctpxuat,pxuat

where ctpxuat.sopx=pxuat.sopx

group by pxuat.sopx

order by trigia desc

--------------------------------------

4.2 g

delete from ctdondh

from dondh

where convert(char(10),ngaydh,103)='15/01/2005'

and dondh.sodh=ctdondh.sodh

--------------------------------------

4.1 i

delete from ctpxuat

-------------------------------------

4.1 j

(1)

select sopx,mavtu,slxuat,dgxuat,ttien=slxuat*dgxuat

from ctpxuat

order by sopx

compute sum(slxuat*dgxuat) by sopx

(2)

select mavtu,sopn,slnhap,dgnhap

from ctpnhap

order by mavtu

compute sum(slnhap),min(dgnhap),max(dgnhap) by mavtu

------------------------------------------

//4.2 a

(c1)

declare @dtb real

select @dtb=avg(dgxuat)

from ctpxuat

where mavtu in (select mavtu from vattu where tenvtu='Ð?u DVD Hitachi 1 dia')

print 'don gia trung binh cua dau dvd hitachi1 la'+cast(@dtb as char(10))

if @dtb>3800000

print 'khong nen thay doi gia ban'

else

print 'da den luc tang ga ban'

(c2)

DECLARE @A REAL

SELECT @A=AVG(DGXUAT)

FROM CTPXUAT,VATTU

WHERE CTPXUAT.MAVTU=VATTU.MAVTU

AND VATTU.MAVTU='DD01'

Print 'Don gia trung binh cua dau DVD Hitachi1 la' + CAST(@A as char(4))

IF @A>3800000

PRINT ' khong nen thay doi gia ban'

else

PRINT ' Da den luc tang gia ban'

--------------------------------------------------------------

4.2 b

declare @dem int

select @dem=count(*)

from dondh

where datename(dw,ngaydh)='sunday'

if(@dem>0) --co don dat hang ko hop le--

select * from dondh

where datename(dw,ngaydh)='sunday'

else

print 'ngay lap cac don dat hang deu la hop le'

------------------------------------------------------

4.2 c

declare @dem int

select @dem=count(sodh)

from pnhap

where sodh='d001'

if(@dem>0)

print('có '+cast(@dem as char(4))+' so phieu nhap hang cho don dat hang d001')

else

print' chua co nhap hang nao cho d001'

-----------------------------------------------------

4.3 a

select mavtu,tenvtu into vattu_temp

from vattu

declare @mavtu char(4),@tenvtu char(100)

while exists(select * from vattu_temp)

select top 1 @mavtu=mavtu,@tenvtu=tenvtu from vattu_temp

delete vattu_temp where mavtu=@mavtu

print 'dang xoa vat tu: '+@tenvtu

end

Drop table VATTU_temp

--------------------------------------------

4.3 b

--chua xong dang nghj~ hehe

alter table vattu_temp

add sopx char(4), dgxuat float

declare @dgtb real,@dem int,@dgxuat float

set @dem=1

select @dgtb=avg(dgxuat) from ctpxuat where mavtu='dd01'

while( @dgtb<350000)

set dgxuat=dgxuat+dgxuat*5/100

set @dem=@dem+1

select

end

select dgxuat=@dgxuat from ctpxuat

print 'da thuc hien tang'+cast(@dem as char(4))

--------------------------------------------

4.4 a

select *,thu = case datename(dw,ngaydh)

when 'sunday' then 'chu nhat'

when 'monday' then 'thu hai'

when 'tuesday' then 'thu ba'

when 'wednesday' then 'thu tu'

when 'thursday' then 'thu nam'

when 'saturday' then 'thu bay'

end

from dondh

--------------------------------------------

4.4 b

update ctpxuat

set dgxuat=case

when slxuat<4 then dgxuat

when slxuat>=4 and slxuat<10 then dgxuat-dgxuat*5/100

when slxuat>=10 and slxuat<=20 then dgxuat - dgxuat*10/100

when slxuat>20 then dgxuat -dgxuat*20/100

end

where sopx in(select sopx from pxuat

where convert(char(6),ngayxuat,112)='200501')

select * from ctpxuat

-------------------------------------------------

-----------------------------------------------------

5.1 a

create proc spud_dondh_tinhsldat

@sodh char(4),@mavtu char(4),@sldat int output

as

declare @kq int

select @kq=sldat from ctdondh

where sodh=@sodh and mavtu=@mavtu

set @sldat=@kq

--sudung

declare @sldat int

exec spud_dondh_tinhsldat 'd001','dd01',@sldat output

print @sldat

-------------------------------------

5.1 b

alter proc spud_pnhap_tinhtongslnhang @sodh char(4),@mavtu char(4),@tongnhap int output

as

declare @kg int

select @kg=sum(slnhap)

from ctpnhap a inner join pnhap b on a.sopn=b.sopn

where mavtu=@mavtu and sodh=@sodh

set @tongnhap=isnull(@kg,0)

--su dung

declare @tongnhap int

exec spud_pnhap_tinhtongslnhang 'd001','dd02',@tongnhap output

print @tongnhap

----------------------------------------

5.1 c

create proc spud_tonkho_tinhlsdau @namthang char(6),@mavtu char(4),@sldau int output

as

declare @kq int

select @kq=sldau from tonkho where namthang=@namthang and mavtu=@mavtu

set @sldau=isnull(@kq,0)

declare @sldau int

exec spud_tonkho_tinhlsdau '200502','vd01',@sldau output

print @sldau

------------------------------------------------------

5.2 a

create proc spud_vattu_them

@mavtu char(4),@tenvtu nvarchar(100),@dvtinh nvarchar(10),@phantram real

as

-- kiem tra ma vat tu chua co trong bang vattu

if (select count(*) from vattu where mavtu=@mavtu)>0

print 'Mavtu da trung mavtu trong bang, nhap lai!'

return

end

-- them mau tin

insert into vattu values (@mavtu,@tenvtu,@dvtinh,@phantram)

-- Su dung

exec spud_vattu_them 'tv22','tivi sony 26 inches','cai',10

-- Xem sua chua

select * from vattu

-----------------------------------------------------

5.2 b

create proc spud_vattu_xoa @mavtu char(4)

as

if(select count(mavtu) from ctdondh where mavtu=@mavtu) >0

print 'mavtu da co trong bang ctdondh'

return

end

if(select count(mavtu) from ctpnhap where mavtu=@mavtu)>0

print 'mavtu da co trong bang ctpnhap'

return

end

if(select count(mavtu) from ctpxuat where mavtu=@mavtu)>0

print 'mavtu da co trong bang ctpxuat'

return

end

if(select count(mavtu) from ctdondh where mavtu=@mavtu) >0

print 'mavtu da co trong bang tonkho'

return

end

delete from vattu where mavtu=@mavtu

-- Su dung

exec spud_vattu_xoa 'tv22'

-- Xem sua chua

select * from vattu

select * from ctdondh

-------------------------------------------------------

5.1 c

create proc spud_vattu_sua

@mavtu char(4),@tenvtu nvarchar(100),@dvtinh nvarchar(10),@phantram real

as

update vattu

set tenvtu=@tenvtu,dvtinh=@dvtinh,phantram=@phantram

where mavtu=@mavtu

-- Su dung

exec spud_vattu_sua 'tv21','tivi sony 26 inches','cai',10

-- xem sua chua!

select * from vattu

-----------------------------------------------

5.3 a

create proc spud_vattu_bcaodanhsach

as

select * from vattu

order by tenvtu

exec spud_vattu_bcaodanhsach

----------------------------------------

5.3 b

create proc spud_tonkho_bcaotonkho @namthang char(6)

as

select namthang,vattu.mavtu,tenvtu,sldau,tongsln,tongslx,slcuoi from vattu,tonkho where vattu.mavtu=tonkho.mavtu and namthang=@namthang

-- su dung

exec spud_tonkho_bcaotonkho '200501'

---------------------------------------------------

5.3 c

create proc spud_pxuat_bcaopxuat @sopx char(4)=null

as

if @sopx is null

select pxuat.sopx,mavtu,ngayxuat,tenkh,slxuat,dgxuat from ctpxuat,pxuat where pxuat.sopx=ctpxuat.sopx

else

select pxuat.sopx,mavtu,ngayxuat,tenkh,slxuat,dgxuat from ctpxuat,pxuat where pxuat.sopx=ctpxuat.sopx and pxuat.sopx=@sopx

-- Su dung

exec spud_pxuat_bcaopxuat

exec spud_pxuat_bcaopxuat 'x003'

---------------------------------------------------

--------------------------------------------------------

5.4 a

create proc spud_dondh_them

@sodh char(4),@ngaydh datetime,@manhacc char(3)

as

if (select count(sodh) from dondh where sodh=@sodh)>0

print' so dh da trung'

return

end

if(select count(manhacc) from nhacc where manhacc=@manhacc)=0

print'manhacc khong co ben bang nhacc'

return

end

if @ngaydh is null

insert into dondh values (@sodh,getdate(),@manhacc)

else

insert into dondh values (@sodh,@ngaydh,@manhacc)

-- Van chua xong dang loi phan convert ngaydh ko bit lam ntn ca ^_^ --

exec spud_dondh_them 'd007','c07','01/01/2008'

exec spud_DONDH_Them 'D006','01/01/2008','C01'

--------------------------------------------------------------

5.4 b

create proc spud_dondh_xoa @sodh char(4)

as

if (select count(sodh) from pnhap where sodh=@sodh)>0

print 'so dat hang da co trong bang pnhap'

return

end

delete from dondh where sodh=@sodh

-- Lai sai tiep ko hieu vi sao nua@@ --

exec spud_dondh_xoa ''

select * from dondh

------------------------------------------------

5.4 f

Create proc spud_CTDONDH_Sua

@SoDH char(4), @MaVTu char(4), @SLDat int

as

Begin

if (not exists (select * from CTDONDH where SoDH=@SoDH and MaVTu=@MaVTu))

print' SoDH va MaVTu nay chua co trong bang DonDH!'

return

end

-----------------------------------------------

6.1.a

create function fn_tongnhapthang(@namthang char(6),@mavtu char(4))

returns int

as

declare @kq int

select @kq=sum(slnhap)

from ctpnhap,pnhap

where ctpnhap.sopn=pnhap.sopn and mavtu=@mavtu and convert(char(6),ngaynhap,112)=@namthang

return isnull(@kq,0)

end

--Su dung

declare @tong int

set @tong=dbo.fn_tongnhapthang('200201','dd01')

print @tong

-- test

select sum(slnhap) from ctpnhap,pnhap

where ctpnhap.sopn=pnhap.sopn

and mavtu='dd01' and convert (char(6),ngaynhap,112)='200201'

--------------------------------------

6.1 b

create function fn_tongxuatthang(@namthang char(6),@mavtu char(4))

returns int

as

declare @kq int

select @kq=sum(slxuat)

from ctpxuat,pxuat

where ctpxuat.sopx=pxuat.sopx

and mavtu=@mavtu

and convert(char(6),ngayxuat,112)=@namthang

return isnull(@kq,0)

end

--Su dung

declare @tong int

set @tong=dbo.fn_tongxuatthang('200201','dd01')

print @tong

--Test roi chuan ko can chinh ^^

----------------------------------------

6.1 c

create function fn_tongnhap(@sodh char(4),@mavtu char(4))

returns int

as

declare @kq int

select @kq=sum(slnhap) from ctpnhap,pnhap

where ctpnhap.sopn=pnhap.sopn

and sodh=@sodh and mavtu=@mavtu

return isnull(@kq,0)

end

-- Su dung

declare @tong int

set @tong=dbo.fn_tongnhap('d001','dd01')

print @tong

------------------------------

6.1 d

create function fn_connhap(@sodh char(4),@mavtu char(4))

returns int

as

declare @kq int,@sldat int

select @sldat=sum(sldat) from ctdondh

where sodh=@sodh and mavtu=@mavtu

set @kq=@sldat-dbo.fn_tongnhap(@sodh,@mavtu)

return isnull(@kq,0)

end

--su dung nhanh

print dbo.fn_connhap('d001','dd01')

-- chac chuan roi nhanh nhanh làm cái khác

---------------------------

6.1.e

create function fn_toncuoi(@mavtu char(4),@namthang char(6))

returns int

as

declare @kq int

select @kq=slcuoi from tonkho

where mavtu=@mavtu and namthang=@namthang

return isnull(@kq,0)

end

-- Su dung nhanh

print dbo.fn_toncuoi('dd01','200201')

------------------------------------

---------------------------------------

6.2.a

create function fn_ds_vattuconnhap(@sodh char(4))

returns table

as

return (select ctdondh.sodh,vattu.mavtu,tenvtu,dbo.fn_connhap(@sodh,vattu.mavtu)as slconnhap from vattu,ctdondh

where ctdondh.mavtu=vattu.mavtu

and sodh=@sodh and dbo.fn_connhap(@sodh,vattu.mavtu)>0)

--Su dung

select * from dbo.fn_ds_vattuconnhap('d001')

--------------------------------------

create function fn_ds_vattutonkho(@namthang char(6),@toithieu int)

returns table

as

return(select mavtu from tonkho

where namthang=@namthang

and slcuoi>@toithieu)

--Su dung

select * from dbo.fn_ds_vattutonkho('200201','4')

-------------------------------------

6.3.a

create function fn_ds_tonkho_lech_1thang(@namthang char(6))

returns @DSVT TABLE

( mavtu char(4),

sldau int,

slcuoi int

as

-- thôi dài lam bo qua

----------------------------------

----------- Trigger---------------

7.1.a

create trigger tg_pnhap_them on pnhap

for insert

as

declare @sodh char(4),@ngaynhap datetime

-- nhan du lieu

select @sodh=sodh,@ngaynhap=ngaynhap from inserted

--Kiem tra dk 1

if not exists (select * from dondh where sodh=@sodh)

raiserror('So dondh ko hop le',16,1)

rollback tran

return

end

--Kiem tra dk 2

declare @ngaydh datetime

select @ngaydh=ngaydh from dondh where sodh=@sodh

if @ngaynhap<@ngaydh

declare @loi char(200)

set @loi='Ngay nhap phai sau ngay:'+convert(char(10),@ngaydh,103)

raiserror (@loi,16,1)

rollback tran

return

end

-- Su dung

alter table pnhap nocheck constraint all

insert into pnhap values ('N200','1/1/2002','d003')

-- ? dây chú ý cái ràng bu?c add contrain mình dã làm t? tru?c nên nó uu tiên cái dó tru?c r?i m?i d?n trigger

--------------------------------------------

7.1.b

create trigger tg_ctpnhap_them on ctpnhap

for insert

as

declare @slnmoi int,@tongsldat int,@tongslncu int,@mavtumoi char(4)

select @mavtumoi=mavtu from inserted

select @slnmoi=slnhap from inserted

select @tongslncu=sum(slnhap)-@slnmoi from ctpnhap

where mavtu=@mavtumoi

select @tongsldat =sum(sldat) from ctdondh

where mavtu=@mavtumoi

if @slnmoi>(@tongsldat-@tongslncu)

declare @loi char(200)

set @loi='khong duoc vuot qua'+cast((@tongsldat-@tongslncu) as char(3))

raiserror(@loi,16,1)

rollback tran

return

end

-- Test

alter table ctpnhap nocheck constraint all

insert into ctpnhap values('n005','tv29','100','200000')

select * from ctpnhap

---------------------------------------------

7.2.a

create trigger tg_pxuat_xoa on pxuat

for delete

as

declare @sopx char(4)

select @sopx=sopx from deleted

delete from ctpxuat where sopx=@sopx

--Test

select * from ctpxuat

select * from pxuat

delete from pxuat where sopx='x006'

-----------------------------------

7.2.b

create trigger tg_pnhap_xoa on pnhap

for delete

as

declare @sopn char(4)

select @sopn=sopn from deleted

delete from ctpnhap where sopn=@sopn

--Test----

delete from pxuat where sopn=''

------------------------------------------

7.3.a

create trigger tg_pnhap_sua on pnhap

for update

as

-- Dieu kien 1

if update(sopn)or update(sodh)

rollback tran

raiserror('khong the thay doi sodh hay so pn',16,1)

return

end

-- Dieu kien 2

if update(ngaynhap)

if exists(select * from pnhap where sopn in(select sopn from deleted))

declare @maxngaynhap datetime

select @maxngaynhap=max(pnhap.ngaynhap) from pnhap,deleted

where pnhap.sopn=deleted.sopn

-- kiem tra ngay nhap phai sau ngay dat hang

if @maxngaynhap>(select ngaynhap from inserted)

declare @loi char(200)

set @loi='ngay dat hang phai sau ngay'+ convert(char(10),@maxngaynhap,103)

raiserror(@loi,16,1)

rollback tran

return

end

end

end

--test

select * from pnhap

alter table pnhap nocheck constraint all

update pnhap set sodh='n100' where sodh='n001'

-- oi roi oi qua chuan lun hi' hi'! di gia truyen roi lam tiep

------------------------------

7.3 b

create trigger tg_pxuat_sua on pxuat

for update

as

if update(sopx)

raiserror('ko duoc sua so px',16,1)

rollback tran

return

end

if update(ngayxuat)

if exists(select * from pxuat where sopx in (select sopx from deleted))

declare @ngayxuat datetime

select @ngayxuat=ngayxuat from deleted

if convert(char(6),@ngayxuat,112)<> (select convert(char(6),ngayxuat,112) from inserted)

declare @loi char(200)

set @loi='ngay xuat phai cung nam thang:'+ convert(char(6),@ngayxuat,112)

raiserror (@loi,16,1)

return

end

end

end

--Test

alter table pxuat nocheck constraint all

update pxuat set ngayxuat='',tenkh='' where sopn=''

select * from pxuat

--me oi met qua di thoi gio moi den 7.4 nghi ma nan qua

-------------------------------------------

7.4.a

--tao cot tongtg

alter table pnhap add tongtg float

update pnhap

set tongtg=( select sum(slnhap*dgnhap) from ctpnhap where sopn=pnhap.sopn)

select * from pnhap

--bat dau trigger

alter trigger tg_ctpnhap_them on ctpnhap

for insert

as

declare @sopn char(4),@mavtu char(4),@slnhap int,@dgnhap money

select @sodh=sopn,@mavtu=mavt,@slnhap=slnhap from inserted

--kiem tra 1

declare @sodh char(4)

select @sodh=sodh from pnhap where sopn=@sopn

if not exists(select * from ctdondh where sodh=@sodh and mavtu=@mavtu)

raiserror('ma vat tu chua co dat',16,1)

rollback tran

return

end

--xu li tinh toan 1

update pnhap set tg=tg+@slnhap*@dgnhap where sopn=@sopn

--Xu li tinh toan 2

declare @namthang char(6)

select @namthang=convert(char(6),ngaynhap,112)

from pnhap where sopn=@sopn

update tonkho set tongslnhap=tongslnhap+@slnhap*@dgnhap where namthang=@namthang and mavtu=@mavtu

-- Thuc hien---

-- Mai thi tren giay chan qua hoc hanh gi nua.... ko lam n?a

7.4b

Alter table PXUAT

add TongTG int

Update PXUAT

set TongTG=(select sum(SLXuat*DgXuat)from CTPXUAT where CTPXUAT.SoPX=PXUAT.SoPX group by SoPX)

from CTPXUAT

where CTPXUAT.SoPX=PXUAT.SoPX

Create trigger tg_PXUAT_Them

on CTPXUAT

for insert

as

Begin

Declare @SoPX char(4),@MaVTu char(4),@SLXuat int, @DgXuat money

select @SoPX=SoPX,@MaVTu=MaVTu,@SLXuat=SLXuat,@DgXuat=DgXuat

from inserted

if(not exists (select * from PXUAT where @SoPX=SoPX))

RollBack Tran

raiserror('SoPX phai co trong bang PXUAT!',16,1)

end

if( not exists (select * from VATTU where @MaVTu=MaVTu))

RollBack Tran

raiserror('MaVTu phai co trong bang VATTU!',16,1)

end

if( not exists (select * from TONKHO where @MaVTu=MaVTu having (sum(SLDau)-sum(TongSLN)-Sum(TongSLX))>= @SLXuat ))

RollBack Tran

Raiserror('SLXuat phai du trong bang TONKHO!',16,1)

end

Update PXUAT