Trigger dịch nôm sang tiếng Việt có nghĩa là cò súng, ý muốn nói nó như một cây cò súng và sẽ kích hoạt khi có một hành động kéo cò tác động vào.

Trong SQL, Trigger là một kiểu stored procedure mà được thực thi khi cố gắng sửa đổi dữ liệu trong bảng mà trình kích hoạt được tạo. Không giống các system stored procedure thông thường, trigger không thể thực thi trực tiếp, cũng không truyền hoặc nhận vào tham số.

Mỗi table thường sẽ có 3 thao tác làm thay đổi dữ liệu đó là: UPDATE, INSERT, DELETE. Và đôi khi mỗi hành động như vậy ta sẽ có những ràng buộc trên bảng để giúp bảo toàn dữ liệu, lúc này sử dụng trigger là một giải pháp tốt

Các kiểu Triggers

Triggers sẽ được tự động thực thi khi sự kiện bắt gặp trong bảng hoặc view. Ngôn ngữ sự kiện được chia ra 2 loại là DML và DDL events. Triggers liên kết với sự kiện DML được gọi là DML triggers, ngược lại gọi là DDL triggers.

DML Triggers

DML triggers được thực thi khi hoàn thành một sự kiện DML hoặc thay cho sự kiện DML. Những triggers này đảm bảo tính toàn vẹn tham chiếu bằng cách xếp tầng các thay đổi đối với các bảng có liên quan khi một hàng được sửa đổi.

DML triggers có 3 loại chính:

• INSERT trigger
• UPDATE trigger
• DELETE trigger

Giới thiệu về Inserted và Deleted Tables

Câu lệnh SQL trong DML triggers sử dụng 2 loại bảng đặc biệt để sửa đổi database. Khi dữ liệu được thêm vào, cập nhậật hoặc xóa, SQL Server tạo và quản lý các bảng này tự động, bảng tạm lưu trữ các dữ liệu gốc cũng như dữ liệu sửa đổi như sau:

Inserted table

Inserted table lưu trữ các bản coppy của những hàng được áp dụng thông qua câu lệnh INSERT và UPDATE. Trong quá trình insert hoặc update,, hàng mới sẽ đưojc thêm vào cả 2 bảng là inserted table và trigger table. Hàng trong inserted table là bản copy của hàng mới trong trigger table.

Deleted table

Deleted table lưu trữ bản copy của các hàng được áp dụng thông qua câu lệnh DELETE và UPDATE. Trong quá trình thực thi DELETE hoặc UPDATE, hàng sẽ đưỡ xóa khỏi trigger table và chuyển tới deleted table.

Lưu ý: 2 bảng inserted table và deleted table là tạm thời, để lưu trú bộ nhớ và kiểm tra ảnh hưởng của các sửa đổi dữ liệu nhất định và đặt điều kiện cho các hành động DML trigger. SQL Server 2019 không cho phép tham chiếu cột văn bản, nxtext hoặc hình ảnh trong các bảng được chèn và xóa cho các trigger AFTER.

Insert Triggers

Một INSERT trigger được thực thi khi một bảng ghi mới được insert vào bảng. INSERT trigger đảm bảo rằng giá trị nhập vào thỏa mãn nhưng ràng buộc định nghĩa ra trong bảng.

Khi người dùng nhập liệu một bản ghi vào trong bảng, INSERT trigger lưu copy của những record này vào trong inserted table. Nó sau đó kiểm tra liệu giá trị moiws có thỏa mãn các ràng buộc trong bảng hay không.

Nếu bản ghi hợp lệ, INSERT trigger sẽ thêm hàng vào trong bảng, nếu không nó sẽ hiển thị thông báo lỗi. Một INSERT trigger được tạo thông qua từ khóa INSERT trong câu lệnh CREATE TRIGGER và ALTER TRIGGER.

Cú pháp INSERT|UPDATE|DELETE trigger:

CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] 
AS
BEGIN
 <sql_statement>
END;

Ví dụ:

Bước 1 – tạo 2 bảng theo thông tin sau:

CREATE TABLE Locations (LocationID int, LocName varchar(100));
CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

Bước 2 – tạo INSERT trigger tên là TRIGGER_INSERT_Locations cho bảng Locations

CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations 
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID , getdate()
FROM inserted
END;

Bước tiếp theo test trigger câu lệnh:

insert into dbo.Locations values(1,'Ha Noi');

Trong trigger trên, khi insert dữ liệu của locations, dữ liệu LocationHistory được thêm vào dựa theo dữ liệu của Locations.

