Tiếp theo phần I
1.
Cho
khung nhìn được định nghĩa như sau:
CREATE VIEW
view_donhang
AS
SELECT
dondathang.sohoadon,makhachhang,manhanvien,
ngaydathang,ngaygiaohang,ngaychuyenhang,
noigiaohang,mahang,
giaban,soluong,mucgiamgia
FROM dondathang
INNER JOIN chitietdathang
ON
dondathang.sohoadon = chitietdathang.sohoadon
a.
Có thể
thông qua khung nhìn này để bổ sung dữ liệu cho bảng DONDATHANG được không?
Có
b. Có thể
thông qua khung nhìn này để bổ sung dữ liệu cho bảng CHITIETDATHANG được không?
Không
2.
Với
khung nhìn được định nghĩa như sau:
CREATE VIEW
view_donhang
AS
SELECT
dondathang.sohoadon,makhachhang,manhanvien,
ngaydathang,ngaygiaohang,ngaychuyenhang,
noigiaohang,mahang,
giaban*soluong
as thanhtien,
mucgiamgia
FROM dondathang
INNER JOIN chitietdathang
ON
dondathang.sohoadon = chitietdathang.sohoadon
a.
Có thể
thông qua khung nhìn này để xoá hay cập nhật dữ liệu trong bảng DONDATHANG được
không?
Có thể cập nhật nhưng không thể xóa
b. Có thể thông
qua khung nhìn này để cập nhật dữ liệu trong bảng CHITIETDATHANG được không?
Có thể được
3.
Tạo
thủ tục lưu trữ để thông qua thủ tục này có thể bổ sung thêm một bản ghi mới
cho bảng MATHANG (thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần bổ
sung: không trùng khoá chính và đảm bảo toàn vẹn tham chiếu)
CREATE PROCEDURE
sp_insert_mathang(
@mahang NVARCHAR(10),
@tenhang NVARCHAR(50),
@macongty NVARCHAR(10) = NULL,
@maloaihang INT = NULL,
@soluong INT = 0,
@donvitinh NVARCHAR(20) = NULL,
@giahang money = 0)
AS
IF NOT EXISTS(SELECT mahang FROM mathang
WHERE mahang=@mahang)
IF (@macongty IS NULL OR EXISTS(SELECT
macongty
FROM nhacungcap
WHERE macongty=@macongty))
AND
(@maloaihang IS NULL OR
EXISTS(SELECT
maloaihang FROM loaihang
WHERE maloaihang=@maloaihang))
INSERT INTO mathang
VALUES(@mahang,@tenhang,
@macongty,@maloaihang,
@soluong,@donvitinh,@giahang)
exec sp_insert_mathang '005','Bom B52','ct03',2,34,'VND',$500000
4.
Tạo thủ
tục lưu trữ có chức năng thống kê tổng số lượng hàng bán được của một mặt hàng
có mã bất kỳ (mã mặt hàng cần thống kê là tham số của thủ tục).
Create PROCEDURE
sp_thongkebanhang(@mahang NVARCHAR(10))
AS
SELECT mathang.mahang,tenhang,
SUM(chitietdathang.soluong) AS 'Tong so luong'
FROM mathang LEFT JOIN
chitietdathang
ON mathang.mahang=chitietdathang.mahang
WHERE mathang.mahang=@mahang
GROUP BY mathang.mahang,tenhang
exec sp_thongkebanhang '001'
an> EXISTS(SELECT
macongty
FROM nhacungcap
WHERE macongty=@macongty))
AND
(@maloaihang IS NULL OR
EXISTS(SELECT
maloaihang FROM loaihang
WHERE maloaihang=@maloaihang))
INSERT INTO mathang
VALUES(@mahang,@tenhang,
@macongty,@maloaihang,
@soluong,@donvitinh,@giahang)
exec sp_insert_mathang '005','Bom B52','ct03',2,34,'VND',$500000
5.
Viết
trigger cho bảng CHITIETDATHANG theo yêu cầu sau:
ü Khi một 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ì huỷ bỏ thao tác bổ sung.
create trigger
trg_chitietdathang_insert
on chitietdathang
for insert
as
begin
declare @mahang nvarchar(10)
declare @soluongban int
declare @soluongcon int
select @mahang=mahang, @soluongban=soluong
from inserted
select @soluongcon=soluong from mathang
where mahang=@mahang
if @soluongcon>=@soluongban
update mathang set soluong=soluong-@soluongban
where mahang=@mahang
else
rollback transaction
end
ü 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ì huỷ bỏ thao tác cập nhật.
create trigger
trg_chitietdathang_update
on chitietdathang
for update
as
if update(soluong)
begin
if exists(select sohoadon from
inserted where soluong<0)
rollback transaction
else
begin
update mathang
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<0)
rollback transaction
end
end
6.
Viết
trigger cho bảng CHITIETDATHANG để sao cho chỉ chấp nhận giá hàng bán ra phải
nhỏ hơn hoặc bằng giá gốc (giá của mặt hàng trong bảng MATHANG)
CREATE TRIGGER
trg_chitietdathang_giaban
ON chitietdathang
FOR INSERT,UPDATE
AS
IF UPDATE(giaban)
IF EXISTS(SELECT inserted.mahang
FROM mathang INNER JOIN inserted
ON mathang.mahang=inserted.mahang
WHERE mathang.giahang>inserted.giaban)
ROLLBACK TRANSACTION