Giáo trình SQL Server (Phần 1)

Liên thuộc tính trong cùng một bảng: 
- Kiểm tra ngày mượn phải nhỏ hơn hoặc bằng ngày hiện tại, ngày mượn phải 
nhỏ hơn hoặc bằng ngày hẹn trả và ngày trả. 
- Ngày hẹn trả lớn hơn hoặc bằng ngày mượn, ngày hẹn trả phải nhỏ hơn hoặc 
bằng ngày trả. 
- Ngày trả phải lớn hơn hoặc bằng ngày mượn, ngày trả phải lớn hơn hoặc bằng 
ngày hẹn trả. 
CREATE TRIGGER Trigger_Insert_MT 
ON muontra 
For INSERT 
AS 
BEGIN 
declare @kt bit 
declare @ngaym datetime, @ngayt datetime, @ngayht datetime 
set @kt=1 
if not exists (select * from inserted, docgia where inserted.madg=docgia.madg) 
 begin 
 set @kt=0 
raiserror('Mã độc giả không tồn tại trong bảng độc giả',16,6) 
 end 
if not exists (select * from inserted, sach where inserted.masach=sach.masach) 
 begin 
 set @kt=0 
raiserror('Mã sách không tồn tại trong bảng sách',16,6) 
Tập bài giảng SQL Server 
164 
 end 
set @ngaym = (select ngaymuon from inserted) 
set @ngayht = (select ngayht from inserted) 
set @ngayt = (select ngaytra from inserted) 
if @ngaym@ngayt or @ngaym>@ngayht 
 begin 
 set @kt=0 
 raiserror('Ngày mượn không hợp lệ',16,6) 
 end 
if @ngayht@ngayt 
 begin 
 set @kt=0 
 raiserror('Ngày hẹn trả không hợp lệ',16,6) 
 end 
if @ngayt<@ngayht 
 begin 
 set @kt=0 
 raiserror('Ngày trả không hợp lệ',16,6) 
 end 
if @kt=0 
 begin 
 rollback tran 
raiserror('Không thêm được thông tin vào bảng mượn trả',16,6) 
 end 
else 
 begin 
raiserror('Đã thêm được thông tin vào bảng mượn trả',16,6) 
 end 
END 
Trường hợp 1: Vi phạm ràng buộc toàn vẹn khóa ngoại và liên thuộc tính trong 
cùng một bảng. 
Tập bài giảng SQL Server 
 165 
Trường hợp 2: Không vi phạm ràng buộc toàn vẹn khóa ngoại và liên thuộc tính 
trong cùng một bảng. 
2) Khi xóa bản ghi 
Trigger của sự kiện này sẽ tự động kích hoạt khi dữ liệu bên trong bảng bị xóa. 
Khi đó, trong trigger sẽ có một số các kiểm tra ràng buộc toàn vẹn dữ liệu như là: kiểm 
tra ràng buộc toàn vẹn dữ liệu khóa ngoại dùng để xóa tự động dữ liệu bên bản con có 
liên quan hoặc thông báo lỗi đã vi phạm ràng buộc toàn vẹn khi xóa dữ liệu bên bảng 
cha. 
Khi giá trị dữ liệu trong bảng bị xóa nếu có vi phạm ràng buộc toàn vẹn dữ liệu 
khóa ngoại thì trigger sẽ thông báo cho người dùng biết và không hủy thông tin của 
các dòng dữ liệu. Trong các trigger hủy bỏ dữ liệu thì dữ liệu vừa bị hủy bỏ sẽ được 
lưu trữ tạm thời trong bảng Deleted. Ta sẽ tham chiếu đến bảng Deleted để lấy ra giá 
trị của các dữ liệu vừa bị hủy bỏ dùng cho các kiểm tra ràng buộc toàn vẹn dữ liệu 
khóa ngoại. 
Ví dụ 1: Xây dựng trigger trong bảng ngành để kiểm tra các ràng buộc toàn vẹn 
dữ liệu khi người dùng xóa một ngành. Cần phải kiểm tra các ràng buộc toàn vẹn dữ 
liệu như sau: 
Kiểm tra xem ngành học bị xóa đã có thí sinh nào đăng kí hay chưa. Nếu đã có 
thí sinh đăng kí ngành học này thì không xóa. Ngược lại thì xóa ngành học này. 
CREATE TRIGGER Trigger_Del_NH 
ON Nganh 
AFTER DELETE 
Tập bài giảng SQL Server 
166 
AS 
BEGIN 
 declare @man nvarchar(15) 
 select @man=(select man from deleted) 
 if (select count(*) from deleted)>0 
 begin 
