Category Archives: CSDL

Cở sở dữ liệu

Trigger in SQL Sever

Hôm nay chúng ta sẽ cùng tìm hiểu về Trigger, một trong những chủ đề cơ bản và quan trọng khi làm việc với SQL Sever.

Trigger được hiểu đơn giản là một thủ tục được thực thi từ phía máy chủ cơ sở dữ liệu (CSDL) khi có một sự kiện xãy ra như  Update, Insert hay Delete.

Trigger thường dùng để kiểm ra các ràng buộc toàn vẹn trên CSDL, và chúng được thực thi một cách tự động mà không cần sự can thiệp bằng các thao tác thủ công như kiểm tra dữ liệu, đồng bộ hóa dữ liệu,…

– Tạo Trigger

Cú pháp để tạo một Trigger cơ bản như sau:

1 CREATE TRIGGER trigger_name
2 ON table view -- Chỉ định bảng hoặc view sử dụng Trigger
3 FOR AFTER INSTEAD OF }
4 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } -- Các biến cố tự động kích hoạt Trigger
5 AS { sql_statement,...}

+Về các tham số { FOR | AFTER | INSTEAD OF }

Khi thực hiện một Trigger thì SQL tự động tạo ra 2 bảng Inserted và Deleted trong bộ nhớ chính và cục bộ cho mỗi Trigger, có nghĩa là khi áp dụng Trigger trên bảng nào thì bảng Inserted và Deleted sẽ được sử dụng riêng cho đó bảng đó.

Cấu trúc 2 bảng Inserted và Deleted được tạo ra sẽ giống hệt cấu trúc của bảng mà Trigger đang thực thi và chúng chỉ tồn tại trong thời gian Trigger đó thực thi mà thôi.

Ví dụ trên: 2 bảng Inserted và Deleted có cấu trúc giống với bảng CTHD (Bảng mà Trigger đang thực thi) gồm các cột: SoHD, MaSP, SL

Inserted là bảng chứa các dòng dữ liệu vừa được Insert hay Update vào bảng mà Trigger đang thực thi.
Deleted là bảng  chứa các dòng dữ liệu mới được xóa khỏi bảng bằng thao tác Delete hay Update.
* Khi thực hiện thao tác Update, thì đồng nghĩa với việc sẽ xóa những dòng dữ liệu cũ và thêm những dòng dữ liệu mới, khi đó tác Update sẽ vừa đồng thời thêm dữ liệu là các dòng mới vào 2 bảng Inserted và Deleted

FOR | AFTER

Đối với tham số For | After thì Trigger sẽ được gọi sau khi có thao tác Insert hoặc Update.
Thứ tự thực hiện là từ Database rồi đến bảng Inserted/Deleted
+ Khi đó dữ liệu vừa mới Insert/Update vào sẽ nằm trong cả 2 bảng: bảng chính trong Database và bảng Inserted.
+ Khi thực hiện xóa  một dòng dữ liệu thì dòng dữ liệu trên Database sẽ bị xóa, sau đó dòng bị xóa sẽ được thêm vào bảng Deleted.

INSTEAD OF

Khi sử dụng tham số Instead of thì Trigger sẽ bỏ qua việc tác động tới CSDL, thay vào đó nó thực hiện việc lưu dữ liệu vào bảng Inserted khi có thao tác Insert, lưu dữ liệu vào bảng Deleted đối với thao tác Delete.

Vì vậy, khi một dòng dữ liệu được thêm vào nó chỉ chứa trong bảng Inserted, và khi xóa một dòng dữ liệu thì nó đồng thời chứa trong bảng Deleted và vẫn còn tồn tại trong Database.
Trigger Instead of thường được dùng để cập nhật khung nhìn (View).

– SQL Statement: Nội dung Trigger bao gồm các câu lệnh dùng để thực thi Trigger

Sau đây chúng ta sẽ lấy ví dụ minh họa việc sử dụng Trigger trong SQL

Sử dụng lược đồ CSDL như sau:

KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)
NHANVIEN (MANV,HOTEN, NGVL, SODT)
SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA)
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD,MASP,SL)

Ràng buộc: Ngày mua hàng (NGHD) của một khách hàng thành viên sẽ lớn hơn hoặc bằng ngày khách hàng đó đăng ký thành viên (NGDK).

01 CREATE TRIGGER CHECK_NGAYNV --Tên Trigger
02 ON HOADON
03 FOR UPDATE,INSERT
04 AS
05     IF UPDATE(NGHD) --Kiểm tra việc cập nhật trên cột
06     BEGIN
07     DECLARE @NGHD SMALLDATETIME, @NGVL SMALLDATETIME
08     SET @NGHD=(SELECT NGHD FROM INSERTED)
09     SET @NGVL=(SELECT NGVL FROM NHANVIEN A,INSERTED B WHERE A.MANV=B.MANV)
10     IF(@NGHD<@NGVL)
11         BEGIN
12         PRINT 'NGHD PHAI LON HON NGVL'
13         ROLLBACK TRAN -- Câu lệnh quay lui khi thực hiện biến cố không thành công
14         END
15         END

+ Sử dụng cú pháp IF UPDATE để kiểm tra sự thay đổi trên cột, Trigger sẽ tự động thực hiện khi có thay đổi trên cột nhất định nào đó, thay vì chỉ định Trigger kích hoạt trên cả bảng.

1 IF UPDATE(Column_List) --Có thể kiểm tra trên một hay nhiều cột

+ Khai báo một biến cùng kiểu dữ liệu ta thực hiện câu lệnh:

1 DECLARE @Tên_biến Kiểu dữ liệu
1 DECLARE @NGHD SMALLDATETIME

+ Thiết lập giá trị cho một biến

1 SET @NGHD=(SELECT NGHD FROM INSERTED)

Thiết lập giá trị cho một biến có thể là một biểu thức hoặc một câu truy vấn.
Ở đây tôi sử dụng tham số FOR vì thế tôi có thể dễ dàng Select cột NGHD từ bảng Inserted mà không phải là bảng HOADON, như thế việc truy suất sẽ nhanh hơn vì ở đây bảng Inserted chỉ chứa dòng dữ liệu mới thêm vào, mà ta chỉ quan tâm đến những dòng dữ liệu mới thêm vào mà thôi.

+ Câu lệnh PRINT dùng để in thông báo lỗi ra màn hình.

1 PRINT 'THONG BAO LOI'

Hoặc sử dụng câu lệnh RAISERROR:

1 RAISERROR ('THONG BAO LOI')

Khi thực hiện một giao tác(transaction) không thành công thì sẽ tự động quay lui (Rolled Back) bằng cách chèn thêm câu lệnh:

1 ROLLBACK TRAN

Câu lệnh RollBack Tran còn thực hiện trong việc định nghĩa một Trigger cho biến cố Delete không cho xóa một dòng dữ liệu.
VD: Tạo Trigger cho biến cố Delete như sau

1 CREATE TRIGGER CANNOT_DELETE
2 ON HOADON
3 FOR DELETE
4 AS
5    ROLLBACK TRAN

Khi thực hiện xóa một dòng dữ liệu sẽ có thông báo lỗi

1 DELETE FROM HOADON WHERE MANV='NV01'

Sau khi cài đặt Trigger CHECK_NGAYNV, thử thêm một dòng dữ liệu có NGHD<NGDK:

1 INSERT INTO HOADON VALUES(1001,'23/07/2006','KH01','NV01',320000) -- Giả sử ngày DK là 27/07/2006

Ở đây NGHD=23/07/2006<27/07/2006
Kết quả :

 Đây là ví dụ rất cơ bản về cách cài đặt và sử dụng về Trigger trên một dòng dữ liệu. Bài viết tiếp theo chúng ta sẽ tìm hiểu về Trigger tác động đến nhiều dòng dữ liệu. See you next time!

http://trongthaonh.wordpress.com