Giáo trình SQL Server 2

sử trong cơ sở dữ liệu ta có 3 người dùng là A, B và C. A và B đều có 
quyền sử dụng và cấp phát quyền trên bảng R. A thực hiện lệnh sau để cấp phát quyền 
xem dữ liệu trên bảng R cho C: 
 GRANT SELECT 
 ON R TO C 
và B cấp phát quyền xem và bổ sung dữ liệu trên bảng R cho C bằng câu lệnh: 
GRANT SELECT, INSERT 
 35 
ON R TO C 
Như vậy, C có quyền xem và bổ sung dữ liệu trên bảng R. Bây giờ, nếu B thực hiện lệnh: 
REVOKE SELECT, INSERT 
ON R FROM C 
Người dùng C sẽ không còn quyền bổ sung dữ liệu trên bảng R nhưng vẫn có thể xem 
được dữ liệu của bảng này (quyền này do A cấp cho C và vẫn còn hiệu lực). 
Nếu ta đã cấp phát quyền cho người dùng nào đó bằng câu lệnh GRANT với tuỳ chọn 
WITH GRANT OPTION thì khi thu hồi quyền bằng câu lệnh REVOKE phải chỉ định 
tuỳ chọn CASCADE. Trong trường hợp này, các quyền được chuyển tiếp cho những 
người dùng khác cũng đồng thời được thu hồi. 
Ví dụ 4.6: Ta cấp phát cho người dùng A trên bảng R với câu lệnh GRANT như sau: 
GRANT SELECT 
ON R TO A 
WITH GRANT OPTION 
sau đó người dùng A lại cấp phát cho người dùng B quyền xem dữ liệu trên R với câu lệnh: 
GRANT SELECT 
ON R TO B 
Nếu muốn thu hồi quyền đã cấp phát cho người dùng A, ta sử dụng câu lệnh REVOKE 
như sau: 
REVOKE SELECT 
ON NHANVIEN 
FROM A CASCADE 
Câu lệnh trên sẽ đồng thời thu hồi quyền mà A đã cấp cho B và như vậy cả A và B đều 
không thể xem được dữ liệu trên bảng R Trong trường hợp cần thu hồi các quyền đã 
được chuyển tiếp và khả năng chuyển tiếp các quyền đối với những người đã được 
cấp phát quyền với tuỳ chọn 
WITH GRANT OPTION, trong câu lệnh REVOKE ta chỉ định mệnh đề GRANT 
OPTION FOR. 
Ví dụ 4.7: Trong ví dụ trên, nếu ta thay câu lệnh: 
REVOKE SELECT 
ON NHANVIEN 
FROM A CASCADE 
 36 
bởi câu lệnh: 
REVOKE GRANT OPTION FOR SELECT 
ON NHANVIEN 
FROM A CASCADE 
Thì B sẽ không còn quyền xem dữ liệu trên bảng R đồng thời A không thể chuyển tiếp 
quyền mà ta đã cấp phát cho những người dùng khác (tuy nhiên A vẫn còn quyền xem 
dữ liệu trên bảng R). 
4.3.2 Thu hồi quyền thực thi các câu lênh: 
Việc thu hồi quyền thực thi các câu lệnh trên cơ sở dữ liệu (CREATE DATABASE, 
CREATE TABLE, CREATE VIEW,...) được thực hiện đơn giản với câu lệnh 
REVOKE có cú pháp: 
REVOKE ALL | các_câu_lệnh_cần_thu_hồi 
FROM danh_sách_người_dùng 
Ví dụ 4.8: Để không cho phép người dùng thuchanh thực hiện lệnh CREATE TABLE 
trên cơ sở dữ liệu, ta sử dụng câu lệnh: 
REVOKE CREATE TABLE 
FROM thuchanh 
 37 