if exists (select * from thisinh where man=@man) 
 begin 
 rollback tran 
raiserror('Không thể xóa ngành học này',16,6) 
 end 
 else 
 begin 
 raiserror('Ngành học này đã bị xóa',16,6) 
 end 
 end 
 else 
 begin 
raiserror('Không tồn tại ngành học này',16,6) 
 end 
 END 
Trường hợp 1: Không có ngành học trong bảng ngành. 
Trường hợp 2: Tồn tại ngành học trong bảng thí sinh. 
Tập bài giảng SQL Server 
 167 
 Trường hợp 3: Xóa được ngành học trong bảng ngành 
Ví dụ 2: Xây dựng trigger trong bảng độc giả để kiểm tra các ràng buộc toàn 
vẹn dữ liệu khi người dùng xóa một độc giả. Cần phải kiểm tra các ràng buộc toàn vẹn 
dữ liệu như sau: 
Kiểm tra xem độc giả bị xóa có mượn sách không. Nếu thí sinh có mượn sách 
thì xóa các thông tin liên quan đến thí sinh này trong bảng mượn trả. 
CREATE TRIGGER Trigger_DEL_DG 
 ON Docgia 
 AFTER DELETE 
AS 
BEGIN 
 declare @madg nvarchar(15) 
 declare @sbg integer 
 declare @sbgs nvarchar(15) 
 select @madg=(select madg from deleted) 
 if (select count(*) from deleted)>0 
 begin 
 raiserror('Độc giả này đã bị xóa',16,6) 
if exists (select * from muontra where madg=@madg) 
 begin 
select @sbg=(select count(*) from muontra where 
madg=@madg) 
Tập bài giảng SQL Server 
168 
 delete from muontra where madg=@madg 
select @sbgs=(CONVERT(nvarchar(10),@sbg)+' Bản ghi trong 
bảng mượn trả đã bị xóa') 
 raiserror(@sbgs,16,6) 
 end 
 end 
 else 
 begin 
 raiserror('Không có độc giả này',16,6) 
 end 
END 
Trường hợp 1: Không có độc giả trong bảng độc giả. 
Trường hợp 2: Độc giả chưa mượn sách lần nào. 
Trường hợp 3: Thông tin có trong cả hai bảng độc giả, mượn trả. 
Tập bài giảng SQL Server 
 169 
3) Khi cập nhật bản ghi 
Trigger của sự kiện này sẽ tự động kích hoạt khi dữ liệu trong bảng bị sửa đổi. 
Trong trigger sẽ có một số các kiểm tra ràng buộc toàn vẹn dữ liệu như là: kiểm tra 
ràng buộc toàn vẹn dữ liệu khóa ngoại, miền giá trị, liên thuộc tính trong cùng một 
bảng dữ liệu, liên thuộc tính của nhiều bảng dữ liệu khác nhau. Tuy nhiên, ta nên hạn 
chế việc sửa đổi dữ liệu, chỉ cho phép người sử dụng sửa đổi dữ liệu trên một số cột 
nhất định nào đó bên trong bảng. 
Để kiểm tra giá trị dữ liệu của một cột bên trong bảng có bị thay đổi trong các 
trigger sửa đổi dữ liệu, ta sẽ sử dụng hàm UPDATE. Hành động sửa đổi dữ liệu bên 
dưới của Microsoft SQL Server thực chất là sự kết hợp của hai hành động đi kèm là 
xóa dữ liệu cũ hiện có và thêm lại dữ liệu mới đã được sửa đổi. Do đó, bên trong 
trigger sửa đổi dữ liệu khi đó bảng Inserted sẽ chứa đựng dữ liệu mới sau khi sửa đổi 
và bảng Deleted sẽ chứa đựng dữ liệu cũ trước khi sửa đổi. Thông thường trong trigger 
sửa đổi chúng ta có thể tham chiếu đến cùng lúc hai bảng Inserted và Deleted. 
Ví dụ: Xây dựng trigger trong bảng hồ sơ để kiểm tra các ràng buộc toàn vẹn 
dữ liệu khi người dung cập nhật một bản ghi. Cần phải kiểm tra các ràng buộc toàn 
vẹn dữ liệu như sau: 
Không cho phép cập nhật mã nhân viên. Ngày vào ngành phải lớn hơn ngày 
sinh. 
CREATE TRIGGER Trigger_Update_HS 
ON Hoso 
AFTER UPDATE 
AS 
BEGIN 
declare @ngayvn as date 
declare @ngays as date 
if (select count(*) from inserted)>0 
begin 
 if update(manv) 
 begin 
 raiserror('Không thể cập nhật mã nhân viên',16,1) 
 rollback tran 
 end 
 if update(ngayvn) 
 begin 
