Tìm Hiểu Về Trigger Trong SQL Server - Tin Học 88

Bài viết sau đây sẽ giới thiệu về Trigger trong SQL Server. Hãy cùng nhau tìm hiểu để giúp cho công việc của chúng ta dễ dàng hơn.

Trigger là một dạng đặc biệt của thủ tục lưu trữ, được thực thi một cách tự động khi có sự thay đổi dữ liệu (do tác động của câu lệnh INSERT, UPDATE, DELETE) trên một bảng nào đó.

1 Các đặc điểm của trigger

Trigger chỉ thực thi tự động thông qua các sự kiện mà không thực hiện bằng tay.

Trigger sử dụng được với khung nhìn.

Khi trigger thực thi theo các sự kiện Insert hoặc Delete th ì dữ liệu khi thay đổi sẽ được chuyển sang các bảng INSERTED và DELETED, là 2 bảng tạm thời chỉ chứa trong bộ nhớ, các bảng này chỉ được sử dụng với các  lệnh trong trigger. Các bảng n ày thường được sử dụng để khôi phục lại phần dữ liệu đã thay đổi (roll back).

Trigger chia thành 2 loại INSTEAD OF và AFTER: INSTEAD OF là loại trigger mà hoạt động của sự kiện gọi trigger sẽ bị bỏ qua và thay vào đó là các lệnh trong trigger được thực hiện. AFTER trigger là loại ngầm định, khác với loại INSTEAD OF thì loại trigger này sẽ thực hiện các lệnh bênh trong sau khi đã thực hiện xong sự kiện kích hoạt trigger.

2 Các trường hợp sử dụng trigger

Sử dụng Trigger khi các biện pháp bảo đảm to àn vẹn dữ liệu khác không bảo đảm được.

Các công cụ này sẽ thực hiện kiểm tra tính toán vẹn trước khi đưa dữ liệu vào CSDL, còn

Trigger thực hiện kiểm tra tính toàn vẹn khi công việc đã thực hiện

Khi CSDL chưa được chuẩn hóa (Normalization) thì có thể xảy ra dữ liệu thừa, chứa ở nhiều vị trí trong CSDL thì yêu cầu đặt ra là dữ liệu cần cập nhật thống nhất trong mọi nơi.

Trong trường hợp này ta phải sử dụng Trigger.

Khi xảy ra thay đổi dây chuyền dữ liệu giữa các bảng với nhau (khi dữ liệu bảng n ày thay đổi thì dữ liệu trong bảng khác cũng được thay đổi theo).

3 Khả năng sau của trigger

Một trigger có thể nhận biết, ngăn chặn và huỷ bỏ được những thao tác làm thay đổi trái phép dữ liệu trong cơ sở dữ liệu.

Các thao tác trên dữ liệu (xoá, cập nhật và bổ sung) có thể được trigger phát hiện ra và tự động thực hiện một loạt các thao tác khác tr ên cơ sở dữ liệu nhằm đảm bảo tính hợp lệ của dữ liệu.

Thông qua trigger, ta có thể tạo và kiểm tra được những mối quan hệ phức tạp hơn giữa các bảng trong cơ sở dữ liệu mà bản thân các ràng buộc không thể thực hiện được.

4 Định nghĩa trigger

Câu  lệnh  CREATE  TRIGGER  được  sử  dụng  để  đinh  nghĩa  trigger  và  có  cấu trúc như sau:

CREATE TRIGGER tên_trigger ON tên_bảng FOR {[INSERT][,][UPDATE][,][DELETE]} AS [IF UPDATE(tên_cột) [AND UPDATE(tên_cột)|OR UPDATE(tên_cột)] ...] các_câu_lệnh_của_trigger

