BEGIN
if @@rowcount>0 begin
end END
if (columns_updated()& 48)=48 begin
raiserror('Đã cập nhật cả hai trường số lượng và đơn giá',10,1) end
Ví dụ 3: Tạo Trigger cho bảng Mua bán chỉ cho phép cập nhật trường số lượng và đơn giá trong bảng mua bán.
Create TRIGGER Trigger_ColumnUpdate2 ON muaban
AFTER UPDATE AS
Có thể bạn quan tâm!
-
Kết Quả Tạo Các Hàm Người Dùng -
Các Đặc Trưng Và Hạn Chế Của Trigger -
Các Lệnh Hệ Thống Và Các Hàm Sử Dụng Trong Trigger -
SQL Server - 23 -
Các Loại Bản Sao Lưu Cơ Sở Dữ Liệu -
Chọn Đường Dẫn Lưu Trữ File Backup
Xem toàn bộ 323 trang tài liệu này.
BEGIN
if @@rowcount>0 begin
if (columns_updated()|48)!=48 begin
raiserror('Chỉ cho phép có cập nhật trường số lượng và đơn giá',10,1)
rollback tran end
end END
else begin
update dbo.muaban
set dongia=inserted.dongia, soluong=inserted.soluong
from muaban inner join inserted on muaban.mamh=inserted.mamh
and muaban.makh=inserted.makh end
Trường hợp 2: Bảng có quá 8 trường.
Ta sử dụng hàm SUBSTRING để trigger hiểu bit mặt nạ cần kiểm tra.
- Nếu trường kiểm tra là một trường thuộc 8 trường đầu tiên: Cú pháp:
(SUBSTRING(COLUMNS_UPDATED(),1,1)
=power(2,(COLUMN_ID-1)))
- Nếu trường kiểm tra là một trường khác 8 trường đầu tiên, trường thứ 9 sẽ bắt đầu lại tương ứng là 1.
Cú pháp:
(SUBSTRING(COLUMNS_UPDATED(),2,1)
=power(2,(COLUMN_ID-1)))
Ví dụ 1: Tạo Trigger để kiểm tra trường họ tên và trường quê quán trong bảng thí sinh có được cập nhật hay không.
CREATE TRIGGER Trigger_UpdateColumn ON Thisinh
instead of UPDATE AS
BEGIN
if (SUBSTRING(COLUMNS_UPDATED(),1,1)
=power(2,(2-1))+power(2,(5-1)))
begin
raiserror('Cập nhật đã xẩy ra trên trường họ tên và quê quán',16,1) end
END
Ví dụ 2: Tạo Trigger để kiểm tra trường điểm toán, điểm lý, điểm hóa, mã ưu tiên, mã ngành trong bảng thí sinh có được cập nhật hay không.
CREATE TRIGGER Trigger_UpdateColumn1 ON Thisinh
For UPDATE AS
BEGIN
if ((SUBSTRING(COLUMNS_UPDATED(),1,1)
=power(2,(6-1))+power(2,(7-1))+power(2,(8-1)) ) and ((SUBSTRING(COLUMNS_UPDATED(),2,1)
=power(2,(1-1))+power(2,(2-1)))))
begin
raiserror('Cập nhật đã xảy ra trên trường điểm toán, điểm lý, điểm hóa, mã ưu tiên, mã ngành',16,1)
end
END
3) Các câu lệnh T-SQL không sử dụng được trong một trigger
Các câu lệnh không sử dụng được trong một trigger: ALTER, CREATE, DROP, RESTORE và LOAD DATABASE:
- LOAD và RESTORE LOG
- DISK RESIZE và DISK INIT
- RECONFIGURE
5.3.3. Các thao tác lập trình trigger
1) Trigger lồng nhau
Trigger lồng nhau là loại trigger mà bên trong trigger ta có thể thực hiện các lệnh INSERT, UPDATE, DELETE để cập nhật dữ liệu của các bảng khác. Các lệnh này sẽ làm kích hoạt các trigger liên quan khác (nếu có) và cứ thế các trigger có thể gọi thực hiện lồng nhau.
Cấp độ lồng tối đa của các trigger không vượt quá 32 cấp. Ta có thể dùng biến hệ thống @@NESTLEVEL để biết được cấp độ lồng hiện hành của trigger. Mặc định các trigger được phép lồng nhau. Tuy nhiên, ta cũng có thể tạm thời tắt chế độ lồng của trigger bằng lệnh: EXEC sp_configure „nested triggers‟,0. Ta cũng có thể bật trở lại chế độ lồng nhau của trigger bằng lệnh: EXEC sp_configure „nested triggers‟,1.
2) Khi thêm mới bản ghi
Trigger của hành động này sẽ tự động kích hoạt khi dữ liệu trong bảng được thêm mới vào bảng dữ liệu. Khi thêm mới các bản ghi, trigger thường kiểm tra ràng buộc toàn vẹn dữ liệu:
- Khóa chính.
- Khóa ngoại.
- Miền giá trị.
- Liên thuộc tính trong cùng một bảng.
- Liên thuộc tính của nhiều bảng khác nhau.
Khi các giá trị dữ liệu thêm mới vi phạm các ràng buộc toàn vẹn dữ liệu thì trigger sẽ thông báo cho người dùng biết và không lưu lại các thông tin của dòng dữ liệu vừa được thêm mới vào bên trong bảng. Trong các trigger thêm mới dữ liệu thì các dữ liệu vừa mới thêm vào sẽ được lưu trữ tạm thời trong bảng Inserted. Do đó, ta sẽ tham chiếu đến bảng Inserted để lấy ra các giá trị dữ liệu vừa mới thêm dùng trong những kiểm tra ràng buộc toàn vẹn dữ liệu.
Ví dụ 1: Xây dựng trigger trong bảng thí sinh để kiểm tra các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới một thí sinh. Các ràng buộc toàn vẹn dữ liệu bao gồm:
Khóa chính: Kiểm tra số báo danh đã tồn tại trong bảng thí sinh không.
Khóa ngoại: Kiểm tra mã ngành có tồn tại trong bảng ngành không, mã ưu tiên có tồn tại trong bảng ưu tiên không.
Miền giá trị: Kiểm tra điểm toán, điểm lý, điểm hóa phải lớn hơn hoặc bằng 0 và nhỏ hơn hoặc bằng 10.
CREATE TRIGGER Trigger_Insert_TS ON Thisinh
for INSERT AS
BEGIN
declare @kt bit
declare @sbd nvarchar(15)
declare @dt float, @dh float, @dl float set @kt=1
set @sbd = (select sbd from inserted)
if (select count(*) from thisinh where sbd=@sbd)>1 begin
set @kt=0
raiserror('Số báo danh đã tồn tại trong bảng thí sinh',16,6) end
if not exists (select * from inserted, nganh where inserted.man=nganh.man) begin
set @kt=0
raiserror('Mã ngành không tồn tại trong bảng ngành',16,6) end
if not exists (select * from inserted, uutien where inserted.maut=uutien.maut) begin
set @kt=0
raiserror('Mã ưu tiên không tồn tại trong bảng ưu tiên',16,6) end
select @dt = (select dtoan from inserted),@dl = (select dly from inserted),@dh
= (select dhoa from inserted) if @dt<0 or @dt>10
begin
set @kt=0
raiserror('Điểm toán không hợp lệ',16,6) end
if @dl<0 or @dl>10
begin
set @kt=0
raiserror('Điểm lý không hợp lệ',16,6) end
if @dh<0 or @dh>10
begin
set @kt=0
raiserror('Điểm hóa 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 thí sinh',16,6) end
else
END
begin
raiserror('Đã thêm được thông tin vào bảng thí sinh',16,6) end
Để kiểm tra các hoạt động bên trong trigger có đúng hay không, ta cần phải lần lượt thực hiện các lệnh INSERT INTO để thêm dữ liệu vào bảng Thí sinh cho các trường hợp mà ta đã kiểm tra trong trigger.
Trường hợp 1: Vi phạm ràng buộc toàn vẹn khóa chính, khóa ngoại và miền giá trị.
Trường hợp 2: Không vi phạm ràng buộc toàn vẹn khóa chính, khóa ngoại và miền giá trị.
Ví dụ 2: Xây dựng trigger trong bảng mượn trả để kiểm tra các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới một bản ghi. Các ràng buộc toàn vẹn dữ liệu bao gồm:
Khóa ngoại: Kiểm tra mã độc giả có tồn tại trong bảng độc giả không, mã sách có tồn tại trong bảng sách không.
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)
end
set @ngaym = (select ngaymuon from inserted) set @ngayht = (select ngayht from inserted)
set @ngayt = (select ngaytra from inserted)
if @ngaym<getdate() or @ngaym>@ngayt or @ngaym>@ngayht begin
set @kt=0
raiserror('Ngày mượn không hợp lệ',16,6) end
if @ngayht<@ngaym or @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
END
begin
raiserror('Đã thêm được thông tin vào bảng mượn trả',16,6) 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.
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