Trong đây có một từ khóa mới Replication là công nghệ sao chép, phân phối dữ liệu và là một trong những giải pháp khôi phục dữ liệu sau thảm họa có sẵn trong SSMS rất hữu ích để duy trì bản sao thứ hai hoặc bản sao dự phòng của các đối tượng (Table, View, Stored Procedure) và CSDL. Ý nghĩa NOT FOR REPLICATION là không sử dụng cho tình huống replication.

Update Triggers

Câu lệnh UPDATE trigger copy bản ghi gốc vào trong bảng Deleted và copy bản ghi mới vào bảng inserted khi bản ghi được cập nhật. Sau đó nó sẽ đánh giá bản ghi mới để nhận diện nếu giá trị có thỏa mãn điều kiện ràng buộc trong bảng không.

Nếu giá trị mới hợp lệ, bản ghi từ inserted table sẽ được copy vào bảng trigger. Tuy nhiên, nếu giá trị mới không hợp lệ, một error message sẽ được in ra cho ngờời dùng. Vì vậy, các bản ghi gốc đưojc copy từ Deleted table lại vào trigger table.

Một UPDATE trigger có thể được tạo băng cách sử dụng từ khóa UPDATE trong lệnh CREATE TRIGGER hoặc ALTER TRIGGER.

Ví dụ:

CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID, getdate() FROM inserted
END;
GO
update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

Sau khi chạy lệnh tạo trigger trên và update dữ liệu vào bảng Locations, bảng LocationHistory được thêm mới dữ liệu khi đữ liệu bảng Locations được cập nhật.

Delete Trigger

Delete trigger được tạo để hạn chế người dùng khỏi việc xóa bản ghi cụ thể khỏi bảng

Tuần tự thực thi sau sẽ xảy ra khi người dùng cố gắng xóa bản ghi:

• Bản ghi được xóa khỏi bảng trigger và thêm vào bảng Deleted.
• Nó sẽ kiểm tra so khớp ràng buộc khi xóa dữ liệu
• Nếu có ràng buộc ở bản ghi ngăn chặn việc xóa, DELETE trigger sẽ hiển thị thông báo lỗi
• Bản ghi bị xóa lưu trữ trong bảng Deleted sẽ được copy lại vào bảng trigger.

Ví dụ:

CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID, getdate()
FROM deleted
END;
GO
DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

Sau khi thực thi delete, trigger sẽ làm thêm việc thêm mới bản ghi vào bảng LocationsHistory.

AFTER Triggers

Một AFTER trigger được thực thi sau khi hoàn thành nghiệp vụ INSERT,UPDATE,DELETE. Một bảng có thể có nhiều trigger AFTER được định nghĩa cho mỗi hoạt động INSERT, UPDATE, DELETE. Nếu nhiều trigger được tạo trong cùng một bảng, ngời dùng phải định nghĩa thức tự thực thi trigger thực thi. Một AFTER trigger đưojc thực thi khi các ràng buộc kiểm tra hooàn thành, vì vậy, trigger sẽ được thực thi sau khi bảng Inserted và Deleted được tạo.

Cú pháp:

CREATE TRIGGER <trigger_name> ON <table_name>
[WITH ENCRYPTION]
[FOR | AFTER]
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
AS BEGIN <sql_statement> END;

Ví dụ:

CREATE TRIGGER AFTER_INSERT_Locations ON Locations
AFTER INSERT 
AS
BEGIN
INSERT INTO LocationHistory
SELECT LocationID
,getdate()
FROM inserted
END;

Sau khi thực thi, nó sẽ insert vào bảng LocationHistory sau khi insert Locations thành công, sự khác biệt ở trigger after và trigger on chỉ nằm ở thời điểm thực thi trigger.

INSTEAD OF Triggers

INSTEAD OF trigger sẽ đưojc thực thi thay cho hoạt động INSERT,UPDATE,DELETE khi được sử dụng. Một bảng hoặc view chỉ có thể có duy nhất một INSTEAD OF trigger được định nghĩa cho mỗi hoạt động INSERT, UPDATE , DELETE.

INSTEAD OF trigger sẽ được thực thi trước khi kiểm tra ràng buộc được thực thi trên bảng, trigger này được thực thi sau khi bảng inserted và deleted được tạo.

Ví dụ:

CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations
INSTEAD OF DELETE
AS
BEGIN
SELECT 'example instead trigger' as Message
END;
GO
DELETE FROM dbo.Locations
WHERE LocName='Ha Noi'

Khi câu lệnh delete thực thi, INSTEAD OF trigger được kích hoạt và khối lệnh bên trong trigger được thực thi, và hành động xóa không xảy ra.

Thứ tự thực thi của DML Triggers

