5. Bẫy lỗi (Trigger) trong SQL Server

Trigger là một dạng đặc biệt của thủ tục thường 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 INSER T, UPDATE,

DELETE) trên một bảng nào đó.

5.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.

5.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 chuNn 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).

5.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.

5.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, chuNn 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.

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

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:

Giả sử có một khách hàng mua 10 đơn vị mặt hàng LAPTOP. Khi đó số lượng

LAPTOP 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_DecreaseQuantityOf ItemForSale')

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.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 = itemsf orsale.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.

5.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)

5.6 DDL TRIGGER

Được giới thiệu trong SQL Server 2005, khác v ới DML trigger được kích hoạt

khi có sự thay đổi dữ liệu trên bảng, DDL trigger được thiết kế để đáp ứng lại các sự kiện

diễn ra trên server hay trên CSDL. Một DDL trigger có thể được kích hoạt khi người dùng

thực hiện các lệnh CREATE TABLE hay DROP TABLE. Ở cấp độ server, DDL trigger có

thể được kích hoạt khi có một tài khoản mới được tạo ra DDL trigger được lưu trữ trong

CSDL mà DDL trigger được gắn vào. Với các Server DDL Trigger theo dõi các thay đổi ở

cấp độ Server, được lưu trữ trong CSDL master.

DDL trigger được tạo ra cũng bằng câu lệnh CREATE TRIGGER với cấu trúc

như sau:

CREATE TRIGGER tên_trigger

ON { ALL SERVER | DATABASE }

FOR { loại_sự_kiện } [ ,...n ]

AS { các_câu_lệnh_SQL}

Trong đó:

ALL SERVER | DATABASE : quy định trigger sẽ kích hoạt dựa trên các sự

kiện diễn ra trên Server hay các sự kiện diễn ra trên CSDL.

loại_sự_kiện: là một sự kiện đơn ở cấp độ Server hay cấp độ CSDL làm kích

hoạt DDL trigger như: CREATE_TABLE, ALTER_TABLE, DROP_TABLE…

Ví dụ 1: Câu lệnh dưới đây xây dựng một trigger được kích hoạt khi xảy ra các

sự kiện ở cấp độ CSDL. Trigger này sẽ ngăn chặn các lệnh DROP TABLE và ALTER

TABLE.

create trigger t_safety

on database

for CREATE_TABLE, DROP_TABLE

as

print N'Phải xóa trigger t_safety trước khi ALTER hay DROP bảng'

rollback tran

Tiến hành xóa bảng ORDERDETAIL

drop table orderdetail

Ví dụ 2: Câu lệnh dưới đây xây dựng một trigger được kích hoạt khi xảy ra các

sự kiện ở cấp độ Server. Trigger này sẽ ngăn chặn việc tạo ra một account login mới

IF EXISTS (SELECT * FROM sys.server_triggers

WHERE name = 't_DoNotAllowCreateNewLogin')

DROP TRIGGER t_DoNotAllowCreateNewLogin

ON ALL SERVER

GO

CREATE TRIGGER t_DoNotAllowCreat eNewLogin

ON ALL SERVER

FOR CREATE_LOGIN

AS

PRINT N'Phải DROP trigger t_DoNotAllowCreateNewLogin tr ước khi

tạo account'

rollback

GO

Tiến hành tạo một account login mới :

create login test with password = '123456'

5.7 Enable/ Disable TRIGGER

Trigger cần bị vô hiệu hóa trong một số trường hợp:

Trigger gây ra lỗi trong quá trình xử lý CSDL

Quá trình nhập hay khôi phục những dữ liệu không thỏa trigger.

Vô hiệu hóa trigger bằng lệnh DISABLE TRIGGER có cấu trúc như sau:

DISABLE TRIGGER tên_trigger

ON { tên_đối_tượng | DATABASE | SERVER }

Ví dụ 1: Ví dụ này sẽ vô hiệu hóa trigger t_DoNotAllowCreateNewLogin

disable trigger t_DoNotAllowCreateNewLogin

on all server

Tiến hành tạo một account login mới:

create login newLogin with password = '12345'

Ví dụ 2: Ví dụ này sẽ khôi phục lại trigger t_ DoNotAllowCreateNewLogin

enable trigger t_DoNotAllowCreateNewLogin

on all server

Tiến hành tạo một account login mới:

create login newLogin1 with password = '12345'