Tập bài giảng SQL Server 
170 
 select @ngayvn=(select ngayvn from inserted) 
 select @ngays=(select ngaysinh from inserted) 
 if @ngayvn<@ngays 
 begin 
raiserror('Ngày vào ngành không hợp lệ',16,1) 
 rollback tran 
 end 
 end 
end 
else 
begin 
 raiserror('Không có nhân viên này',16,1) 
end 
END 
Trường hợp 1: Ngày vào ngành không hợp lệ. 
Trường hợp 2: Không thể cập nhật mã nhân viên. 
Trường hợp 3: Không có nhân viên này trong bảng hồ sơ. 
Tập bài giảng SQL Server 
 171 
Trường hợp 4: Thỏa mãn các điều kiện cập nhật. 
4) Khi cập nhật giá trị tự động 
Khi dữ liệu bị cập nhật bởi người sử dụng, ngoài việc kiểm tra các ràng buộc 
toàn vẹn dữ liệu, bên trong trigger còn có thêm một tính năng ưu việt nữa đó là việc 
tính toán các giá trị và cập nhật tự động vào các cột của một bảng dữ liệu nào đó. 
Thông thường dữ liệu của các bảng này được hình thành dựa vào số liệu của một hoặc 
nhiều bảng khác bên trong cơ sở dữ liệu. Ta không nên viết các xử lý cập nhật giá trị 
tự động này thành một trigger mới hoàn toàn mà chỉ cần bổ sung các xử lý này vào 
bên dưới trong các trigger đã có. Bởi vì các xử lý cập nhật giá trị tự động chỉ được 
thực hiện khi nào các ràng buộc toàn vẹn dữ liệu đã được kiểm tra hợp lệ trước đó. 
a) Khi thêm mới bản ghi: 
Khi giá trị của các dữ liệu thêm mới đã được kiểm tra hợp lệ so với các ràng 
buộc toàn vẹn dữ liệu thì các dữ liệu này sẽ được cập nhật thay đổi giá trị tại một 
trường nào đó của các bảng liên quan. 
Ví dụ: Tạo Trigger cho bảng xuất hàng khi thêm một bản ghi mới. Ta cần kiểm 
tra các ràng buộc toàn vẹn dữ liệu: 
- Kiểm tra số lượng xuất phải nhỏ hơn hoặc bằng số lượng nhập. 
- Kiểm tra đơn giá xuất phải lớn hoặc bằng đơn giá nhập. 
Nếu tất cả các ràng buộc toàn vẹn dữ liệu ở trên đều hợp lệ thì thay đổi giá trị 
của trường số lượng tồn kho trong bảng tồn kho. 
CREATE TRIGGER Trigger_Insert_Xuat 
ON Xuat 
Tập bài giảng SQL Server 
172 
AFTER INSERT 
AS 
BEGIN 
declare @mamh as nvarchar(50) 
declare @slx as float 
declare @sln as float 
declare @dgx as float 
declare @dgn as float 
set @mamh=(select mamh from inserted) 
if (select count(*) from nhap where mamh=@mamh)>0 
begin 
 set @slx=(select slx from inserted) 
 set @dgx=(select dgx from inserted) 
 set @sln=(select sln from nhap where mamh=@mamh) 
 set @dgn=(select dgn from nhap where mamh=@mamh) 
 if @slx>@sln or @dgn>@dgx 
 begin 
 rollback tran 
raiserror('Số lượng xuất, đơn giá xuất không hợp lệ',16,2) 
 end 
 else 
 begin 
 update tonkho 
 set slt=slt-@slx 
 where mamh=@mamh 
 end 