Lưu ý: Như đã nói ở trên, chuẩn SQL định nghĩa hai bảng logic INSERTED v à DELETED để sử dụng trong các trigger. Cấu trúc của hai bảng n ày tương tự như cấu trúc của bảng mà trigger tác động. Dữ liệu trong hai bảng này tuỳ thuộc vào câu lệnh tác động lên bảng làm kích hoạt trigger; cụ thể trong các trường hợp sau:

Khi câu lệnh DELETE được thực thi trên bảng, các dòng dữ liệu bị xoá sẽ được  sao  chép vào  trong  bảng  DELETED.  Bảng  INSERTED  trong  tr ường hợp này không có dữ liệu.

Dữ liệu trong bảng INSERTED sẽ là dòng dữ liệu được bổ sung vào bảng gây   nên   sự kích   hoạt   đối   với   trigger   bằng   câu   lệnh   INSERT.   Bảng DELETED trong tr ường hợp này không có dữ liệu.

Khi câu lệnh UPDATE được thực thi trên bảng, các dòng dữ liệu cũ chịu sự tác động của câu lệnh sẽ được sao chép vào bảng DELETED, còn trong bảng INSERTED sẽ là các dòng sau khi đã được cập nhật.

Ho ạt động	Bảng INSERTED	Bảng DELETED
INSERT	d ữ liệu được insert	không có d ữ liệu
DELETE	không có d ữ liệu	d ữ liệu bị xóa
UPDATE	d ữ liệu được cập nhật	d ữ liệu trước khi cập nhật

Ví dụ 1: Ví dụ dưới đây minh họa việc trigger được kích hoạt khi thêm dữ liệu vào bảng

CUSTOMERS

if exists (select name from sysobjects where name = 't_CheckCustomerName' and type = 'TR') drop trigger t_CheckCustomerName go create trigger t_CheckCustomerName on customers for insert as declare @lengthOfName int select @lengthOfName = len(inserted.customername) from inserted if @lengthOfName <=1 print N'Tên không hợp lệ' rollback tran go //Thêm một khách hàng mới có tên là A insert into customers values('A', '5/5/1978', 'True', '35 Hung Vuong')

Ví dụ 2: Ví dụ dưới đây minh họa trigger được kích hoạt khi có sự thay đổi mang tính đây chuyền giữa các bảng.

Giả sử có CSDL như sau:

trong bảng  ITEMFORSALE sẽ giảm xuống còn 90. Trigger dưới đây sẽ thực hiện công việc đó.

if exists (select name from sysobjects where name = 't_DecreaseQuantityOfItemForSale') drop trigger t_DecreaseQuantityOfItemForSale go create trigger t_DecreaseQuantityOfItemForSale on SALE for insert as update ITEMSFORSALE set itemsforsale.quantity = itemsforsale.quantity - inserted.salequantity from itemsforsale inner join inserted on itemsforsale.itemid = inserted.itemid go

Thực hiện thêm dòng vào bảng SALE

insert into sale values( 1, 10)

    

Ví dụ 3: Ví dụ này minh họa cũng minh họa trigger được kích hoạt khi có sự thay đổi mang tính dây chuyền giữa các bảng nhưng trong trường hợp này dữ liệu thay đổi liên quan đến nhiều dòng.

Giả sử người quản lý muốn thay đổi số lượng bán mặt hàng LAPTOP trong bãng SALE lên thêm 5 đơn vị. Như vậy từ kết quả ví dụ 2, ta thấy cần phải giảm số l ượng LAPTOP trong bảng ITEMSFORSALE xuống 10 đơn vị. Tuy nhiên, trong thực tế khi số lượng các dòng trong bảng SALE rất lớn, khi đó phải sử dụng trigger:

if exists (select name from sysobjects where name = 't_DecreaseSumQuantityOfItemForSale') drop trigger t_DecreaseSumQuantityOfItemForSale go create trigger t_DecreaseSumQuantityOfItemForSale on SALE for update as if update(salequantity) update ITEMSFORSALE set itemsforsale.quantity = itemsforsale.quantity (select sum(inserted.salequantity - deleted.salequantity) from deleted inner join inserted on deleted.saleid = inserted.saleid where inserted.itemid = itemsforsale.itemid) where itemsforsale.itemid in (select inserted.itemid from inserted)

