Giáo trình Hệ quản trị cơ sở dữ liệu - Chương 6: Bẫy lỗi (Trigger) - Lê Thị Minh Nguyên

8/25/2017 1 Chương 6. Bẫy lỗi (Trigger) GV: Lê Thị Minh Nguyện Email: nguyenltm@huflit.edu.vn Nội dung 1. Khái niệm. 2. Công dụng trigger 3. Các dạng ràng buộc toàn vẹn 4. Ràng buộc dữ liệu toàn vẹn với trigger 5. Cơ chế hoạt động của trigger Hệ quản trị Cơ sở dữ liệu 2 3 • Trigger là một dạng đặc biệt của thủ tục lưu trữ và thực thi tự động khi người dùng áp dụng câu lệnh cập nhật dữ liệu lên một table chỉ định nhằm mục đích đảm bảo tính toàn vẹn dữ liệu. Nếu trigger bị vi phạm, câu lệnh sẽ không được thực thi 1. Khái niệm 4 • Đảm bảo tính toàn vẹn trong CSDL • Kiểm soát dữ liệu hiện có trong CSDL khi thay đổi giá trị của một mẩu tin trong bảng • Kiểm tra dữ liệu nhập có thỏa điều kiện không • Kiểm chứng khi xóa mẩu tin trong bảng • Tự động cập dữ liệu trong bảng B khi bảng A thay đổi 2. Công dụng trigger 8/25/2017 2 3. Các dạng ràng buộc toàn vẹn ??? Hệ quản trị Cơ sở dữ liệu 5 6 4.1. Thêm mới mẩu tin 4.2. Xóa mẩu tin 4.3. Sửa mẩu tin 4. Ràng buộc dữ liệu toàn vẹn với trigger 7 • Kiểm tra ràng buộc dữ liệu • Khoá ngoại • Miền giá trị • Liên bộ trên một quan hệ • Liên thuộc tính trong cùng một quan hệ • Liên thuộc tính của nhiều quan hệ 4.1. Thêm mới mẩu tin 4.2. Xóa mẩu tin • Phụ thuộc khóa ngoại • Liên bộ liên quan hệ Hệ quản trị Cơ sở dữ liệu 8 8/25/2017 3 9 • Khoá ngoại • Miền giá trị • Liên bộ trên một quan hệ • Liên thuộc tính trong một quan hệ • Liên thuộc tính của nhiều quan hệ 4.3. Sửa mẩu tin 10 • 3 biến cố kích hoạt 1 trigger • INSERT • UPDATE • DELETE • Trigger lưu trữ dữ liệu của mẩu tin vừa thêm vào một table mới có tên là INSERTED • Trigger lưu trữ dữ liệu của mẩu tin vừa xoá vào một table có tên là DELETED. • Trigger lưu trữ dữ liệu của mẩu tin vừa cập nhật là sự phối hợp của 2 table DELELTED và INSERTED 5. Cơ chế hoạt động của trigger 11 • Cú pháp: CREATE TRIGGER Tên_trigger ON tên_table| tên_view AFTER | INSTEAD OF biến_cố_kích_hoạt_trigger AS -- Các câu lệnh T-SQL Có thể thay bằng FOR. After là mặc định 5. Cơ chế hoạt động của trigger (tt) 12 CREATE TRIGGER Them_HH ON HANG_HOA AFTER INSERT AS Select * From Inserted • Thêm dữ liệu INSERT HANG_HOA(MaHH, TenHH) VALUES(‘TV01’, ‘Tivi Sony’) 5. Cơ chế hoạt động của trigger (tt) 8/25/2017 4 13 CREATE TRIGGER SUA_HH ON HANG_HOA AFTER UPDATE AS Select * From Inserted Select * From Deleted • Cập nhật dữ liệu UPDATE HANG_HOA SET Ten_HH = ‘Man Hinh Sony’ WHERE MaHH = ‘TV01’ 5. Cơ chế hoạt động của trigger (tt) 14 CREATE TRIGGER Xoa_HH ON HANG_HOA AFTER DELETE AS Select * From Inserted Select * From Deleted • Xóa dữ liệu DELETE HANG_HOA WHERE MaHH = ‘TV01’ 5. Cơ chế hoạt động của trigger (tt) 15 • HOADON_DH(MaHD, NgayDH, MaKH) • PHIEU_XUAT(MaPX, NgayXuat, #MaHD ) • CHITIET_DH(MAHD, MaHH, SoLuong, DonGia) • Xây dựng trigger trong bảng PHIEU_XUAT để kiểm tra các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới thông tin của một phiếu xuất hàng cho một bảng hoá đơn đặt hàng trước đó. Các ràng buộc toàn vẹn dữ liệu bao gồm. • Khoá ngoại: cần kiểm tra số đặt hàng phải tồn tại trong bảng đơn đặt hàng. • Miền giá trị: cần kiểm tra ngày giao hàng phải ở sau ngày đặt hàng. Ví dụ 1 16 CREATE TRIGGER tg_PhieuXuat_Insert ON PHIEU_XUAT FOR INSERT AS DECLARE @NgayHD datetime, @ErrMsg varchar(200) -- Kiểm tra số hoá đơn đã có trong bảng DONDH không? IF NOT EXISTS(Select * From Inserted I, HOADON_DH D Where I.MaHD= D.MaHD) Begin Rollback Tran Raiserror(‘Số đơn đặt hàng không tồn tại’, 16,1) Return End Ví dụ 1 (tt) 8/25/2017 5 17 --Tính ra ngày đặt hàng Select @NgayDH=NgayDH From HoaDon_DH D, Inserted I Where D.MaHD = I.MaHD -- Kiểm tra ngày giao hàng phải sau ngày đặt hàng IF @NgayDH < (Select ngayxuat From Inserted) Begin Set @ErrMsg = ‘ngày giao hàng phải ở sau ngày:’+ Convert(char(10), ngayDH, 103 ) Raierror(@ErrMsg,16,1) Rollback tran End Ví dụ 1 (tt) 18 •Kiểm tra ràng buộc • Kiểm tra ràng buộc khóa ngoại • Ví dụ: khi xoá một số hoá đơn đặt hàng trong bảng HOADON_DH cần phải kiểm tra các RBTV dữ liệu sau: • Kiểm tra xem đơn đặt hàng bị xoá đã được xuất hàng chưa? Nếu đã được xuất rồi thì thông báo không thể xoá đơn đặt hàng được. • Ngược lại thì xoá dữ liệu liên quan bên bảng chi tiết đơn đặt hàng (CHITIET_HD) Ví dụ 2 19 CREATE TRIGGER tg_HOADON_Delete ON HOADON_DH FOR DELETE AS DECLARE @SoPX char(5), @ErrMsg char(200), @Delete_Err int -- Kiểm tra xem đơn hàng đã được xuất chưa IF EXISTS(Select MaPX From PHIEU_XUAT Where MaHD IN(Select MaHD From Deleted)) Begin Select @MaPX = MaPX From PHIEU_XUAT Where MaHD In(Select MaHD From Deleted) Set @ErrMsg = ‘Đơn đặt hàng đã được nhập theo ’+ ‘số xuất hàng ’+ @SoPX + char(13) + ‘.Không thể huỷ được’ RaiseError(@ErrMsg,16,1) Rollback tran End Ví dụ 2 (tt) 20 Else Begin -- Xoá tự động chi tiết các đơn đặt hàng liên quan Delete FROM CHITIET_DH Where MaHD In(Select MaHD From DELETED) Set @Delete_Err = @@ERROR IF @Delete_Err 0 Begin Set @ErrMsg = ‘Lỗi vi phạm xóa trên bảng chi tiết đặt hàng’ RaisError(@ErrMsg, 16, 1) Rollback Tran End End Ví dụ 2 (tt) 8/25/2017 6 21 • Ý nghĩa • Kiểm tra dữ liệu của cột bên trong bảng có bị thay đổi trong các trigger sửa đổi dữ liệu • Cú pháp UPDATE (tên_cột) (biểu thức luận lý) • Tên_cột: tên cột mà chúng ta muốn kiểm tra xem dữ liệu tại đó có bị sửa đổi trong trigger không. • Biểu thức luận lý: trả về True khi giá trị dữ liệu của cột đã bị sửa đổi, ngược lại trả về False khi giá trị dữ liệu của cột không bị sửa đổi Hàm Update 22 Sửa đổi thông tin của một số đặt hàng bên trong bảng HOADON_DH cần phải kiểm tra các ràng buộc toàn vẹn dữ liệu sau: • Không cho phép sửa đổi dữ liệu tại cột MaDH hoặc MaKH vì khi đó dữ liệu sẽ ảnh hưởng đến nhiều bảng. • Sửa đổi giá trị cột ngày đặt hang (NgayDH) thì phải đảm bảo luôn luôn trước ngày giao hàng đầu tiên của số đặt hàng đó (nếu đơn đặt hàng đã có giao hàng). HOADON_DH(MaHD, NgayDH, MaKH) PHIEU_XUAT(MaPX, NgayXuat, #MaHD ) Ví dụ 3 23 CREATE TRIGGER tg_HOADON_DH_Update ON HOADON_DH FOR UPDATE AS Declare @MinNgayXH date, @ErrMsg varchar(200) -- Khi sửa đổi các cột MaDH hoặc MaKH IF Update(MaDH) OR Update(MaKH) Begin Rollback Tran Set @ErrMsg = ‘Không thể thay đổi số đặt hàng hoặc mã khách hàng’ RaisError(@ErrMsg, 16, 1) Return End Ví dụ 3 (tt) 24 -- Khi sửa đổi ngày đặt hàng IF Update(NgayDH) Begin -- Kiểm tra đơn đặt hàng đã được xuất chưa IF EXISTS (Select MaPX From PHIEU_XUAT PX, Deleted d where px.madh=d.madh) Begin -- Tính ra ngày nhập hàng đầu tiên Select @MinNgayXH = Min(NgayXuat) From PHIEU_XUAT PX, DELETED D Where PX.MaDH = D.MaDH Ví dụ 3 (tt) 8/25/2017 7 25 --kiểm tra giá trị ngày đăt hàng sau khi sửa đổi phải luôn trước ngày giao hàng đầu tiên IF @MinNgayXH < (Select NgayDH From Inserted) Begin Rollback tran Set @ErrMsg = ‘Ngày đặt hàng phải ở trước ngày:’ + Convert(char(10), @MinNgayXH, 103) RaisError(@ErrMsg, 16, 1) End End End Ví dụ 3 (tt) 26 Cài đặt trigger ngăn chặn tạo login ở cấp độ server IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = ‘rtg_KhongTaoLoginMoi’) DROP TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER GO CREATE TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER FOR CREATE_LOGIN AS PRINT N'Phi DROP trigger rtg_KhongTaoLoginMoi trưc khi to account’ rollback Ví dụ 4 27 Cho phép (enable) hoặc vô hiệu hóa (disable) trigger: ENABLE | DISABLE TRIGGER trigger_name ON {OBJECT | DATABASE | SERVER} Vô hieu hóa trigger rtg_KhongTaoLoginMoi: disable trigger rtg_KhongTaoLoginMoi Cho phép trigger rtg_KhongTaoLoginMoi hoạt động trở lại: enable trigger rtg_KhongTaoLoginMoi Trigger 28 Hiển thị thông tin vê các triggger: Tất cả các đối tượng trong CSDL được liệt kê trong bảng hệ thống sysobjects. Cột type trong sysobjects xác định các trigger với chữ viết tắt là TR SELECT * FROM sysobjects WHERE type=‘TR’ Trigger 8/25/2017 8 29 Cú pháp hiển thị thông tin về triggger: sp_help tên_trigger Hiển thị thông tin trigger tgr_test: Sp_help tgr_test Trigger 30 • Câu lệnh Create triggger của mỗi trigger được lưu trữ trong bảng hệ thống syscomments. Người dùng có thể hiển thị nội dung câu lệnh trigger bằng cách sử dụng thủ tục sp_helptext • Hiển thị nội dung trigger tgr_test: Sp_helptext tgr_test Trigger Hệ quản trị Cơ sở dữ liệu 31