end 
else 
begin 
 rollback tran 
 raiserror('Không tồn tại mặt hàng này',16,2) 
end 
END 
Trường hợp 1: Thêm thành công 
Tập bài giảng SQL Server 
 173 
Trường hợp 2: Không thêm bản ghi vào được bảng xuất 
Trường hợp 3: Không tồn tại mặt hàng này. 
b) Khi xóa bản ghi: 
Ngược lại với việc thêm mới, khi giá trị các dòng dữ liệu trong bảng bị hủy bỏ 
thì ta cập nhật thay đổi giá trị tại cột nào đó của các bảng liên quan. 
Ví dụ: Tạo Trigger cho bảng xuất hàng khi xóa một bản ghi. Khi một bản ghi bị 
xóa thì phải thay đổi giá trị của trường số lượng tồn kho trong bảng tồn kho. 
CREATE TRIGGER Trigger_DEL_xuat 
ON Xuat 
AFTER Delete 
AS 
BEGIN 
declare @mamh as nvarchar(50) 
Tập bài giảng SQL Server 
174 
declare @slx as float 
set @mamh=(select mamh from deleted) 
if (select count(*) from nhap where mamh=@mamh)>0 
begin 
 set @slx=(select slx from deleted) 
 update tonkho 
 set slt=slt+@slx 
 where mamh=@mamh 
end 
else 
begin 
 rollback tran 
 raiserror('Không tồn tại mặt hàng này',16,2) 
end 
END 
Trường hợp 1: Không tồn tại mặt hàng này. 
Trường hợp 2: Thực hiện việc xóa và cập nhật dữ liệu thành công. 
c) Khi cập nhật bản ghi: 
Sau khi bản ghi được sửa đổi, ta phải tính toán để cập nhật lại giá trị các cột của 
các bảng có liên quan khớp với giá trị mới vừa được sửa đổi. Việc cập nhập này có thể 
là tăng hoặc giảm so với giá trị cũ trước đó hoàn toàn tùy thuộc vào giá trị chênh lệch 
giữa giá trị mới được sửa đổi và giá trị cũ trước đó. 
Tập bài giảng SQL Server 
 175 
Vì vậy, nguyên tắc chung trong các trigger sửa đổi dữ liệu là cập nhật lại giá trị 
chênh lệch sau khi và trước khi sửa đổi dữ liệu, tùy thuộc vào số chênh lệch này là âm 
hoặc dương mà giá trị được cập nhật sẽ giảm hoặc tăng so với giá trị hiện hành. 
Ví dụ 1: Tạo Trigger cho bảng nhập hàng khi cập nhật một bản ghi. Ta cần 
kiểm tra các ràng buộc toàn vẹn dữ liệu: 
- Không cho phép cập nhật mã mặt hàng, tên mặt hàng. 
- Kiểm tra số lượng nhập phải lớn hơn hoặc bằng số lượng xuất. 
- Kiểm tra đơn giá nhập phải nhỏ hơn đơn giá xuất. 
Nếu tất cả các ràng buộc toàn vẹn dữ liệu ở trên đều hợp lệ thì thay đổi giá trị 
của trường số lượng tồn kho trong bảng tồn kho. 
CREATE TRIGGER Trigger_Update_Nhap 
ON Nhap 
AFTER Update 
AS 
BEGIN 
declare @mamh as nvarchar(50) 
declare @slx as float 
declare @sln as float 
declare @dgx as float 
declare @dgn as float 
if (COLUMNS_UPDATED() & 3) > 0 
begin 
 rollback tran 
 raiserror('Không cho phép cập nhật mã mặt hàng, tên mặt hàng',16,2) 
end 
else 
begin 
set @mamh=(select mamh from inserted) 
if (select count(*) from nhap where mamh=@mamh)>0 
begin 
 set @sln=(select sln from inserted) 
 set @dgn=(select dgn from inserted) 
 set @slx=(select slx from xuat where mamh=@mamh) 
 set @dgx=(select dgx from xuat where mamh=@mamh) 
 if @slx>@sln or @dgn>@dgx 
 begin 
