HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 8:TRIGGER

Chia sẻ: Nguyễn Mạnh Cường | Ngày: | Loại File: PDF | Số trang:18

Thêm vào BST

Báo xấu

608
lượt xem 169
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

I- Khái niệm: • Trigger là một thủ tục không có tham số • Một Table có thể chứa nhiều Trigger • Trigger tự động thực hiện khi một trong 3 phát biểu Insert, Update, Delete thay đổi dữ liệu trên table chứa trigger.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 8:TRIGGER

HỆ QUẢN TRỊ CSDL SQL SERVER Chương 8: TRIGGER Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng
I- Khái niệm: • Trigger là một thủ tục không có tham số • Một Table có thể chứa nhiều Trigger • Trigger tự động thực hiện khi một trong 3 phát biểu Insert, Update, Delete thay đổi dữ liệu trên table chứa trigger. • Trigger chỉ được thực hiện khi phát biểu cập nhật thoả mãn các ràng buộc đã khai báo trên Table. Do đó, Trigger thường được dùng để kiểm tra các ràng buộc mà không thể khai báo trên Table như các ràng buộc liên thuộc tính liên quan hệ, liên bộ liên quan hệ, ràng buộc chu trình. • Sử dụng lệnh ROLLBACK TRAN trong Trigger để bãi bỏ phát biểu cập nhật khi cần thiết. Mã hóa 2
II- Tạo Trigger cho Table: 1. Dùng lệnh Create Trigger 2. Dùng công cụ Management Studio Mã hóa 3
1- Lệnh Create Trigger: CREATE TRIGGER ON [WITH ENCRYPTION] AFTER | FOR {DELETE, INSERT, UPDATE} AS • Tập con của { DELETE, INSERT, UPDATE} dùng chỉ định những phát biểu cập nhật nào trên Table sẽ kích hoạt Trigger. Mã hóa 4
2- Sử dụng bảng tạm: Inserted và Deleted Khi thực hiện Trigger, SQL tự động tạo 2 bảng tạm có cùng cấu trúc với Table được cập nhật: • Bảng INSERTED: dùng chứa tạm các mẫu tin mới khi thực hiện lệnh Insert hoặc mẫu tin chứa dữ liệu đã sửa đổi khi thực hiện lệnh Update. • Bảng DELETED : dùng chứa tạm các mẫu tin bị xóa khi thực hiện lệnh Delete hoặc mẫu tin chứa dữ liệu cũ khi thực hiện lệnh Update. Ta sử dụng dữ liệu trên các table này để thực hiện xử lý bên trong Trigger liên quan đến các mẫu tin vừa mới thêm, sửa hay xóa. Mã hóa 5
a) Khi Insert mẫu tin mới CTHD INSERTED SoHD MaLH SL DGBan SoHD MaLH SL DGBan 10248 56 10 4 10249 100 20 9 10248 12 4 1 10248 23 9 6 10249 10 8 2 DELETED 10249 30 3 8 SoHD MaLH SL DGBan 10249 9 100 20 10249 100 20 9 Ví dụ : Kiểm tra RB: Một hóa đơn không có quá 10 chi tiết hóa đơn. T S X CTHD + + - [SoHD] Mã hóa 6
Ví dụ: Khi thêm 1 CTHD, kiểm tra RB: Một hóa đơn không có quá 10 chi tiết hóa đơn. Create Trigger itrg_SoCTHD On CTHD For Insert As If (Select Count(a.SoHD) From CTHD a Join INSERTED b On a.SoHD = b.SoHD) >= 10 Begin Print ‘So CTHD Khong the > 10’ RollBack Tran End Mã hóa 7
Ví dụ: Khi thêm chi tiết hóa đơn thì phải giảm số tồn của mặt hàng đã ghi trên chi tiết hóa đơn. Create Trigger itrg_GiamTon On CTHD For Insert As Declare @D int Select @D = Count( * ) From MatHang a Join INSERTED b On a.MaMH = b.MaMH Where SoTon - b.SL < 0 If (@D > 0 ) Begin Print ‘Khong du hang de ban’ RollBack Tran Return End Update MatHang Set SoTon = SoTon - SL From INSERTED Where MatHang.MaMH = INSERTED.MaMH Mã hóa 8
b) Khi Delete mẫu tin CTHD INSERTED SoHD MaLH SL DGBan SoHD MaLH SL DGBan 10248 56 10 4 10248 12 4 1 DELETED 10248 23 9 6 SoHD MaLH SL DGBan 10249 10 8 2 10249 100 20 9 10249 30 3 8 10249 100 20 9 Ví dụ: Khi xóa một chi tiết hóa đơn thì phải tăng số tồn của mặt hàng đã ghi trên chi tiết hóa đơn. Mã hóa 9
Ví dụ: Khi xóa một chi tiết hóa đơn thì phải tăng số tồn của mặt hàng đã ghi trên chi tiết hóa đơn. Create Trigger trg_XoaCTHD On CTHD For Delete As Update MatHang Set SoTon = SoTon + SL From DELETED Where MatHang.MaMH = DELETED.MaMH Go Mã hóa 10
c) Khi Update mẫu tin CTHD INSERTED SoHD MaLH SL DGBan SoHD MaLH SL DGBan 10248 56 10 4 10249 10 20 9 10248 12 4 1 10248 23 9 6 DELETED 10249 10 8 2 SoHD MaLH SL DGBan 10249 30 3 8 10249 10249 100 20 9 9 10 20 10249 100 20 9 Ví dụ: Khi sửa SoHD của CTHD, kiểm tra RB: Một hóa đơn không có quá 10 chi tiết hóa đơn. T S X CTHD + + - [SoHD] Mã hóa 11
Ví dụ: Khi sửa SoHD của CTHD, kiểm tra RB: Một hóa đơn không có quá 10 chi tiết hóa đơn. T S X CTHD + + - [SoHD] Create Trigger utrg_SoCTHD On CTHD For Update As If (Select Count(a.SoHD) From CTHD a Join INSERTED b On a.SoHD = b.SoHD) > 10 Begin Print ‘So CTHD Khong the > 10’ RollBack Tran End Mã hóa 12
Ví dụ: Khi sửa số lượng trên các chi tiết hóa đơn thì phải sửa số tồn của mặt hàng đã ghi trên chi tiết hóa đơn. Create Trigger utrg_SuaCTHD On CTHD For Update As Declare @D int Select @D = Count( * ) From MatHang a, DELETED b, INSERTED c Where a.MaMH = b.MaMH And a.MaMH = c.MaMH And SoTon + b.SL - c.SL < 0 If (@D > 0 ) Begin Print ‘Khong du hang de ban’ RollBack Tran Return End Update MatHang Set SoTon = SoTon + b.SL - c.SL From MatHang a, DELETED b, INSERTED c Where a.MaMH = b.MaMH And a.MaMH = c.MaMH Mã hóa 13
3- Nhận biết Cột được cập nhật : • Hàm Update() : Trả về giá trị TRUE nếu được cập nhật dữ liệu. Ví dụ: Khi sửa thông tin chi tiết hóa đơn: • Nếu sửa SoHD thì kiểm tra RB "Một hóa đơn không có quá 10 CTHD • Nếu sửa SL thì thay đổi số tồn của mặt hàng Mã hóa 14
Ví dụ: Sử dụng hàm Update Create Trigger utrg_CTHD If (@D > 0 ) On CTHD For Update Begin As If Update(SoHD) RollBack Tran If (Select Count(a.SoHD) Return From CTHD a, INSERTED b Where a.SoHD = b.SoHD)>=10 End Begin RollBack Tran Update MatHang Return Set SoTon = SoTon + b.SL – c.SL End From MatHang a, DELETED b, If Update(SL) INSERTED c Begin Where a.MaMH = b.MaMH And Declare @D int a.MaMH = c.MaMH Select @D = Count( * ) End From MatHang a, DELETED b, INSERTED c Where a.MaMH = b.MaMH And a.MaMH = c.MaMH And SoTon + b.SL – c.SL < 0 Mã hóa 15
4- Chỉ định thứ tự thực hiện các Trigger • Nếu cùng một thao tác trên một Table mà có nhiều Trigger (khác tên nhau). Khi đó thứ tự thực hiện các trigger được xác định dựa trên thứ tự tạo ra chúng. • Để thay đổi thứ tự thực hiện mặc định này, sử dụng thủ tục: sp_settriggerorder[@triggername = ] 'Tên trigger' , [@order = ] 'First | Last | None' , [@stmttype = ] 'Insert | Update | Delete' Ví dụ: sp_SetTriggerOrder itrg_SoCTHD, 'First', ‘Insert’ sp_SetTriggerOrder itrg_GiamTon, 'Last', ‘Insert’ • SP này chỉ có thể chỉ định trigger nào được thực hiện đầu tiên và Trigger nào được thực hiện cuối cùng. Các Trigger còn lại sẽ thực hiện theo thứ tự tạo ra chúng. Mã hóa 16
5- Thực hiện hay không thực hiện Trigger ALTER TABLE ENABLE | DISABLE TRIGGER ALL | [,…n] Ví dụ: Không thực hiện tất cả Triggers của table CTHD ALTER TABLE DISABLE TRIGGER ALL Ví dụ: Không thực hiện Trigger itrg_SoCTHD và utrg_SoCTHD của table CTHD ALTER TABLE DISABLE TRIGGER itrg_SoCTHD, utrg_SoCTHD Mã hóa 17
III- Sửa, Xóa Trigger: 1. Sửa Trigger: ALTER TRIGGER ON [WITH ENCRYPTION] AFTER | FOR {DELETE, INSERT, UPDATE} AS 2. Xóa Trigger: DROP TRIGGER [,…n] Mã hóa 18