Thực hiện cập nhật cho bảng SALE:

update sale set salequantity = salequantity + 10 where itemid = 1

Ví dụ 4: Ví dụ này minh họa INSTEAD OF trigger. Trigger dưới đây sẽ không cho thực hiện thao tác xóa trên bảng CUSTOMERS.

create trigger t_RollbackDelete on customers after delete as rollback tran

5 Kích hoạt trigger dựa trên sự thay đổi dữ liệu trên cột

Thay vì chỉ định một trigger được kích hoạt trên một bảng, ta có thể chỉ định trigger được kích hoạt và thực hiện những thao tác cụ thể khi việc thay đổi dữ liệu chỉ li ên quan đến một số cột nhất định nào đó của cột. Trong trường hợp này, ta sử dụng mệnh đề IF UPDATE trong trigger. IF UPDATE không sử dụng được đối với câu lệnh DELETE.

Trở lại ví dụ 3 trong phần định nghĩa trigger:

if exists (select name from sysobjects where name = 't_DecreaseSumQuantityOfItemForSale') drop trigger t_DecreaseSumQuantityOfItemForSale go create trigger t_DecreaseSumQuantityOfItemForSale on SALE for update as if update(salequantity) update ITEMSFORSALE set itemsforsale.quantity = itemsforsale.quantity (select sum(inserted.salequantity - deleted.salequantity) from deleted inner join inserted on deleted.saleid = inserted.saleid where inserted.itemid = itemsforsale.itemid) where itemsforsale.itemid in (select inserted.itemi d from inserted)

Trong ví dụ này trigger sẽ được kích hoạt khi có sự thay đổi dữ liệu trong cột salequantity của bảng Sale. Nếu có sự thay đổi dữ liệu trên các cột khác thì trigger sẽ không được kích hoạt. Câu lệnh dưới đây không làm cho trigger kích hoạt.

update sale set itemid = 3 where itemid = 2

Mệnh đề IF UPDATE có thể xuất hiện nhiều lần trong phần thân của trigger. Khi đó, mệnh đề IF UPDATE nào đúng thì phần câu lệnh của mệnh đề đó sẽ được thực thi khi trigger được kích hoạt.

6 Sử dụng trigger và Giao tác (TRANSACTION)

Khi một trigger được kích hoạt, SQL Server luôn tạo ra một giao tác theo dõi những thay đổi do câu lệnh kích hoạt trigger hoặc do bản thân trigger gây ra. Sự theo d õi này cho phép

CSDL quay trở lại trạng thái trước đó.

 Ví dụ: Ví dụ dưới đây xây dựng trigger không cho phép nhập v ào một bản ghi trong bảng

SALE khi số lượng hàng bán lớn hơn số lượng hàng thực tế còn lại trong bảng

ITEMSFORSALE

if exists (select name from sysobjects where name = 't_CheckQuantity' and type = 'TR') drop trigger t_CheckQuantity go create trigger t_CheckQuantity on sale for insert as declare @insertedQuantity decimal(18,2) declare @currentQuantity decimal(18,2) declare @itemid int select @itemid = itemid from inserted select @insertedQuantity = salequantity from inserted select @currentQuantity = quantity from itemsforsale where itemid = @itemid if(@currentquantity < @insertedquantity) print N'số lượng nhập vào lớn hơn số lượng hiện có' rollback tran

Tiến hành thêm vào bảng SALE số liệu như sau:

insert into sale values(2, 1000)

Tags: Sql 2005sql server 2005giáo trình sql servergiáo trình sql servertự học sql 2005tự học sql 2005tìm hiểu sql serversql serverTìm hiểu về Trigger trong SQL Server