Trigger trong SQL

SQL Nguyễn Thị Hậu TRIGGER 1. Khái niệm • Trigger là một kiểu thủ tục được lưu đặc biệt, chúng được tự động gọi khi bạn sửa đổi dữ liệu mà trigger được thiết kế để bảo vệ. • Các trigger giúp bảo đảm sự toàn vẹn cho dữ liệu của bạn bằng cách ngăn không cho những sự thay đổi không nhất quán được thực hiện. • Vd : lương của nhân viên không thể cao hơn lương của người quản lý. 2. Tạo trigger CREATE TRIGGER Trigger_name ON table | view [WITH ENCRYPTION] { FOR | AFTER | INSTEAD OF } {[DELETE] [,] [INSERT] [,] [UPDATE] } AS Sql_statement Trong đó WITH ENCRYPTION Ngăn chặn người dùng khác xem văn bản của trigger 2. Tạo trigger AFTER : - trigger được thực thi sau khi tất cả các câu lệnh SQL gây ra trigger đã thực thi thành công. - AFTER là kiểu mặc định nếu từ khóa FOR được dùng. -Không thể định nghĩa trigger AFTER cho view INSTEAD OF - trigger được thực thi thay cho các câu lệnh SQL gây ra trigger. - INSTEAD OF trigger dùng được cho view. {[DELETE] [,] [INSERT][,][UPDATE]} - xác định câu lệnh mà khi thực thi trên bảng hoặc view sẽ gây ra trigger. Một số chú ý • deleted, inserted là các bảng logic lưu các giá trị cũ và mới của các hàng có thể bị thay đổi. • IF UPDATE (column_list) Dùng để kiểm tra hành động Insert, Update (không dùng cho Delete) 3.Các kiểu trigger 3.1. Insert trigger Create trigger TongLuong1 On NhanVien For Insert As If ( (select MasoDV from Inserted) is Not Null) Begin Update DonVi Set TongLuong = TongLuong + (select luong from Inserted ) Where MasoDV= (select MasoDV from Inserted ); Print 'Da cap nhat tong luong cua don vi. Tong luong1' End Kiểm tra : insert into Nhanvien values (17,'Phan Nhu','Quynh','1985-04-16','Nu','Nam Dinh', 4000000, 2,3) 3.Các kiểu trigger 3.2. Update Trigger CREATE TRIGGER UpdateMaNV ON NhanVien FOR UPDATE AS IF UPDATE (MaNV) BEGIN PRINT ‘Khong the thay doi gia tri cua MaNV’ ROLLBACK TRANSACTION END Kiểm tra : update NhanVien set MasoNV=100 where MasoNV=1 3.Các kiểu trigger 3.3. Delete trigger Create trigger XoaDV On DonVi For Delete As Begin Delete from NhanVien Where MasoDV=(select MasoDV from deleted); Delete from DuAn Where MasoDV=(select MasoDV from deleted); End Kiểm tra : Delete from Donvi Where MasoDV=1 4.Sửa đổi trigger ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } 5.Xóa trigger • DROP TRIGGER { trigger_name } [ ,...n ] Xóa một hay nhiều trigger Ví dụ : USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR') DROP TRIGGER employee_insupd GO 6. INSTEAD-OF trigger • Instead Of Trigger thường dùng cho Khung nhìn với các chức năng sau : - Cập nhật nhiều bảng một lúc trong một khung nhìn. - Tăng điều kiện ràng buộc trên các thuộc tính so với CHECK. - Đánh giá trạng thái của bảng trước hoặc sau khi cập nhật dữ liệu, và thực thi một số nhiệm vụ như in thông báo lỗi, sửa đổi bảng khác. - Cho phép một phần tập hợp câu lệnh bị từ chối trong khi các phần còn lại vẫn được thực thi thành công. INSTEAD OF trigger sử dụng các bảng logic inserted, deleted để lưu những thay đổi về dữ liệu khi trigger đang được thực thi. Ví dụ: Tạo view NV_DV lấy thông tin từ 2 bảng NhanVien và DonVi Create view NV_DV AS Select MasoNV,Hodem,Ten,ngaysinh,gioitinh,diachi,nv.MasoDV,Te nDV,MasoNQL From NhanVien nv, DonVi dv Where nv.MasoDV=dv.MasoDV Tạo trigger chen_NVDV trên view NV_DV dùng để chèn dữ liệu vào các bảng tương ứng khi chèn một bản ghi vào view. Create Trigger chen_NVDV On NV_DV Instead of Insert As Begin Insert into NhanVien (MasoNV,Hodem,Ten,ngaysinh,gioitinh,diachi,MasoDV) Select MasoNV,Hodem,Ten,ngaysinh,gioitinh,diachi,MasoDV From inserted; Insert into DonVi(MasoDV,TenDV,MasoNQL) Select MasoDV,TenDV,MasoNQL From inserted; End Bài tập Viết các trigger sau : 1. Khi thông tin nhân viên làm việc cho một dự án thay đổi, nếu tổng số giờ làm việc của nhân viên cho các dự án lớn hơn 40h/tuần, hoặc nhỏ hơn 20h/tuần thì hiển thị thông báo. 2. Khi một nhân viên bị xóa, hãy xóa các thông tin về dự án, người phụ thuộc liên quan đến nhân viên đó. Nếu nhân viên đó là người quản lý đơn vị, thì giá trị MaNQL=null, nếu nhân viên là người giám sát thì giá trị MaNGS của các nhân viên dưới sự giám sát của người này là null. 3. Khi chèn thêm một bản ghi hay cập nhật lương của một nhân viên thì đảm bảo lương nhân viên đó không lớn hơn lương của người giám sát và người quản lý đơn vị.