Trigger
Ta đã biết các ràng buộc được sử dụng để đảm bảo tính toàn vẹn dữ liệu trong cơ sở dữ liệu. Một đối tượng khác cũng thường được sử dụng trong các cơ sở dữ liệu cũng với mục đích này là các trigger. Cũng tương tự như thủ tục lưu trữ, một trigger là một đối tượng chứa một tập các câu lệnh SQL và tập các câu lệnh này sẽ được thực thi khi trigger được gọi. Điểm khác biệt giữa thủ tục lưu trữ và trigger là: các thủ tục lưu trữ được thực thi khi người sử dụng có lời gọi đến chúng còn các trigger lại được "gọi" tự động khi xảy ra những giao tác làm thay đổi dữ liệu trong các bảng.
Mỗi một trigger được tạo ra và gắn liền với một bảng nào đó trong cơ sở dữ liệu. Khi dữ liệu trong bảng bị thay đổi (tức là khi bảng chịu tác động của các câu lệnh INSERT, UPDATE hay DELETE) thì trigger sẽ được tự đông kích hoạt.
Sử dụng trigger một cách hợp lý trong cơ sở dữ liệu sẽ có tác động rất lớn trong việc tăng hiệu năng của cơ sở dữ liệu. Các trigger thực sự hữu dụng với những khả năng sau:
- Một trigger có thể nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong cơ sở dữ liệu.
- Các thao tác trên dữ liệu (xoá, cập nhật và bổ sung) có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác trên cơ sở dữ liệu nhằm đảm bảo tính hợp lệ của dữ liệu.
- Thông qua trigger, ta có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong cơ sở dữ liệu mà bản thân các ràng buộc không thể thực hiện được.
Định nghĩa trigger
Một trigger là một đối tượng gắn liền với một bảng và được tự động kích hoạt khi xảy ra những giao tác làm thay đổi dữ liệu trong bảng. Định nghĩa một trigger bao gồm các yếu tố sau:
- Trigger sẽ được áp dụng đối với bảng nào?
- Trigger được kích hoạt khi câu lệnh nào được thực thi trên bảng: INSERT, UPDATE, DELETE?
- Trigger sẽ làm gì khi được kích hoạt?
Câu lệnh CREATE TRIGGER được sử dụng để đinh nghĩa trigger và có cú pháp như sau:
CREATE TRIGGER tên_trigger
ON tên_bảng
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_trigger
Ví dụ : Ta định nghĩa các bảng như sau:
Bảng MATHANG lưu trữ dữ liệu về các mặt hàng:
CREATE TABLE mathang
(
mahang NVARCHAR(5) PRIMARY KEY, /*mã hàng*/
tenhang NVARCHAR(50) NOT NULL, /*tên hàng*/
soluong INT, /*số lượng hàng hiện có*/
)
Bảng NHATKYBANHANG lưu trữ thông tin về các lần bán hàng
CREATE TABLE nhatkybanhang
(
stt INT IDENTITY PRIMARY KEY,
ngay DATETIME, /*ngày bán hàng*/
nguoimua NVARCHAR(30), /*tên người mua hàng*/
mahang NVARCHAR(5) /*mã mặt hàng được bán*/
FOREIGN KEY REFERENCES mathang(mahang),
soluong INT, /*giá bán hàng*/
giaban MONEY /*số lượng hàng được bán*/
)
Câu lệnh dưới đây định nghĩa trigger trg_nhatkybanhang_insert. Trigger này có chức năng tự động giảm số lượng hàng hiện có khi một mặt hàng nào đó được bán (tức là khi câu lệnh INSERT được thực thi trên bảng NHATKYBANHANG).
CREATE TRIGGER trg_nhatkybanhang_insert
ON nhatkybanhang
FOR INSERT
AS
UPDATE mathang
SET mathang.soluong=mathang.soluong-inserted.soluong
FROM mathang INNER JOIN inserted
ON mathang.mahang=inserted.mahang
Với trigger vừa tạo ở trên, nếu dữ liệu trong bảng MATHANG là:
thì sau khi ta thực hiện câu lênh:
INSERT INTO nhatkybanhang
(ngay,nguoimua,mahang,soluong,giaban)
VALUES('5/5/2004','Tran Ngoc Thanh','H1',10,5200)
Trong câu lệnh CREATE TRIGGER ở ví dụ trên, sau mệnh đề ON là tên của bảng mà trigger cần tạo sẽ tác động đến. Mệnh đề tiếp theo chỉ định câu lệnh sẽ kích hoạt trigger (FOR INSERT). Ngoài INSERT, ta còn có thể chỉ định UPDATE hoặc DELETE cho mệnh đề này, hoặc có thể kết hợp chúng lại với nhau. Phần thân của trigger nằm sau từ khoá AS bao gồm các câu lệnh mà trigger sẽ thực thi khi được kích hoạt.
Chuẩn SQL định nghĩa hai bảng logic INSERTED và DELETED để sử dụng trong các trigger. Cấu trúc của hai bảng này tương tự như cấu trúc của bảng mà trigger tác động. Dữ liệu trong hai bảng này tuỳ thuộc vào câu lệnh tác động lên bảng làm kích hoạt trigger; cụ thể trong các trường hợp sau:
- Khi câu lệnh DELETE được thực thi trên bảng, các dòng dữ liệu bị xoá sẽ được sao chép vào trong bảng DELETED. Bảng INSERTED trong trường hợp này không có dữ liệu.
- Dữ liệu trong bảng INSERTED sẽ là dòng dữ liệu được bổ sung vào bảng gây nên sự kích hoạt đối với trigger bằng câu lệnh INSERT. Bảng DELETED trong trường hợp này không có dữ liệu.
- Khi câu lệnh UPDATE được thực thi trên bảng, các dòng dữ liệu cũ chịu sự tác động của câu lệnh sẽ được sao chép vào bảng DELETED, còn trong bảng INSERTED sẽ là các dòng sau khi đã được cập nhật.
Sử dụng mệnh đề IF UPDATE trong trigger
Thay vì chỉ định một trigger được kích hoạt trên một bảng, ta có thể chỉ định trigger được kích hoạt và thực hiện những thao tác cụ thể khi việc thay đổi dữ liệu chỉ liên quan đến một số cột nhất định nào đó của cột. Trong trường hợp này, ta sử dụng mệnh đề IF UPDATE trong trigger. IF UPDATE không sử dụng được đối với câu lệnh DELETE.
Ví dụ 5.13: Xét lại ví dụ với hai bảng MATHANG và NHATKYBANHANG, trigger dưới đây được kích hoạt khi ta tiến hành cập nhật cột SOLUONG cho một bản ghi của bảng NHATKYBANHANG (lưu ý là chỉ cập nhật đúng một bản ghi)
CREATE TRIGGER trg_nhatkybanhang_update_soluong
ON nhatkybanhang
FOR UPDATE
AS
IF UPDATE(soluong)
UPDATE mathang
SET mathang.soluong = mathang.soluong -
(inserted.soluong-deleted.soluong)
FROM (deleted INNER JOIN inserted ON
deleted.stt = inserted.stt) INNER JOIN mathang
ON mathang.mahang = deleted.mahang
Với trigger ở ví dụ trên, câu lệnh:
UPDATE nhatkybanhang
SET soluong=soluong+20
WHERE stt=1
sẽ kích hoạt trigger ứng với mệnh đề IF UPDATE (soluong) và câu lệnh UPDATE trong trigger sẽ được thực thi. Tuy nhiên câu lệnh:
UPDATE nhatkybanhang
SET nguoimua='Mai Hữu Toàn'
WHERE stt=3
lại không kích hoạt trigger này.
Mệnh đề IF UPDATE có thể xuất hiện nhiều lần trong phần thân của trigger. Khi đó, mệnh đề IF UPDATE nào đúng thì phần câu lệnh của mệnh đề đó sẽ được thực thi khi trigger được kích hoạt.
Ví dụ : Giả sử ta định nghĩa bảng R như sau:
CREATE TABLE R
(
A INT,
B INT,
C INT
)
và trigger trg_R_update cho bảng R:
CREATE TRIGGER trg_R_test
ON R
FOR UPDATE
AS
IF UPDATE(A)
Print 'A updated'
IF UPDATE(C)
Print 'C updated'
Câu lệnh:
UPDATE R SET A=100 WHERE A=1
sẽ kích hoạt trigger và cho kết quả là:
A updated
và câu lệnh:
UPDATE R SET C=100 WHERE C=2
cũng kích hoạt trigger và cho kết quả là:
C updated
còn câu lệnh:
UPDATE R SET B=100 WHERE B=3
hiển nhiên sẽ không kích hoạt trigger