SQL Server cho phép người dùng xác định AFTER trigger nào sẽ được thực thi trước và cái nào sẽ thực thi sau. Tất cả các AFTER trigger khiđược gọi giữa các trình kích hoạt đầu tiên và cuối cùng không có thứ tự thực thi nhất định.

Sử dụng sp_settriggerorder stored procedure để định nghĩa thứ tự cửa DML Trigger,

Cú pháp xác định thứ tự thực thi AFTER DML trigger:

sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

Ví dụ:

EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

Xem định nghĩa của DML Trigger

Để xem định nghĩa trigger thông qua stored procedure sp_hepltext
Cú pháp:

sp_helptext '<trigger_name>';

Ví dụ:

sp_helptext TRIGGER_DELETE_Locations

ALTER DML trigger

Có 2 cách để sửa trigger:

• DROP và RECREATE lại trigger với parameter mới
• Thay đổi trigger với cú pháp ALTER TRIGGER

Cú pháp:

ALTER TRIGGER <trigger_name>
ON {<table_name>|<view_name>}
[WITH ENCRYPTION]

{FOR|ALTER|INSTEAD OF}
{ [INSERT] [,] [UPDATE] [,] [DELETE] }
AS <sql_statement>

Ví dụ:

ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations
WITH ENCRYPTION FOR INSERT
AS
IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted)
BEGIN 
PRINT 'Location can not be updated'
ROLLBACK TRANSACTION
END;

DROP DML Trigger

Cú pháp:

DROP TRIGGER <DML_trigger_name> [,...n]

Ví dụ:

DROP TRIGGER TRIGGER_UPDATE_Locations

DDL Triggers

Một Data Definition Language (DDL) trigger thực thi stored procedure khi sự kiện DDL như CREATE,ALTER,DROP xuất hiện trong database hoặc server. DDL triggers chỉ có thể vận hành khi sự kiện DDL hoàn thành.
DDL trigger có thể sử dụng để ngăn chặn chỉnh sửa trong database schema.
DDL trigger có thể gọi một sự kiện hoặc hiển thị một tin nhắn dựa trên thông báo của schema. DDL trigger được định nghĩa hoặc ở tầng database hoặc ở tầng server.

Cú pháp:

CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

Ví dụ:

CREATE TRIGGER Secure ON DATABASE
FOR DROP_TABLE, ALTER_TABLE AS
PRINT 'You must disable trigger scure to drop or alter table'
ROLLBACK;

Với đoạn code này, DDL trigger được tạo cho câu lệnh DROP TABLE hoặc ALTER TABLE.

Phạm vi DDL Trigger

DDDL trigger được gọi bởi câu lệnh sql được thực thi trong database hoặc server hiện tại. Ví dụ, một DDL trigger được tạo cho câu lệnh CREATE TABLE thực thi trên sự kiện CREATE TABLE trên database.
DDL trigger tạo cho câu lệnh CREATE LOGIN thực thi trên sự kiện LOGIN trên server.

Phạm vi của DDL trigger phụ thuộc vào liệu trigger thực thi cho sự kiện database hay sự kiện server, trigger được chia thành 2 loại như sau:

Database-scoped DDL triggers

Database-scoped DDL trigger được gọi bởi sự kiện chỉnh sửa database schema. Những trigger này được lưu trữ trong database và thực thi ở sự kiện DDL, ngoại trừ những thứ liên kết với bảng tạm.

Server-Scoped DDL triggers

Server scoped DDL trigger được gọi bởi sự kiện DDL ở tầng server. Những trigger này được lưu trữ trong database master.

Nested trigger

Cả DDL và DML trigger đều có khái niệm nà khi trigger triển khai một hành động mà nó khởi tạo một trigger khác. DDL và DML trigger có thể được lồng nhau tới 32 tầng. Giả sử nếu trigger chỉnh sửa một bảng trên đó có mộ trigger khác, trigger thứ 2 được khởi tạo, sau đó sẽ gọi trigger thứ 3 và tiếp tục …

Nếu trigger lồng nhau đươc cho phép chạy, các trigger sẽ tuần tự bắt đầu như một vòng lặp vô hạn, nếu nó đạt tới tầng lông cuối cùng, trigger sẽ chấm dứt.

Trigger lồng nhau có thể sử dụng để xử lý các chức năng như lưu trữ hoặc backup các bản ghi bị ảnh hưởng bởi hành động trước đó.

Người dùng có thể disable nested trigger, bằng cách cấu hình nested triggers option thông qua stored procedure sp_configure thành 0 hoặc OFF. Mặc định cấu hình sẽ cho phép nested trigger.

Ví dụ:

GO
CREATE TRIGGER Employee_Deletion ON HumanResources.Employee
AFTER DELETE
AS
BEGIN
PRINT 'Deletion will affect EmployeePayHistory table'
DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted)
END;
GO
CREATE TRIGGER Deletion_Comnfirmation
ON HumanResources.EmployeePayHistory AFTER DELETE
AS
BEGIN
PRINT 'Employe details successfully deleted from EmployeePayHistory table'
END;
DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

Hàm UPDATE()

Hàm UPDATE() trả về giá trị boolean xác định liệu UPDATE hay INSERT đã thực thi trên view hoặc cột của table chưa.

Hàm UPDATE() có thể sử dụng ở bất kỳ đâu bên trong thân của trigger UPDATE hoặc INSERT để kiểm tra kiểm tra xem trình kích hoạt có nên thực hiện một số hành động hay không.

Cú pháp:

UPDATE (column);

Ví dụ:

CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE
AS
IF (UPDATE(TransactionID) OR UPDATE(ProductID)) 
BEGIN
RAISEERROR(5009,16,10)
END;
GO

Xử lý nhiều hàng(records) trong một phiên

Khi một người dùng viết code cho DML trigger, các câu lệnh sẽ dựa vào trigger để kích hoạt các câu lệnh đơn. Câu lệnh đơn sẽ cập nhật nhiều bản ghi trong dữ liệu. Đaya là hành vi phổ biến trong trigger DELETE và UPDATE, và thường các câu lệnh sẽ ảnh hưởng tới nhiều bản ghi. Hành vi cho trigger INSERT sẽ ít phổ biến hơn vì câu lệnh insert thông thường chỉ thêm vào 1 bản ghi.

Khi một tính năng của DML trigger gọi tự động và tính toán lại tóm tắt các giá trị của bảng và lưu trữ kết quả vào bảng khác, cân nhắc multirow là quan trọng.

Ví dụ xét tình huống lưu trữ một kết quả tổng của single row insert:

USE AdventureWorks2019;
GO
CREATE TRIGGER PODetails
ON Purchasing.PurchaseOrderDetail AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = Subtotal + LineTotal FROM INSERTED
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

Trong đoạn code dưới, subtotal được được tính toán và lưu trữ cho mỗi hoạt động lệnh insert single row.
Đoạn code dứ sẽ chạy cho cả tình huống multirow hay single row:

USE AdventureWorks2019;
GO
CREATE TRIGGER PODetailMultiple
ON Purchasing.PurchaseOrderDetail AFTER INSERT AS
UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

LOGON Triggers

Những trigger này kích hoạt khi có phản hồi từ sự kiện LOGON trong SQL Server. Logon triggers kích hoạt stored procedure trong sự kiện LOGON. Sự kiện này bắt gặp khi phiên người dùng được thành lập với instance của SQL Server. Logon trigger xảy ra sau khi quá trình xác thực đăng nhập kết thúc, nhưng trước đó phiên người dùng thực sự đã được thành lập. Loggon trigger không chạy khi xác thực thất bại.

Loggon trigger được tạo trên tầng server và hữu dụng trong các trường hợp:

• Kiểm tra các hoạt động đăng nhập
• Điều khiển các hành động đăng nhập

Ví dụ tạo bảng LoginActivity cùng các cột LOGONEvent và Logintime được tạo trong adventureworks như sau:

CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

Tạo trigger để khi ngời dùng login sẽ insert lịch sử login vào bảng LoginActivity:

CREATE TRIGGER [track_login] ON ALL SERVER
FOR LOGON AS
BEGIN
INSERT INTO LoginActivity
SELECT EVENTDATA(),
GETDATE()
END;

Lưu ý: thật thận trọng khi sử dụng LOGON Trigger, có thể gây ra tình huống lỗi logic TRIGGER không đăng nhập được phải mở DAC để kết nối, nên test thật kĩ trước khi thực thi.

Hiệu suất trigger

Trong thực tế, trigger không mang theo chi phí, thay vào đó hiệu năng chúng khá ổn. Tuy nhiên, nhiều vấn đề về hiệu suất có thể xảy ra do logic hiện diện bên trong trình kích hoạt. Giả sử một trình kích hoạt tạo một con trỏ và lặp qua nhiều hàng chúng sẽ có sự chậm lại trong quá trình thực thi.

Tương tự, xét tình huống trigger thực thi nhiều câu lệnh SQL ngăn chặn ở bảng các bảng riêng biệt trong bảng inserted và deleted . Nó sẽ trả kết quả thêm một lần nữa và làm chậm câu truy vấn hơn việc không sử dụng trigger.

Vì vậy, tốt nhất nên giữ cho logic trigger đơn giản và tránh sử dụng con trỏ khi thực thi các câu lệnh trong các bảng và các tác vụ khác nhau làm giảm hiệu năng hệ thống.