Giáo trình mô đun Hệ quản trị cơ sở dữ liệu SQL

NVARCHAR(200) NULL , 
noidung NTEXT NULL , maphanloai INT NULL FOREIGN KEY 
REFERENCES loaibantin(maphanloai) 
) 
Trong bảng LOAIBANTIN, giá trị cột BANTINMOINHAT cho biết mã số của bản 
tin thuộc loại tương ứng mới nhất (được bổ sung sau cùng). 
Hãy viết các trigger cho bảng BANTIN sao cho: 
•Khi một bản tin mới được bổ sung, cập nhật lại cột BANTINMOINHAT của 
dòng tương ứng với loại bản tin vừa bổ sung. 
Trang 109 
•Khi một bản tin bị xoá, cập nhật lại giá trị của cột BANTINMOINHAT trong 
bảng LOAIBANTIN của dòng ứng với loại bản tin vừa xóa là mã số của bản tin trước 
đó (dựa vào ngày đưa tin). Nếu không còn bản tin nào cùng loại thì giá trị của cột này 
bằng 0. 
•Khi cập nhật lại mã số của một bản tin và nếu đó là bản tin mới nhất thì cập nhật 
lại giá trị cột BANTINMOINHAT là mã số mới. 
5.1 
CREATE PROCEDURE sp_insert_mathang( @mahang @tenhang 
NVARCHAR(50), @macongty NVARCHAR(10) = NULL, 
@maloaihang 
INT = NULL, @soluong INT = 0, @donvitinh NVARCHAR(20) 
= 
NULL, @giahang money = 0) AS IF NOT EXISTS(SELECT 
mahang 
FROM mathang WHERE mahang=@mahang) IF (@macongty IS 
NULL 
OR EXISTS(SELECT macongty FROM nhacungcap WHERE 
macongty=@macongty)) AND (@maloaihang IS NULL OR 
EXISTS(SELECT maloaihang FROM loaihang WHERE 
maloaihang=@maloaihang)) INSERT INTO mathang 
VALUES(@mahang,@tenhang, @macongty,@maloaihang, 
@soluong,@donvitinh,@giahang) 
5.2 
CREATE PROCEDURE sp_thongkebanhang(@mahang 
NVARCHAR(10)) 
AS SELECT mathang.mahang,tenhang, 
SUM(chitietdathang.soluong) AS tongsoluong FROM 
mathang 
LEFT OUTER JOIN chitietdathang ON 
mathang.mahang=chitietdathang.mahang WHERE 
mathang.mahang=@mahang GROUP BY mathang.mahang,tenhang 
5.3 Định nghĩa hàm 
CREATE FUNCTION func_banhang() RETURNS TABLE AS RETURN 
(SELECT mathang.mahang,tenhang, CASE WHEN 
sum(chitietdathang.soluong) IS NULL THEN 0 ELSE 
sum(chitietdathang.soluong) END AS tongsl FROM mathang 
LEFT OUTER JOIN chitietdathang ON mathang.mahang = 
chitietdathang.mahang GROUP BY mathang.mahang,tenhang) 
Sử dụng hàm đã định nghĩa 
Trang 110 
SELECT a.mahang,a.tenhang,soluong+tongsl FROM mathang 
AS a 
INNER JOIN dbo.func_banhang() AS b ON 
a.mahang=b.mahang 
5.4 Định nghĩa hàm 
CREATE TRIGGER trg_chitietdathang_insert ON 
chitietdathang 
FOR INSERT AS BEGIN DECLARE @mahang NVARCHAR(100) 
DECLARE 
@soluongban INT DECLARE @soluongcon INT SELECT 
@mahang=mahang,@soluongban=soluong FROM inserted 
SELECT 
@soluongcon=soluong FROM mathang WHERE mahang=@mahang 
IF 
@soluongcon>=@soluongban UPDATE mathang SET 
soluong=soluong@soluongban WHERE mahang=@mahang ELSE 
ROLLBACK TRANSACTION END CREATE TRIGGER 
trg_chitietdathang_update_soluong ON chitietdathang 
FOR 
UPDATE AS IF UPDATE(soluong) BEGIN IF EXISTS SELECT 
sohoadon FROM inserted WHERE soluong 0 ROLLBACK 
TRANSACTION ELSE BEGIN UPDATE mathang SET 
soluong=soluong 
(SELECT SUM(inserted.soluong ,deleted.soluong) FROM 
inserted INNER JOIN deleted ON 
inserted.sohoadon=deleted.sohoadon AND 
inserted.mahang=deleted.mahang WHERE 
inserted.mahang=mathang.mahang GROUP BY 
inserted.mahang) WHERE mahang IN (SELECT DISTINCT mahang 
FROM inserted) IF 
EXISTS SELECT mahang FROM mathang WHERE soluong 0 
ROLLBACK 
TRANSACTION END END 
5.5 
CREATE TRIGGER trg_chitietdathang_giaban ON 
chitietdathang 
FOR INSERT,UPDATE AS IF UPDATE(giaban) IF 
EXISTS(SELECT inserted.mahang FROM mathang INNER JOIN 
inserted ON 
mathang.mahang=inserted.mahang WHERE 
mathang.giahang>inserted.giaban) ROLLBACK TRANSACTION 
Trang 111 
Bài 6. Giao dịch SQL 
Một khái niệm quan trọng là khái niệm giao dịch (Transaction). Các tính chất 
một giao dịch phải có để đảm bảo một HQTCSDL, được xây dựng trên HCSDL tương 
ứng, trong suốt quá trình hoạt động sẽ luôn cho một CSDL tin cậy (dữ liệu luôn nhất 
quán). Quản trị giao dịch nhằm đảm bảo mọi giao dịch trong hệ thống có các tính chất 
mà một giao dịch phải có. Một điều cần chú ý là trong các tính chất của một giao dịch, 
tính chất nhất quán trước hết phải được đảm bảo bởi người lập trình- người viết ra 
giao dịch. 
1 Giao dịch và các tính chất của giao dịch 
Một giao dịch (transaction) là một hoặc một chuỗi 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 
dịch 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 dịch 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 dịch, 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 dịch đò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 dịch (transaction log) để ghi lại các thay 
đổi mà giao dịch 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 dịch đò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 dịch 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 dịch 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 dịch 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 dịch 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 dịch có nghĩa là tác dụng của 
mỗi một giao dịch 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 dịch khi được thực thi đồng thời với những giao dịch 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 dịch đó. 
• Tính bền vững (Durability):Sau khi một giao dịch đã 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, chodù là hệ 
thống có bị lỗi đi chăng nữa. 
2 Mô hình giao dịch trong SQL 
Giao dịch SQL được định nghĩa dựa trên các câu lệnh xử lý giao dịch sau đây: 
• BEGIN TRANSACTION: Bắt đầu một giao dịch 
• SAVE TRANSACTION: Đánh dấu một vị trí trong giao dịch (gọi là điểm đánh 
dấu). 
• ROLLBACK TRANSACTION: Quay lui trở lại đầu giao dịch hoặc một điểm 
đánh dấu trước đó trong giao dịch. 
Trang 112 
• COMMIT TRANSACTION: Đánh dấu điểm kết thúc một giao dịch. Khi câu 
lệnh này thực thi cũng có nghĩa là giao dịch đã thực hiện thành công. 
• ROLLBACK [WORK]: Quay lui trở lại đầu giao dịch. 
• COMMIT [WORK]: Đánh dấu kết thúc giao dịch. 
Một giao dịch 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 dịch và có cú pháp như sau: 
BEGIN TRANSACTION [tên_giao_tác] 
Một giao dịch 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 dịch. Sau câu lệnh này, 
một giao dịch 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 dịch và đưa cơ sở dữ liệu về trạng thái như trước khi 
giao dịch bắt đầu. Một giao dịch mới sẽ bắt đầu sau khi câu lệnh ROLLBACK được 
thực thi. 
• Một giao dịch 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 dịch bắt đầu (tương tự như khi 
câu lệnh ROLLBACK được thực thi để huỷ bỏ một giao dịch). 
Tuy nhiên, trong trường hợp này sẽ không có giao dịch mới được bắt đầu. 
Giao dịch 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 dịch đã 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 dịch 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 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 dịch. 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 
dịch. 
Trong quá trình thực thi giao dịch 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 
Trang 113 
Trong trường hợp này, những thay đổi về mặt dữ liệu mà giao dịch đã 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 dịch 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 dưới mô tả cho ta thấy hoạt động của một giao dịch có sử dụng các điểm đánh 
dấu: 
Hoạt động của 1 giao dịch 
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 dịch, giao dịch 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 dịch (tức là huỷ bỏ giao 
dịch), giao dịch 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. 
Câu lệnh COMMIT TRANSACTION trong giao dịch dưới đây kết thúc thành 
công mộtgiao dịch 
BEGIN TRANSACTION giaotac3 UPDATE diemthi SET 
diemlan2=0 
Trang 114 
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 UPDATE 
monhoc 
SET sodvht=4 WHERE sodvht=3 ROLLBACK TRANSACTION 
giaotac4 
UPDATE monhoc SET sodvht=2 WHERE sodvht=3 COMMIT 
TRANSACTION giaotac4 
3 Giao dịch lồng nhau 
Các giao dịch 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 dịch 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 dịch khác. 
Ví dụ dưới đây minh hoạ cho ta trường hợp các giao dịch lồng nhau. 
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 END 
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 
Lời gọi đến thủ tục sp_TransEx được thực hiện trong một giao dịch khác như 
sau: 
BEGIN TRANSACTION T3 
Trong giao dịch trên, câu lệnh ROLLBACK TRANSACTION T3 huỷ bỏ giao 
dịch và do đó tác dụng của lời gọi thủ tục trong giao dịch không còn tác dụng, tức là 
không có dòng dữ liệu nào mới được bổ sung vào bảng T (cho dù giao dịch 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 dịch khác trong đó có lời gọi đến thủ tục 
sp_tranex như sau : 
Trang 115 
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 dịch 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 dịch đã bị huỷ 
bỏ bởi câu lệnh ROLLBACK TRANSACTION trong giao dịch. 
Như đã thấy trong ví dụ trên, khi các giao dịch SQL được lồng vào nhau, giao 
dịch ngoài cùng nhất là giao dịch có vai trò quyết định. Nếu giao dịch ngoài cùng nhất 
được uỷ thác (commit) thì các giao dịch được lồng bên trong cũng đồng thời uỷ thác. 
Và nếu giao dịch ngoài cùng nhất thực hiện lệnh ROLLBACK thì những giao 
dịch 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 dịch lồng 
bên trong đã thực hiện lệnh COMMIT TRANSACTION). 
Trang 116 
Phụ lục: Cơ sở dữ liệu mẫu 
Trong toàn bộ nội dung giáo trình, hầu hết các ví dụ được dựa trên cơ sở dữ liệu 
mẫu 
được mô tả dưới đây. Cơ sở dữ liệu này được cài đặt trong hệ quản trị cơ sở 
dữ liệu SQL Server 2000 và được sử dụng để quản lý sinh viên và điểm thi của 
sinh viên 
trong một trường đại học. Để tiện cho việc tra cứu và kiểm chứng đối với các ví 
dụ, trong phần đầu của phụ lục chúng tôi giới thiệu sơ qua về cơ sở dữ liệu này. 
Cơ sở dữ liệu bao gồm các bảng sau đây: 
• Bảng KHOA lưu trữ dữ liệu về các khoa hiện có ở trong trường 
• Bảng LOP bao gồm dữ liệu về các lớp trong trường 
• Bảng SINHVIEN được sử dụng để lưu trữ dữ liệu về các sinh viên trong 
trường. 
• Bảng MONHOC bao gồm các môn học (học phần) được giảng dạy trong trường 
• Bảng DIEMTHI với dữ liệu cho biết điểm thi kết thúc môn học của các sinh 
viên 
Mối quan hệ giữa các bảng được thể hiện qua sơ đồ dưới đây 
Các bảng trong cơ sở dữ liệu, mối quan hệ giữa chúng và một số ràng buộc được 
cài đặt như sau: 
CREATE TABLE khoa ( makhoa NVARCHAR(5) NOT NULL 
CONSTRAINT pk_khoa PRIMARY KEY, tenkhoa NVARCHAR(50) 
NOT NULL , dienthoai NVARCHAR(15)NULL ) 
CREATE TABLE lop ( malop NVARCHAR(10) NOT NULL 
CONSTRAINT pk_lop PRIMARY KEY, tenlop NVARCHAR(30) 
NULL , khoa SMALLINT NULL , hedaotao NVARCHAR(25) NULL , 
namnhaphocINT NULL , siso INT NULL , makhoa 
NVARCHAR(5) NULL ) CREATE TABLE sinhvien ( masv 
NVARCHAR(10) NOT NULL 
Trang 117 
CONSTRAINT pk_sinhvien PRIMARY KEY, hodem NVARCHAR(25) 
NOT NULL , ten NVARCHAR(10) NOT NULL , ngaysinh 
SMALLDATETIME NULL , gioitinh BIT NULL , 
noisinh NVARCHAR(100) NULL , malop NVARCHAR(10) NULL ) 
CREATE TABLE monhoc ( mamonhoc NVARCHAR(10) NOT NULL 
CONSTRAINT pk_monhoc PRIMARY KEY, tenmonhoc NVARCHAR(50) 
NOT NULL , sodvht SMALLINT NOT NULL ) CREATE TABLE diemthi 
( mamonhoc NVARCHAR(10) NOT NULL , masv NVARCHAR(10) NOT 
NULL , diemlan1 NUMERIC(5, 2) 
NULL , diemlan2 NUMERIC(5, 2) NULL, 
CONSTRAINT pk_diemthi PRIMARY KEY(mamonhoc,masv) ) 
ALTER TABLE lop ADD CONSTRAINT fk_lop_khoa 
FOREIGN KEY(makhoa) REFERENCES khoa(makhoa) 
ON DELETE CASCADE ON UPDATE CASCADE ALTER TABLE 
sinhvien ADD CONSTRAINT fk_sinhvien_lop FOREIGN KEY 
(malop) 
REFERENCES lop(malop) ON DELETE CASCADE ON UPDATE 
CASCADE 
ALTER TABLE diemthi ADD CONSTRAINT fk_diemthi_monhoc 
FOREIGN KEY (mamonhoc) REFERENCES monhoc(mamonhoc) 
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT fk_diemthi_sinhvien FOREIGN KEY (masv) 
REFERENCES sinhvien(masv) ON DELETE CASCADE ON UPDATE 
CASCADE ALTER TABLE monhoc ADD CONSTRAINT 
chk_monhoc_sodht 
CHECK(sodvht>0 and sodvht<=5) ALTER TABLE diemthi ADD 
CONSTRAINT chk_diemthi_diemlan1 
CHECK (diemlan1>=0 and diemlan1<=10), 
CONSTRAINT chk_diemthi_diemlan2 
CHECK (diemlan2>=0 and diemlan2<=10)