Đăng chủ đề Đăng  trả lời 
 
Đánh giá chủ đề:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Bài tập] Các bài tập về Trigger
12-01-2011, 11:27 AM (Được chỉnh sửa: 12-01-2011 11:36 AM bởi 3211111124.)
Bài viết: #1
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
Các bài tập về Trigger

Link bài tập: http://fit-hitu.edu.vn/forum/showthread.php?tid=5
Chủ đề này chúng ta cùng thảo luận và giải các bài tập về Trigger
AE có ý tưởng hay cứ post lên nhé! Có thể mình viết chưa chính xác, các bạn hãy hoàn thiện bài tập của mình nhé!
Mình chỉ gởi những bài tập có hướng giải quyết phức tạp, các bài tương tự các bạn nên tự làm nhé!

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
Có 2 thành viên nói cảm ơn 3211111124 bài viết này:
ntt18_8 (13-03-2013), trudu181 (18-01-2011)
12-01-2011, 11:32 AM
Bài viết: #2
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

Bài 1: Quản lý sinh viên
1.1) Chỉ có sinh viên ở TPHCM mới có học bổng
Bảng tầm ảnh hưởng:
BảngThêmXóaSửa
SinhVien+(HocBong)-+(HocBong)


Mã PHP: (SELECT ALL)
create trigger tr_hocbong on sinhvien
for insertupdate
as
    if 
exists (select from inserted WHERE Tinh not like 'TPHCM' and HocBong 0)
        
begin
            raiserror
('Hoc bong chi ap dung o TPHCM',15,1);
            
rollback tran
        end
;
-- 
thu nghiem
exec sp_themsv 
@masv '00004',
        @
tensv 'Nguyen Hong Phu'
        @
ngaysinh '08/07/1990'
        @
gioitinh '1',
        @
diachi '207/16',
        @
tinh 'HaNoi',
        @
makhoa 'CNTT',
        @
hocbong '20000'
update sinhvien set tinh 'Ha Noi' where masv '91002' 

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 11:39 AM
Bài viết: #3
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

1.4) Sinh viên thi 1 môn học tối đa 2 lần


Bảng tầm ảnh hưởng:
BảngThêmXóaSửa
KetQua+--

Mã PHP: (SELECT ALL)
create trigger tr_lanthi_insert on ketqua
for insert
as
    if (
select count(*) as SoLT from ketqua as ainserted as 
                     where a
.masv b.masv 
                     
and a.mamh b.mamh
                     
) > 2
            begin 
                raiserror
('Sinh vien da thi 2 lan mon hoc nay',15,1);
                
rollback tran;
            
end

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 11:42 AM (Được chỉnh sửa: 12-01-2011 11:43 AM bởi 3211111124.)
Bài viết: #4
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

--5. Sinh viên phải có kết quả thi lần 1 mới được thi lần 2

Bảng tầm ảnh hưởng:
BảngThêmXóaSửa
KetQua+-+

Mã PHP: (SELECT ALL)
create trigger tr_lanthi_lan2 on ketqua
for insert,update
as 
    if 
exists (select from inserted where lanthi =2)
        if 
not exists (select from ketqua as ainserted as b
                    where a
.masv b.masv
                        
and a.mamh b.mamh 
                        
and a.lanthi '1')
            
begin
                raiserror
('Chua co lan 1',15,1);
                
rollback tran;
            
end
Mã PHP: (SELECT ALL)
-- thu nghiem 
insert into ketqua values
('92242','VP','1','10')
update ketqua set lanthi '2' where masv '92242' and mamh 'VP'
delete from ketqua where masv '92242' and mamh 'VP' 

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 06:53 PM (Được chỉnh sửa: 12-01-2011 09:56 PM bởi 3211111124.)
Bài viết: #5
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

---6. Điểm Trung bình của sinh viên phải bằng Tổng điểm chia cho Tổng số môn học
Bài này thầy hướng dẫn rồi. Mình xin hướng dẫn lại: Vì điểm trung bình (DTB) trong bảng SinhVien phụ thuộc vào số môn học và điểm của môn học đó trong KetQua. Vì thế trigger của ta khi tạo ra phải đảm bảo được rằng khi thêm, xóa, sửa 1 bộ nào trong bảng KetQua đều phải lưu ý đến sự ảnh hưởng đến DTB tương ứng trong bảng SinhVien