CHƯƠNG V GIAO TÁC SQL 
5.1 Giao tác và các tính chất của giao tác 
Một giao tác (transaction) là một chuỗi một hoặc nhiều câu lệnh SQL được kết hợp lại 
với nhau thành một khối công việc. Các câu lệnh SQL xuất hiện trong giao tác thường 
có mối quan hệ tương đối mật thiết với nhau và thực hiện các thao tác độc lập. 
Việc kết hợp các câu lệnh lại với nhau trong một giao tác nhằm đảm bảo tính toàn vẹn 
dữ liệu và khả năng phục hồi dữ liệu. Trong một giao tác, các câu lệnh có thể độc lập 
với nhau nhưng tất cả các câu lệnh trong một giao tác đòi hỏi hoặc phải thực thi trọn 
vẹn hoặc không một câu lệnh nào được thực thi. 
Các cơ sở dữ liệu sử dụng nhật ký giao tác (transaction log) để ghi lại các thay đổi mà 
giao tác tạo ra trên cơ sở dữ liệu và thông qua đó có thể phục hồi dữ liệu trong trường 
hợp gặp lỗi hay hệ thống có sự cố. 
Một giao tác đòi hỏi phải có được bồn tính chất sau đây: 
• Tính nguyên tử (Atomicity): Mọi thay đổi về mặt dữ liệu hoặc phải được thực 
hiện trọn vẹn khi giao tác thực hiện thành công hoặc không có bất kỳ sự thay đổi nào 
về dữ liệu xảy ra nếu giao tác không thực hiện được trọn vẹn. Nói cách khác, tác dụng 
của các câu lệnh trong một giao tác phải như là một câu lệnh đơn. 
• Tính nhất quán (Consistency): Tính nhất quan đòi hỏi sau khi giao tác kết thúc, 
 cho dù là thành công hay bị lỗi, tất cả dữ liệu phải ở trạng thái nhất quán (tức là 
 sự toàn vẹn dữ liệu phải luôn được bảo toàn). 
• Tính độc lập (Isolation): Tính độc lập của giao tác có nghĩa là tác dụng của mỗi 
 một giao tác phải giống như khi chỉ mình nó được thực hiện trên chính hệ thống 
 đó. Nói cách khác, một giao tác khi được thực thi đồng thời với những giao tác 
 khác trên cùng hệ thống không chịu bất kỳ sự ảnh hưởng nào của các giao tác đó. 
• Tính bền vững (Durability): Sau khi một giao tác đã thực hiện thành công, mọi tác 
 dụng mà nó đã tạo ra phải tồn tại bền vững trong cơ sở dữ liệu, cho dù là hệ thống 
 có bị lỗi đi chăng nữa. 
5.2 Mô hình giao tác trong SQL 
Giao tác SQL được định nghĩa dựa trên các câu lệnh xử lý giao tác sau đây: 
• BEGIN TRANSACTION: Bắt đầu một giao tác 
 38 
• SAVE TRANSACTION:Đánh dấu một vị trí trong giao tác (gọi là điểm đánh dấu). 
• ROLLBACK TRANSACTION: Quay lui trở lại đầu giao tác hoặc một điểm đánh 
 dấu trước đó trong giao tác. 
• COMMIT TRANSACTION: Đánh dấu điểm kết thúc một giao tác. Khi câu lệnh 
 này thực thi cũng có nghĩa là giao tác đã thực hiện thành công. 
• ROLLBACK [WORK]: Quay lui trở lại đầu giao tác. 
• COMMIT [WORK]: Đánh dấu kết thúc giao tác. 
Một giao tác trong SQL được bắt đấu bởi câu lệnh BEGIN TRANSACTION. Câu 
lệnh này đánh dấu điểm bắt đầu của một giao tác và có cú pháp như sau: 
 BEGIN TRANSACTION [tên_giao_tác] 
Một giao tác sẽ kết thúc trong các trường hợp sau: 
Câu lệnh COMMIT TRANSACTION (hoặc COMMIT WORK) được thực thi. Câu 
lệnh này báo hiệu sự kết thúc thành công của một giao tác. Sau câu lệnh này, một giao 
tác mới sẽ được bắt đầu. 
• Khi câu lệnh ROLLBACK TRANSACTION (hoặc ROLLBACK WORK) được 
 thực thi để huỷ bỏ một giao tác và đưa cơ sở dữ liệu về trạng thái như trước khi 
 giao tác bắt đầu. Một giao tác mới sẽ bắt đầu sau khi câu lệnh ROLLBACK được 
 thực thi. 
• Một giao tác cũng sẽ kết thúc nếu trong quá trình thực hiện gặp lỗi (chẳng hạn hệ 
 thống gặp lỗi, kết nối mạng bị “đứt”,...). Trong trường hợp này, hệ thống sẽ tự 
 động phục hồi lại trạng thái cơ sở dữ liệu như trước khi giao tác bắt đầu (tương tự 
 như khi câu lệnh ROLLBACK được thực thi để huỷ bỏ một giao tác). Tuy nhiên, 
 trong trường hợp này sẽ không có giao tác mới được bắt đầu. 
