SQL Server

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!

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