Bảng tầm ảnh hưởng:
BảngThêmXóaSửa
SinhVien--+(DTB)
KetQua+++(Diem)

Theo bảng tầm ảnh hưởng trên ta phải viết 4 trigger cho 4 hành động tương ứng với 2 table SinhVien và KetQua
Mình đã gộp 3 hành động thêm, xóa, sửa trong 1 trigger cho bảng KetQua
- Khi đó trigger cho bảng SinhVien có tác dụng kiểm tra dữ liệu thêm mới vào có hợp lệ hay không (DTB)
- Trigger cho bảng KetQua có nhiệm vụ tự động cập nhật lại DTB khi ta thêm, xóa, sửa 1 bộ bất kỳ trong KetQua
- Lưu ý chúng ta phải làm trên bảng Kết quả chuẩn hóa mới đảm bảo chính xác về điểm trung bình
Kết quả chuẩn hóa:
Mã PHP: (SELECT ALL)
create view ketqua_ch
as
select b.*
from
(select masvmamhmax(diem) as diem
from KETQUA
group by MaSV
MaMH) as aketqua as b
where a
.MaSV b.MaSV and
      
a.diem b.Diem and
      
a.MaMH b.MaMH 

Mã PHP: (SELECT ALL)
create trigger tr_dtb_sv on Sinhvien
for update
    
as 
       if 
update(DTB)
        if (
select DTB from inserted) <>
            (
select AVG(diem)
             
from ketqua_ch as kq
             where kq
.masv = (select masv from inserted) )
            
begin
            raiserror
('DTB phai bang TongDiem / SoMon da hoc',15,1);
            
rollback
        end
;

create trigger tr_dtb_kq on KetQua
for insertupdatedelete
    
as
    
begin
        
if update(diem) or not exists (select from deleted)
            
begin
                update sinhvien
                set DTB 
= (select AVG(diem)
                            
from ketqua_ch as kq
                            where kq
.masv = (select masv from inserted))
                
where masv = (select masv from inserted)
            
end;
        if  
not exists (select from inserted)
            
begin
                update sinhvien
                set DTB 
= (select AVG(diem)
                            
from ketqua_ch as kq
                            where kq
.masv = (select masv from deleted))
                
where masv = (select masv from deleted)
            
end;
    
end

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 06:58 PM (Được chỉnh sửa: 12-01-2011 06:58 PM bởi 3211111124.)
Bài viết: #6
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

1.8) Xếp loại sinh viên phải tuân theo tiêu chuẩn:

Điểm trung bình <5.0 : yếu
5.0 <= điểm trung bình < 6.5 : trung bình
6.5 <= điểm trung bình < 8.0 : khá
8.0 <= điểm trung bình < 9.0 : giỏi
9.0 <= điểm trung bình <=10.0 : xuất sắc

Bài này không khó, ta dùng cấu trúc điều khiển "case" để bẫy lỗi. Mình ví dụ cho các bạn hiểu thêm SQL Transact hỗ trợ ta thêm cấu trúc này!

Mã PHP: (SELECT ALL)
create trigger tr_xeploai on sinhvien
for update
 
as 
 if 
exists (select from sinhvien as ainserted as b
            where a
.masv b.masv
            
and a.XL <>  case
                
when a.dtb 5.0 then 'Yeu'
                
when a.dtb >= 5.0 and a.dtb 6.5 then 'TB'
                
when a.dtb >= 6.5 and a.dtb 8.0 then 'Kha'
                
when a.dtb >= 8.0 and a.dtb 9.0 then 'Gioi'
                
when a.dtb >= 9.0 and a.dtb <= 10 then 'Xuat sac'
            
end)
        
begin
            raiserror
('Xep loai sinh vien khong theo chuan ',15,1);
            
rollback
        end


Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 07:08 PM (Được chỉnh sửa: 12-01-2011 07:15 PM bởi 3211111124.)
Bài viết: #7
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

