Bạn đã biết những gì về Trigger trong SQL Server? Nếu đang tìm hiểu về chức năng này, hãy theo dõi bài viết dưới đây nhé!
Trigger được xem như một chức năng quan trọng giúp đảm bảo tính toàn vẹn (Integrity) các ràng buộc (Constraint) và đảm bảo toàn vẹn dữ liệu (Data Integrity). Vậy cách hoạt động, thực hiện, của nó sẽ như thế nào?
Mục lục
Trigger trong SQL Server là gì?
Trigger có thể được hiểu giống như 1 cái bẫy, nó được sẵn sàng kích hoạt bất cứ lúc nào khi có hành động tác động, thay đổi dữ liệu trong bảng CSDL (Cơ sở dữ liệu) SQL Server. Thông thường, mỗi bảng (Table) trong SQL Server có ba thao tác làm thay đổi dữ liệu: Insert, Delete và Update. Khi có những hành động như vậy xảy ra, chúng ta sẽ tạo những Trigger ràng buộc để đảm bảo tính toàn vẹn của dữ liệu.
Hãy xét trường hợp CSDL của chúng ta vô tình bị tấn công hoặc mất tài khoản quản trị: kẻ xấu xâm nhập và thực hiện thao tác Delete (xóa) các dữ liệu hiện có trên CSDL, hoặc Insert (thêm mới) một thông tin tài khoản mạo danh vào bảng dữ liệu của chúng ta, hay chúng Update (thay đổi) quyền truy cập của 1 tài khoản nào đó thay vào tài khoản quản trị. Lúc này, Trigger chính là một giải pháp tối ưu để lựa chọn sử dụng.
Trigger là một dạng đặc biệt của thủ tục lưu trữ trên SQL Server và nó được kích hoạt tự động. Các trigger thường được sử dụng cho việc ép buộc các quy tắc làm việc và toàn vẹn dữ liệu. Tính toàn vẹn tham chiếu có thể được định nghĩa bằng cách sử dụng ràng buộc Foreign Key với câu lệnh Creative Table.
Hoạt động của Trigger
Nếu các ràng buộc tồn tại trong bảng có sự tác động của Trigger, nó được kiểm tra trước việc thực hiện Trigger. Nếu các ràng buộc bị vi phạm, Trigger sẽ không thực thi mà các Trigger được sử dụng trong những cách sau:
Các Trigger có thể thay đổi đồng loạt (cascade change) các bảng có liên hệ trong một CSDL:
- Các Trigger có thể không cho phép hoặc roll back những thay đổi vi phạm tính toàn vẹn tham chiếu, hủy bỏ giao tác sửa đổi dữ liệu.
- Các Trigger có thể áp đặt các giới hạn phức tạp hơn những giới hạn được định nghĩa bằng ràng buộc Check Khác với ràng buộc Check, các trigger có thể tham chiếu đến các cột trong các bảng khác.
- Các Trigger còn có thể tìm sự khác biệt giữa các trạng thái của một bảng trước và sau khi sửa đổi dữ liệu và lấy ra những tác động dựa trên sự khác biệt đó.
Bên cạnh đó, SQL Server đã cung cấp cho chúng ta ba loại Trigger để giải quyết vấn đề:
- Triger Update: Trigger này sẽ được kích hoạt khi có hành động cập nhật trên dữ liệu.
- Trigger Insert: Trigger này sẽ được kích hoạt khi có hành động thêm mới dữ liệu.
- Trigger Delete: Trigger này sẽ được kích hoạt khi có hành động xóa dữ liệu.
Tuy nhiên, mỗi Trigger có thể thực hiện nhiều hàm và gọi đến 16 thủ tục. Mỗi Trigger chỉ có thể áp dụng cho một bảng. Tuy nhiên, một trigger đơn có thể áp dụng cho cả 3 công việc Update, Insert và Delete.
Phân biệt DDL Trigger và DML Trigger
DDL Trigger là Trigger xảy ra khi các biến cố đến từ Database hoặc Server. Bao gồm hai loại:
- DDL Trigger mức Database, có câu lệnh Create, Alter và Drop,… được lưu trong CSDL đã tạo ra nó. Ví dụ: tạo DDL Trigger trên Database để mỗi khi người dùng tạo bảng/ sửa bảng/ xóa bảng thì ghi nhận lại thông tin của các thay đổi này.
- DDL Trigger mức Server có câu lệnh Create User, Create Login,…được lưu trong CSDL master. Ví dụ: tạo DDL Trigger trên Server để không cho người dùng tạo Login.
DML Trigger là Trigger xảy ra với các hành động Insert, Update và Delete trên bảng:
- Dùng để kiểm tra các ràng buộc toàn vẹn phức tạp.
- Dùng để xử lý tính toán và cập nhật tự động, bao gồm After (for) Trigger và Instead of Trigger:
| After (for) Trigger | Instead of Trigger |
|
|
Giới Thiệu Bảng Ảo Inserted và Deleted
SQL Server cung cấp 2 bảng ảo dành riêng cho trigger tên là Inserted và Deleted, 2 bảng này sẽ lưu trữ dữ liệu của các row trước hoặc sau khi hành động xảy ra.
| Bảng Inserted sử dụng cho lệnh Insert | Bảng Deleted sử dụng cho lệnh Delete | Bảng Inserted và Deleted sử dụng cho lệnh Update |
|
|
|
Cách tạo Trigger Trong SQL Server
Để tạo Trigger trong SQL Server chúng ta sẽ sử dụng database Northwind có sẵn, được cung cấp bởi để thực hiện tạo Trigger. Sau đây là cách tạo DML Trigger trong SQL Server. Đầu tiên, các bạn cần phải chuẩn bị tài nguyên bằng cách sử dụng database NorthWind, được download từ đường link
Tiếp đến, các bạn thực hiện theo các bước hướng dẫn sau:
- Tạo bảng Backup thông tin bị thay đổi:
Chúng ta sẽ tạo 1 bảng có tên là Products_backup để lưu lại các dữ liệu khi thao tác thay đổi dữ liệu Insert hoặc Delete xảy ra trên bảng Products.
| CREATE TABLE Products_backup ( Probak_ID int identity primary key, ProductID int NOT NULL , ProductName nvarchar (40) NOT NULL , SupplierID int NULL , CategoryID int NULL , QuantityPerUnit nvarchar (20) NULL , UnitPrice money NULL , UnitsInStock smallint NULL, UnitsOnOrder smallint NULL, ReorderLevel smallint NULL, Discontinued bit NOT NULL, updated_at datetime not null, operation char(3) not null, check (operation = ‘INS’ or operation = ‘DEL’) ); GO |
- Cú pháp tạo Trigger Trong SQL Server:
-
-
- CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION] AS
BEGIN
{sql_statements}
END
Trong đó:
[schema_name.]trigger_name là tên của trigger
AFTER {[INSERT],[UPDATE],[DELETE]}là chọn hành động của trigger, bạn sẽ chọn nhiều hơn một trong ba loại (INSERT, UPDATE, DELETE).
[NOT FOR REPLICATION]thiết lập không mở trình kích hoạt khi sửa đổi dữ liệu được thực hiện như một phần của quy trình sao chép, cái này có thể có hoặc không.
{sql_statements}là những lệnh T-SQL, nội dung chính của trigger
- CREATE TRIGGER [schema_name.]trigger_name
-
- Tạo DML trong Trigger
Bước 1: Khai báo tên trigger như sau: CREATE TRIGGER trg_product_backup
Bước 2: Chỉ ra tên bảng sẽ đặt trigger này: ON products
Bước 3: chọn hành động sẽ kích hoạt trigger, chúng ta sẽ chọn là INSERT và DELETE: AFTER INSERT, DELETE
Bước 4: Bắt đầu phần code chính của trigger: AS BEGIN
Bước 5: Thường khi chạy một trigger thì kết quả trả về là số lượng row bị tác động, chúng ta tắt thông báo này để trả về một thông báo khác nên thiết lập thông số: SET NOCOUNT ON;.
Bước 6: Viết nội dung của trigger là sẽ thêm một row mới vào bảng products_backup khi có bất kì hành động INSERT hoặc DELETE từ bảng products. Vì trigger này dùng cho cả hai trường hợp INSERT và DELETE nên chúng ta phải dùng toán tử UNION để gộp dữ liệu của 2 table ảo inserted và deleted.
Câu lệnh đầy đủ như sau:
| CREATE TRIGGER trg_product_backup ON products AFTER INSERT, DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO Products_backup ( ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, updated_at, operation ) SELECT i.ProductID, i.ProductName, i.SupplierID, i.CategoryID, i.QuantityPerUnit, i.UnitPrice, i.UnitsInStock, i.UnitsOnOrder, i.ReorderLevel, i.Discontinued, GETDATE(), ‘INS’ FROM inserted i UNION ALL SELECT d.ProductID, d.ProductName, d.SupplierID, d.CategoryID, d.QuantityPerUnit, d.UnitPrice, d.UnitsInStock, d.UnitsOnOrder, d.ReorderLevel, d.Discontinued, GETDATE(), ‘DEL’ FROM deleted d; END |
Hoặc chúng ta cũng có thể tách riêng viết 2 Trigger cho 2 hành động Insert và Delete riêng biệt. Sau khi thực thi câu lệnh thành công, chúng ta có kết quả 1 Trigger được tạo trên bảng Products:
Giờ chúng ta hãy thử kiểm tra hoạt động của Trigger bằng cách xóa dữ liệu trong bảng Products để xem Trigger sẽ hoạt động như thế nào. Hãy thực hiện câu lệnh insert thêm 1 bản ghi vào bảng products:
| INSERT INTO Products VALUES (‘THUNG’,1,1,’10 boxes x 20 bags’,18,39,0,10,0) |
Chúng ta có dữ liệu trong bảng products được thêm vào:
Đồng thời trong bảng products_backup chúng ta có dữ liệu tương ứng và nếu chúng ta thực hiện câu lệnh xóa bản ghi này:
| DELETE FROM Products WHERE ProductID = 78 |
Lúc đó, trong bảng Products_backup sẽ có 2 bản ghi được lưu trữ với thời gian và 2 trạng thái INS và DEL.
Trên đây là các bước chi tiết tạo DML Trigger. Trong phần tiếp theo, chúng ta sẽ học cách tạo các DDL Trigger ở mức Server để kiểm soát người dùng tạo tài khoản login và DDL Trigger ở mức Database để mỗi khi người dùng tạo/ sửa/ xóa bảng thì ghi nhận lại thông tin của các thay đổi này. Hãy đón xem những bài viết tiếp theo nhé!
Bộ môn Ứng dụng phần mềm
Cao đẳng FPT Mạng cá cược bóng đá Hà Nội
