Bài giảng Transaction vàTrigger

1Chương 8: TRANSACTION VÀ TRIGGER Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng HỆ QUẢN TRỊ CSDL SQL SERVER Mã hóa2 I- Transaction – Giao Tác Mã hóa3 I- Khái niệm : • Transaction là một gói lệnh dùng đảm bảo rằng các lệnh thay đổi dữ liệu trong gói đều được xử lý một cách trọn vẹn. • Nếu có một lệnh nào đó trong Transaction bị lỗi thì sẽ tự động bãi bỏ các lệnh trong Transaction và phục hồi lại toàn bộ dữ liệu đã bị thay đổi bởi các lệnh trong Transaction. Mã hóa4 2- Các Phát Biểu đóng gói một Transaction a) Bắt đầu Transaction: BEGIN TRAN[SACTION] [transaction_name] • Có thể không cần đặt tên Transaction. Tuy nhiên nên đặt tên Transaction để dễ đọc. b) Kết thúc Transaction: • Không phục hồi dữ liệu đã thay đổi: COMMIT TRAN [transaction_name] • Phục hồi dữ liệu đã thay đổi trong Transaction: ROLLBACK TRAN [transaction_name] 2Mã hóa5 Ví dụ: Khi thêm 1 CTHD cần giảm số tồn của mặt hàng. Nếu sau khi giảm mà số tồn < 0 thì phục hồi lại dữ liệu đã thay đổi. Create Proc THEM_CTHD(@SoHD Int, @MaMH Int, @SL Int, @DGBan Money) As Begin Tran A Insert CTHD(SoHD, MaMH, SL, DGBan) Values (@SoHD, @MaMH, @SL, @DGBan) Update MatHang Set SoTon=SoTon-@SL Where MaMH = @MaMH If (Select SoTon From MatHang Where MaMH=@MaMH)<0 Begin RollBack Tran A Print 'Khong du so luong de ban' Return 1 End Commit Tran A Return 0 Mã hóa6 3- Phân đoạn nhóm lệnh trong Transaction: Được sử dụng khi muốn phân chia nhóm lệnh cần bãi bỏ trong Transaction • Đánh dấu đầu phân đoạn: SAVE TRAN • Kết thúc Transaction và phục hồi dữ liệu đã thay đổi bởi nhóm lệnh: ROLLBACK TRAN Mã hóa7 Ví dụ: Thêm một hóa đơn và 1 CTHD, đồng thời giảm số tồn của mặt hàng. Nếu sau khi giảm mà số tồn < 0 thì bãi bỏ lệnh chèn CTHD. CREATE PROC THEM_HD_CTHD @SoHD int, @NgayHD DateTime, @MaNV Int, @MaMH Int, @SL Int, @DGBan Money As Begin Tran A Insert HoaDon (SoHD, NgayHD, MaNV) Values (@SoHD, @NgayHD, @MaNV) If @@Error 0 Begin RollBack Tran A Return 1 End Save Tran B Insert CTHD(SoHD, MaMH, SL, DGBan) Values (@SoHD, @MaMH, @SL, @DGBan) Update MatHang Set SoTon=SoTon-@SL Where MaMH = @MaMH If (Select SoTon From MatHang Where MaMH=@MaMH)<0 Begin RollBack Tran B Return 2 End Commit Tran A Mã hóa8 II- TRIGGERS 3Mã hóa9 1- Khái niệm: • Trigger là một thủ tục không có tham số và 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 đó. • 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. • Ngoài ra, Trigger còn dùng thực hiện các xử lý phức tạp trên các table có dữ liệu liên quan với table đang cập nhật. Mã hóa10 2- Tạo Trigger cho Table: 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óa11 3- 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óa12 a) Khi Insert mẫu tin mới INSERTED 100 SL DGBanMaLHSoHD 10249 209 CTHD 100 30 10 23 12 56 42 10 30 SL 20910249 DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED SL DGBanMaLHSoHD 4Mã hóa13 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, INSERTED b Where a.SoHD = b.SoHD) >= 10 Begin Print ‘So CTHD Khong the > 10’ RollBack Tran End Mã hóa14 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, INSERTED b Where a.MaMH = b.MaMH And 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óa15 b) Khi Delete mẫu tin INSERTED SL DGBanMaLHSoHD CTHD 100 30 10 23 12 56 42 10 30 SL 20910249 DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED 100 SL DGBanMaLHSoHD 10249 209 Mã hóa16 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 5Mã hóa17 c) Khi Update mẫu tin INSERTED 10 SL DGBanMaLHSoHD 10249 209 CTHD 10 30 10 23 12 56 42 10 30 SL 20910249 DGBanMaLHSoHD 10249 10249 10248 10248 10248 10246 10246 10246 38 82 96 41 104 52 52 41 DELETED 100 SL DGBanMaLHSoHD 10249 209 Mã hóa18 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. Create Trigger utrg_SoCTHD On CTHD For Update As If (Select Count(a.SoHD) From CTHD a, INSERTED b Where a.SoHD = b.SoHD) >= 10 Begin Print ‘So CTHD Khong the > 10’ RollBack Tran End + [SoHD] S XT CTHD -+ Mã hóa19 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óa20 4- 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 6Mã hóa21 Ví dụ: Sử dụng hàm Update If (@D > 0 ) Begin 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 End Create Trigger utrg_CTHD On CTHD For Update As If Update(SoHD) If (Select Count(a.SoHD) From CTHD a, INSERTED b Where a.SoHD = b.SoHD)>=10 Begin RollBack Tran Return End If Update(SL) Begin 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 Mã hóa22 5- 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óa23 6- 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óa24 7- Hiệu chỉnh Trigger: ALTER TRIGGER ON [WITH ENCRYPTION] AFTER | FOR {DELETE, INSERT, UPDATE} AS 7Mã hóa25 8- Xóa Trigger: DROP TRIGGER [,…n]