10. Mã sinh viên phải tuân theo quy tắc sau: 1 ký tự đầu là ‘V’, 4 ký tự sau biểu thị số thứ tự.
--Ví dụ: V0001.

Bài này có 2 cách làm. Một là các bạn dùng toán tử like thay cho patindex (Pattern).
Cú pháp patindex: patindex(<pattern>,<field_compare>)
Dùng pattern:

Mã PHP: (SELECT ALL)
create trigger tr_masv on Sinhvien
for insertupdate
as
     if 
update(MaSV) or not exists (select from deleted)
    if 
not exists(select from inserted
                  where patindex
('[V][0-9][0-9][0-9][0-9]',masv) > 0)  
        
begin
            raiserror
('Mã sinh viên phải tuân theo quy tắc sau: 1 ký tự đầu là ‘V’, 4 ký tự sau biểu thị số thứ tự',15,1);
            
rollback
        end


Dùng like:
Mã PHP: (SELECT ALL)
create trigger tr_masv on Sinhvien
for insertupdate
as
    if 
update(MaSV) or not exists (select from deleted)
        if 
exists(select from inserted
                      where MaSV not like 
'[V][0-9][0-9][0-9][0-9]')   
            
begin
                raiserror
('Mã sinh viên phải tuân theo quy tắc sau: 1 ký tự đầu là ‘V’, 4 ký tự sau biểu thị số thứ tự',15,1);
                
rollback
            end


Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 07:24 PM (Được chỉnh sửa: 12-01-2011 10:19 PM bởi 3211111124.)
Bài viết: #8
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
Trigger bài 2: Quản lý nhân viên

--2.1. Them moi nhan vien, kiem tra tuoi <=25 lam viec o Quan Ly
Mã PHP: (SELECT ALL)
create trigger tr_tuoinv on nhanvien
for insert
    
as
        if 
exists (select from inserted as aPHONGBAN as b
                    where a
.PHG b.MAPHG
                    
and YEAR(GETDATE()) -  YEAR(NGSINH) <= 25
                    
and TENPHG not like 'Quan ly')
                
begin
                    raiserror
('Nhan vien tuoi <=25 phai lam viec o phong Quan ly',15,1);
                    
rollback tran;
                
end;
drop trigger tr_tuoinv
-- test
INSERT INTO NHANVIEN VALUES 
('NGUYEN','HONG','PHU','111','08/07/1990','Q9-TP.HCM','NAM',30000,'005',4)
delete from nhanvien where manv 111 

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
Có 1 thành viên nói cảm ơn 3211111124 bài viết này:
T.Nhung (3211111114) (12-01-2011)
12-01-2011, 10:23 PM
Bài viết: #9
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

2. Thêm vào bảng DEAN một cột TONGGIO có kiểu dữ liệu là decimal(8,2). Tạo
trigger để cập nhật lại tổng số giờ làm việc của các nhân viên trong đề án đó khi
thêm hoặc sửa một bộ trong bảng PHANCONG.

Mã PHP: (SELECT ALL)
-- add column
alter table DEAN
add  TONGGIO decimal
(8,2
-- 
tao trigger
create trigger tr_tonggio on phancong
for insertupdate
    
as
        
begin
            update da
            set tonggio 
= (select sum(THOIGIANfrom phancong pc 
                            where da
.mada pc.soda 
                            group by pc
.soda)
            
from dean dainserted i
            where da
.mada =  i.soda
        end
;
        
-- 
test
drop trigger  tr_tonggio 
--insert
INSERT INTO PHANCONG VALUES 
('008',2,30)
--
update
update phancong set thoigian 
'50' where ma_nvien '008' and soda '2'
-- detele
delete from phancong where ma_nvien 
'008' and soda '2' 

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
12-01-2011, 10:27 PM
Bài viết: #10
Level: 23
Life: 281 / 551
Magic: 212 / 5,508
Experience: 4
RE: Các bài tập về Trigger

Will update........

Tham quan website của thành viên này Tìm tất cả bài viết của thành viên này
Cảm ơn bài viết này Trích dẫn và trả lời bài này
Đăng chủ đề Đăng  trả lời 


Chuyển nhanh:


Các thành viên đang xem bài viết này:
1 khách

Lên trênNội dung