--39. Giam 25% luong cua nhung nhan vien trong nam 2008 khong lap bat ky hoa don nao
Update NhanVien
Set PhuCap=0.25*phucap
Where manhanvien = any (
Select manhanvien
From nhanvien
Where not exists (
Select *
From dondathang
Where dondathang.manhanvien=nhanvien.manhanvien) )
--2.Thong ke tong so luong ban hang cua mot ma hang
Create Proc sp_2
As
Begin
Select mahang as 'Ma Hang', sum(soluong) as 'So Luong'
From chitietdathang, dondathang
Where chitietdathang.sohoadon=dondathang.sohoadon
Group By mahang
End;
Drop Proc sp_2
Execute sp_2
--3.
Create Function Fun_3
Return nvarchar(5)
Begin
Select mahang from mahang
End;
Select * from chitietdathang
where soluong in(200,400,500)
Select * from nhanvien
where manhanvien in ('NV001','NV003','NV002','NV000')
select manhanvien
From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon
group by manhanvien
having sum(soluong) = any (
select sum(soluong)
From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon
group by manhanvien )
Select sum(soluong)
From mahang
where mahang='MH001'
Create Function Fun_sl(@date datetime)
returns int
as
begin
Declare @getdate int
Set @getdate = year(@date)
return @getdate
end
Drop Function Fun_sl
Select * From Fun_sl '2/8/1989'
--Su dung ham noi tuyen inline
Create Function Fun_inline()
returns table
as
return (Select mahang, count(maloaihang),sum(soluong) from mahang
Group By mahang)
Drop Function Fun_inline
Select * from Fun_inline()
(chú ý: CSDL QuanLyBanHang có bảng MatHang, ở CSDL khi các em tạo ra, ghi chệch thành MaHang, nên em sửa lại tên bảng trong đoạn code dưới khi chạy thử ở CSDL của mình)
-- Bài 4: Viết trigger cho bảng chitietdathang
-- a. Khi có 1 bản ghi mới được bổ sung vào bảng này thì giảm số lượng hàng hiện có
-- nếu số lượng hàng hiện có lớn hơn hoặc bằng số lượng hàng được bán ra.
-- Ngược lại thì hủy bỏ thao tác bổ sung
Create trigger trg_chitietdathang_insert
on chitietdathang
for insert
as
begin
-- khai báo các biến sử dụng trong trigger
declare @mahang nvarchar (10) -- biến chứa mã hàng được cập nhật
declare @sl_con int -- biến chứa số lượng hàng còn
declare @sl_ban int -- biến chứa số lượng hàng bán ra
-- Lấy mã hàng, số lượng bán ra từ bảng inserted và gán vào các biến tương ứng
Select @mahang = mahang, @sl_ban = soluong from inserted
-- Lấy số lượng hàng con từ bảng mathang và gán vào các biến tương ứng
select @sl_con = soluong from mathang where mahang=@mahang
-- so sánh và cập nhật nếu thỏa mãn điều kiện
if @sl_con >= @sl_ban
update mathang set soluong = soluong - @sl_ban where mahang = @mahang
else
rollback transaction
end
-- thêm vào bảng chitietdathang để test trigger
insert into chitietdathang values (1,'MH001',120000,30,0)
drop trigger trg_chitietdathang_insert_cursor
-----------------------------------------------------------------------------------------
-- b. Khi cập nhật lại số lượng hàng được bán, kiểm tra số lượng hàng được cập nhật lại
--có phù hợp hay không (số lượng hàng bán ra không được vượt quá số lượng hàng hiện có
--và không được nhỏ hơn 1). Nếu dữ liệu hợp lệ thì giảm (hoặc tăng) số lượng hàng hiện có
--trong công ty, ngược lại thì bỏ thao tác cập nhật
create trigger trg_chitietdathang_update_soluong
on chitietdathang
for update
as
if update(soluong)
begin
if exists (select sohoadon from inserted where soluong <1)
rollback transaction
else
begin
update mathang
-- chú ý: vì đây là trường hợp số lượng bản ghi cập nhật >1, nên cần tính tổng số lượng của các bản ghi được cập nhật
set soluong = soluong - (select sum(inserted.soluong - deleted.soluong)
from inserted inner join deleted on inserted.sohoadon = deleted.sohoadon
and inserted.mahang = deleted.mahang
where inserted.mahang = mathang.mahang
group by inserted.mahang)
where mahang in (select distinct mahang from inserted)
if exists (select mahang from mathang where soluong < 1)
rollback transaction
end
end
drop trigger trg_chitietdathang_update_soluong
--------------------------
Create Trigger trg_chitietdathang_giaban
On Chitietdathang
For Insert, update
As
if update (giaban)
if ( Exists (Select inserted.mahang From Inserted, Mathang
where Inserted.MaHang=MatHang.MaHang
and Mathang.giahang > inserted.giaban) )
Begin
Print'Ko hop le' RollBack Transaction end
(chú ý: CSDL QuanLyBanHang có bảng MatHang, ở CSDL khi các em tạo ra, ghi chệch thành MaHang, nên em sửa lại tên bảng trong đoạn code dưới khi chạy thử ở CSDL của mình)
-- Bài 4: Viết trigger cho bảng chitietdathang
-- a. Khi có 1 bản ghi mới được bổ sung vào bảng này thì giảm số lượng hàng hiện có
-- nếu số lượng hàng hiện có lớn hơn hoặc bằng số lượng hàng được bán ra.
-- Ngược lại thì hủy bỏ thao tác bổ sung
Create trigger trg_chitietdathang_insert
on chitietdathang
for insert
as
begin
-- khai báo các biến sử dụng trong trigger
declare @mahang nvarchar (10) -- biến chứa mã hàng được cập nhật
declare @sl_con int -- biến chứa số lượng hàng còn
declare @sl_ban int -- biến chứa số lượng hàng bán ra
-- Lấy mã hàng, số lượng bán ra từ bảng inserted và gán vào các biến tương ứng
Select @mahang = mahang, @sl_ban = soluong from inserted
-- Lấy số lượng hàng con từ bảng mathang và gán vào các biến tương ứng
select @sl_con = soluong from mathang where mahang=@mahang
-- so sánh và cập nhật nếu thỏa mãn điều kiện
if @sl_con >= @sl_ban
update mathang set soluong = soluong - @sl_ban where mahang = @mahang
else
rollback transaction
end
-- thêm vào bảng chitietdathang để test trigger
insert into chitietdathang values (1,'MH001',120000,30,0)
drop trigger trg_chitietdathang_insert_cursor
-----------------------------------------------------------------------------------------
-- b. Khi cập nhật lại số lượng hàng được bán, kiểm tra số lượng hàng được cập nhật lại
--có phù hợp hay không (số lượng hàng bán ra không được vượt quá số lượng hàng hiện có
--và không được nhỏ hơn 1). Nếu dữ liệu hợp lệ thì giảm (hoặc tăng) số lượng hàng hiện có
--trong công ty, ngược lại thì bỏ thao tác cập nhật
create trigger trg_chitietdathang_update_soluong
on chitietdathang
for update
as
if update(soluong)
begin
if exists (select sohoadon from inserted where soluong <1)
rollback transaction
else
begin
update mathang
-- chú ý: vì đây là trường hợp số lượng bản ghi cập nhật >1, nên cần tính tổng số lượng của các bản ghi được cập nhật
set soluong = soluong - (select sum(inserted.soluong - deleted.soluong)
from inserted inner join deleted on inserted.sohoadon = deleted.sohoadon
and inserted.mahang = deleted.mahang
where inserted.mahang = mathang.mahang
group by inserted.mahang)
where mahang in (select distinct mahang from inserted)
if exists (select mahang from mathang where soluong < 1)
rollback transaction
end
end
drop trigger trg_chitietdathang_update_soluong
--------------------------
Create Trigger trg_chitietdathang_giaban
On Chitietdathang
For Insert, update
As
if update (giaban)
if ( Exists (Select inserted.mahang From Inserted, Mathang
where Inserted.MaHang=MatHang.MaHang
and Mathang.giahang > inserted.giaban) )
Begin
Print'Ko hop le'
RollBack Transaction
End
Bạn đang đọc truyện trên: Truyen2U.Pro