Tập bài giảng SQL Server 
176 
 rollback tran 
 raiserror('Số lượng nhập, đơn giá nhập không hợp lệ',16,2) 
 end 
 else 
 begin 
 update tonkho 
 set slt=@sln-@slx 
 where mamh=@mamh 
 end 
end 
else 
begin 
 rollback tran 
 raiserror('Không tồn tại mặt hàng này',16,2) 
end 
end 
END 
Trường hợp 1: Số lượng nhập, đơn giá nhập không hợp lệ 
Trường hợp 2: Thực hiện việc cập nhật thành công. 
Trường hợp 3: Không tồn tại mặt hàng này. 
Tập bài giảng SQL Server 
 177 
Trường hợp 4: Không cho phép cập nhật tên mặt hàng. 
Ví dụ 2: Xây dựng trigger trong bảng phòng ban để kiểm tra các ràng buộc toàn 
vẹn dữ liệu khi người dùng cập nhật một bản ghi. Cần phải kiểm tra các ràng buộc 
toàn vẹn dữ liệu như sau: 
Khi cập nhật mã phòng trong bảng phòng ban thì mã phòng trong bảng lương 
cũng được cập nhật theo. 
CREATE TRIGGER TriggerUpdate_Phong 
ON Phong 
AFTER UPDATE 
AS 
BEGIN 
declare @mapmoi as nvarchar(10) 
declare @mapcu as nvarchar(10) 
select @mapmoi=(select maphong from inserted) 
select @mapcu=(select maphong from deleted) 
if (select count(*) from deleted)>0 
begin 
 update luong 
 set maphong=@mapmoi 
 where maphong=@mapcu 
 raiserror('Mã phòng trong bảng lương đã được cập nhật',16,1) 
Tập bài giảng SQL Server 
178 
 raiserror('Mã phòng trong bảng phòng đã được cập nhật',16,1) 
end 
else 
begin 
 raiserror('Không có phòng này',16,1) 
end 
END 
Trường hợp 1: Có phòng trong bảng phòng để cập nhật. 
Trường hợp 2: Không có phòng trong bảng phòng để cập nhật. 
Tóm lại, việc phân chia, tổ chức các kiểm tra ràng buộc toàn vẹn dữ liệu phức 
tạp hoặc các cập nhật dữ liệu tự động trong đối tượng trigger sẽ làm cho các xử lý 
được tập trung tại máy chủ và độc lập với ngôn ngữ lập trình tại máy trạm. Điều này 
làm cho tốc độ của các ứng dụng theo mô hình khách chủ được nhanh hơn. 
5.4. Thao tác với Trigger 
1) Tạo Trigger 
- Dùng công cụ MS 
 Bước 1: Chọn tên cơ sở dữ liệu/Chọn Tables/Chọn tên bảng/Chọn 
Triggers/Chọn chuột phải/Chọn New Trigger. 
Tập bài giảng SQL Server 
 179 
Hình 5.6. Tạo Trigger 
Bước 2: Viết đoạn mã lệnh để tạo Trigger. 
Bước 3: Chọn nút để kiểm tra câu lệnh T-SQL. 
Bước 4: Chọn nút để thực hiện câu lệnh Trigger. 
- Dùng câu lệnh T-SQL 
Bước 1: Chọn nút New Query , xuất hiện cửa sổ New Query 
Bước 2: Viết đoạn mã lệnh để tạo Trigger. 
Bước 3: Chọn nút để kiểm tra câu lệnh T-SQL. 
Bước 4: Chọn nút để thực hiện câu lệnh tạo Trigger. 
Hình 5.7. Kết quả tạo Trigger 
Tập bài giảng SQL Server 
180 
2) Thay đổi trigger 
Cách 1: Chọn tên trigger/Chọn chuột phải/Chọn Modify. 
Cách 2: Dùng câu lệnh Alter Trigger. 
3) Xóa trigger 
Cách 1: Chọn tên trigger/Chọn chuột phải/Chọn Delete/Chọn OK. 
Cách 2: Dùng câu lệnh Drop Trigger. 
4) Làm trigger mất hiệu lực 
Cách 1: Chọn tên trigger/Chọn chuột phải/Chọn Disable. 
Cách 2: Dùng câu lệnh. 
5) Làm trigger có hiệu lực 
Cách 1: Chọn tên trigger/Chọn chuột phải/Chọn Enable. 
Cách 2: Dùng câu lệnh.