Thủ tục trong SQl

www.themegallery.com
Bài 2: Trigger
Trang 1
1. Giới thiệu
- Trigger là một dạng đặc biệt của thủ tục lưu trữ
- Trigger không có tham số
- Không gọi thực hiện bằng lệnh Exec, mà tự động kích hoạt khi dữ liệu trên bảng có liên quan đến Trigger được cập nhật
- Một Trigger được tạo cho một bảng và dùng để kiểm tra các ràng buột toàn vẹn phức tạp hoặc cập nhật dữ liệu của các bảng liên quan
www.themegallery.com
Bài 2: Trigger
Trang 2
2. Tạo trigger
Cú pháp:
CREATE TRIGGER Tên_trigger ON Tên_Bảng/Tên View
FOR Insert[,Update,Delete]
AS
[IF UPDATE(tên_cột)
[AND UPDATE(tên_cột)|OR UPDATE(tên_cột)]
...]
Các_câu_lệnh_của_thủ_tục
www.themegallery.com
Bài 2: Trigger
Trang 3
• Tên_Bảng / Tên_View: là tên bảng hoặc tên view (khung nhìn) mà Trigger được tạo
Trong đó:
• Tên_trigger: Tên của trigger cần tạo
www.themegallery.com
Bài 2: Trigger
Trang 4
Các thức hoạt động của Trigger:
- Trong Trigger, SQL tự động tạo 2 bảng là INSERTED và DELETED
- Cấu trúc của 2 table tương tự như cấu trúc của table mà trigger tác động
www.themegallery.com
Bài 2: Trigger
Trang 5
3. Sử dụng mệnh đề IF UPDATE trong trigger
- Sử dụng mệnh đề IF UPDATE để thực hiện một số thao tác dữ liệu liên quan đến một số cột nhất định nào đó
- Mệnh đề IF UPDATE không sử dụng được với câu lệnh DELETE
www.themegallery.com
Bài 2: Trigger
Trang 6
3. Sử dụng mệnh đề IF UPDATE trong trigger
- Ví dụ
CREATE TABLE Vidu
(
A INT,
B INT,
C INT
)
CREATE TRIGGER trg_Vidu_test ON Vidu
FOR UPDATE
AS
IF UPDATE(A)
Print `A updated`
IF UPDATE(C)
Print `C updated`
UPDATE Vidu SET A=100 WHERE A=1
Câu lệnh kích hoạt trigger và hiện kết quả: A updated
www.themegallery.com
Bài 2: Trigger
Trang 7
4. ROLLBACK TRANSACTION và trigger
- Một trigger có khả năng nhận biết sự thay đổi dữ liệu trên một bảng, do đó nó có khả năng phát hiện và hủy bỏ những thao tác không đảm bảo tính toàn vẹn dữ liệu
- Để hủy bỏ câu lệnh làm kích hoạt trigger, sử dụng câu lệnh:
ROLLBACK TRANSACTION
ROLLBACK TRAN
Hoặc
www.themegallery.com
Bài 2: Trigger
Trang 8
4. ROLLBACK TRANSACTION và trigger
- Ví dụ:
Giả sử có bảng sinhvien(masv,hoten,quequan,namsinh). Cần xóa 1 sinh viên từ bảng
CREATE TRIGGER tg_delete_SV ON sinhvien
FOR DELETE
AS
Rollback Tran
www.themegallery.com
Bài 2: Trigger
Trang 9
5. Sử dụng trigger trong trường hợp câu lệnh INSERT, UPDATE và DELETE tác động đến nhiều dòng dữ liệu
- Khi thao tác INSERT, UPDATE, DELETE trên nhiều dòng dữ liệu, nếu sử dụng câu lệnh thông thường sẽ gây sai lệch dữ liệu.
- Có 2 cách khắc phục:
+ Sử dụng truy vấn con
+ Sử dụng biến con trỏ
www.themegallery.com
Bài 2: Trigger
Trang 10
5.1. Sử dụng truy vấn con
a. INSERT trigger
b. UPDATE trigger
c. DELETE trigger
www.themegallery.com
Ví dụ về INSERT
Trang 11
Cho CSDL Quản lý bán hàng như sau:
www.themegallery.com
Ví dụ
Trang 12
Tạo trigger kiểm tra điều kiện hợp lệ của dữ liệu là số lượng hàng bán ra phải nhỏ hơn hoặc bằng số lượng hàng hiện có. Nếu điều kiện này không thoả mãn thì huỷ bỏ thao tác bổ sung dữ liệu.
www.themegallery.com
Trang 13
CREATE TRIGGER trg_nhatkybanhang_insert
ON NHATKYBANHANG
FOR INSERT
AS
DECLARE @sl_co int /* Số lượng hàng hiện có */
DECLARE @sl_ban int /* Số lượng hàng được bán */
DECLARE @mahang nvarchar(5) /* Mã hàng được bán */
SELECT @mahang=mahang,@sl_ban=soluong FROM inserted
SELECT @sl_co = soluong FROM mathang where mahang=@mahang
/*Nếu số lượng hàng hiện có nhỏ hơn số lượng bán
thì huỷ bỏ thao tác bổ sung dữ liệu */
(1) Cách sử dụng và ý nghĩa của câu lệnh ROLLBACK TRANSACTION để bỏ qua thao tác SQL vừa thực thi.
IF @sl_co<@sl_ban
ROLLBACK TRANSACTION
/* Nếu dữ liệu hợp lệ
thì giảm số lượng hàng hiện có */
ELSE
UPDATE mathang
SET soluong=soluong-@sl_ban
WHERE mahang=@mahang