Ví dụ 5.1: Giao tác dưới đây kết thúc do lệnh ROLLBACK TRANSACTION và mọi 
thay đổi vể mặt dữ liệu mà giao tác đã thực hiện (UPDATE) đều không có tác dụng. 
BEGIN TRANSACTION giaotac1 
UPDATE monhoc SET sodvht=4 WHERE sodvht=3 
UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL 
ROLLBACK TRANSACTION giaotac1 
còn giao tác dưới đây kết thúc bởi lệnh COMMIT và thực hiện thành công việc cập 
nhật dữ liệu trên các bảng MONHOC và DIEMTHI. 
BEGIN TRANSACTION giaotac2 
 39 
UPDATE monhoc SET sodvht=4 WHERE sodvht=3 
UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL 
COMMIT TRANSACTION giaotac2 
Câu lệnh: 
SAVE TRANSACTION tên_điểm_dánh_dấu 
được sử dụng để đánh dấu một vị trí trong giao tác. Khi câu lệnh này được thực thi, 
trạng thái của cơ sở dữ liệu tại thời điểm đó sẽ được ghi lại trong nhật ký giao tác. 
Trong quá trình thực thi giao tác có thể quay trở lại một điểm đánh dấu bằng cách sử 
dụng câu lệnh: 
ROLLBACK TRANSACTION tên_điểm_đánh_dấu 
Trong trường hợp này, những thay đổi về mặt dữ liệu mà giao tác đã thực hiện từ điểm 
đánh dấu đến trước khi câu lệnh ROLLBACK được triệu gọi sẽ bị huỷ bỏ. Giao tác sẽ 
được tiếp tục với trạng thái cơ sở dữ liệu có được tại điểm đánh dấu . Hình 6.2 mô tả 
cho ta thấy hoạt động của một giao tác có sử dụng các điểm đánh dấu: 
Sau khi câu lệnh ROLLBACK TRANSACTION được sử dụng để quay lui lại một 
điểm đánh dấu trong giao tác, giao tác vẫn được tiếp tục với các câu lệnh sau đó. 
Nhưng nếu câu lệnh này được sử dụng để quay lui lại đầu giao tác (tức là huỷ bỏ giao 
tác), giao tác sẽ kết thúc và do đó câu lệnh COMMIT TRANSACTION trong trường 
hợp này sẽ gặp lỗi. 
Ví dụ 5.2: Câu lệnh COMMIT TRANSACTION trong giao tác dưới đây kết thúc 
thành công một giao tác 
BEGIN TRANSACTION giaotac3 
UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL 
SAVE TRANSACTION a 
UPDATE monhoc SET sodvht=4 WHERE sodvht=3 
ROLLBACK TRANSACTION a 
UPDATE monhoc SET sodvht=2 WHERE sodvht=3 
COMMIT TRANSACTION giaotac3 
và trong ví dụ dưới đây, câu lệnh COMMIT TRANSACTION gặp lỗi: 
BEGIN TRANSACTION giaotac4 
UPDATE diemthi SET diemlan2=0 WHERE diemlan2 IS NULL 
SAVE TRANSACTION a 
 40 
UPDATE monhoc SET sodvht=4 WHERE sodvht=3 
ROLLBACK TRANSACTION giaotac4 
UPDATE monhoc SET sodvht=2 WHERE sodvht=3 
COMMIT TRANSACTION giaotac4 
5.3 Giao tác lồng nhau 
Các giao tác trong SQL có thể được lồng vào nhau theo từng cấp. Điều này thường 
gặp đối với các giao tác trong các thủ tục lưu trữ được gọi hoặc từ một tiến trình trong 
một giao tác khác. 
Ví dụ dưới đây minh hoạ cho ta trường hợp các giao tác lồng nhau. 
Ví dụ 5.3: Ta định nghĩa bảng T như sau: 
CREATE TABLE T 
( 
A INT PRIMARY KEY, 
B INT 
) 
và thủ tục sp_TransEx: 
CREATE PROC sp_TranEx(@a INT,@b INT) 
AS 
BEGIN 
BEGIN TRANSACTION T1 
IF NOT EXISTS (SELECT * FROM T WHERE A=@A ) 
INSERT INTO T VALUES(@A,@B) 
IF NOT EXISTS (SELECT * FROM T WHERE A=@A+1) 
INSERT INTO T VALUES(@A+1,@B+1) 
COMMIT TRANSACTION T1 
END 
Lời gọi đến thủ tuch sp_TransEx được thực hiện trong một giao tác khác như sau: 
BEGIN TRANSACTION T3 
EXECUTE sp_tranex 10,20 
ROLLBACK TRANSACTION T3 
Trong giao tác trên, câu lệnh ROLLBACK TRANSACTION T3 huỷ bỏ giao tác và do 
đó tác dụng của lời gọi thủ tục trong giao tác không còn tác dụng, tức là không có 
 41 
dòng dữ liệu nào mới được bổ sung vào bảng T (cho dù giao tác T1 trong thủ tục 
sp_tranex đã thực hiện thành công với lệnh COMMIT TRANSACTION T1). 
Ta xét tiếp một trường hợp của một giao tác khác trong đó có lời gọi đến thủ tục 
sp_tranex như sau: 
BEGIN TRANSACTION 
EXECUTE sp_tranex 20,40 
SAVE TRANSACTION a 
EXECUTE sp_tranex 30,60 
ROLLBACK TRANSACTION a 
EXECUTE sp_tranex 40,80 
COMMIT TRANSACTION 
sau khi giao tác trên thực hiện xong, dữ liệu trong bảng T sẽ là: 
A B 
20 40 
21 41 
40 80 
41 81 
Như vậy, tác dụng của lời gọi thủ tục sp_tranex 30,60 trong giao tác đã bị huỷ bỏ bởi 
câu lệnh ROLLBACK TRANSACTION trong giao tác. 
Như đã thấy trong ví dụ trên, khi các giao tác SQL được lồng vào nhau, giao tác ngoài 
cùng nhất là giao tác có vai trò quyết định. Nếu giao tác ngoài cùng nhất được uỷ thác 
(commit) thì các giao tác được lồng bên trong cũng đồng thời uỷ thác; Và nếu giao 
tác ngoài cùng nhất thực hiện lệnh ROLLBACK thì những giao tác lồng bên trong 
cũng chịu tác động của câu lệnh này (cho dù những giao tác lồng bên trong đã thực 
hiện lệnh COMMIT TRANSACTION). 
 42 
 MỤC LỤC 
CHƯƠNG I THỦ TỤC LƯU TRỮ ........................................................................................... 1 
 1.1 Thủ tục lưu trữ (stored procedure).................................................................................... 2 
 1.1.1 Các khái niệm ............................................................................................................ 2 
 1.1.2 Tạo thủ tục lưu trữ ..................................................................................................... 3 
 1.1.3 Lời gọi thủ tục lưu trữ ............................................................................................... 5 
 1.1.4 Sử dụng biến trong thủ tục ........................................................................................ 5 
 1.1.5 Giá trị trả về của tham số trong thủ tục lưu trữ ......................................................... 6 
 1.1.6 Tham số với giá trị mặc định ..................................................................................... 7 
 1.1.7 Sửa đổi thủ tục ........................................................................................................... 8 
 1.1.8 Xoá thủ tục ................................................................................................................ 9 
CHƯƠNG II TRIGGER ........................................................................................................... 10 
 2.1 Định nghĩa trigger ........................................................................................................... 10 
 2.2 Sử dụng mệnh đề IF UPDATE trong trigger .................................................................. 13 
 2.3 ROLLBACK TRANSACTION và trigger ..................................................................... 14 
 2.4 Sử dụng trigger trong trường hợp câu lệnh INSERT, UPDATE và DELETE có tác động 
 đến nhiều dòng dữ liệu ......................................................................................................... 16 
 2.4.1 Sử dụng truy vấn con .............................................................................................. 16 
 2.4.2 Sử dụng biến con trỏ ................................................................................................ 19 
CHƯƠNG III HÀM................................................................................................................ 22 
 3.1. Hàm do người dùng định nghĩa ..................................................................................... 22 
 3.1.1 Định nghĩa và sử dụng hàm ..................................................................................... 22 
 3.1.2 Hàm với giá trị trả về là “dữ liệu kiểu bảng” .......................................................... 23 
Bài tập ....................................................................................................................................... 28 
CHƯƠNG IV BẢO MẬT TRONG SQL ................................................................................. 30 
 4.1 Các khái niệm ................................................................................................................. 30 
 4.2 Cấp phát quyền ............................................................................................................... 31 
 4.2.1 Cấp phát quyền cho người dùng trên các đối tượng cơ sở dữ liệu .......................... 31 
 4.2.2 Cấp phát quyền thực thi các câu lệnh ...................................................................... 33 
 4.3 Thu hồi quyền ................................................................................................................. 34 
 4.3.1 Thu hồi quyền trên đối tượng cơ sở dữ liệu: ........................................................... 34 
 4.3.2 Thu hồi quyền thực thi các câu lênh: ....................................................................... 37 
CHƯƠNG V GIAO TÁC SQL ................................................................................................ 38 
 5.1 Giao tác và các tính chất của giao tác ............................................................................. 38 
 5.2 Mô hình giao tác trong SQL .......................................................................................... 38 
 5.3 Giao tác lồng nhau .......................................................................................................... 41 
 43