SQL Tạo Trigger
Hướng dẫn tạo và sử dụng Trigger trong SQL Server Database.
1.Trigger trong SQL là gì?
2.Sử dụng Trigger khi nào?
3.Trigger và bảng ảo Inserted và Deleted trong SQL Server.
4.Cách tạo và sử dụng Trigger SQL Server Database.
1.Trigger trong SQL là gì?
Trigger trong SQL Server là một thủ tục đặc biệt trong SQL Server, nó được thực thi ở phía server khi có một sự kiện thay đổi dữ liệu như Insert, Delete, Update.
Trigger 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 của bảng trong 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 như: Insert, Delete và Update. Khi có những hành động nà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.
Trigger trong SQL Server Database có 2 loại: DDL Trigger và DML Trigger
-DDL Trigger:
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:
DML Trigger là Trigger xảy ra với các hành động Insert, Update và Delete trên bảng Table:
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)
| After (for) Trigger | Instead of Trigger |
|
|
2.Sử dụng Trigger khi nào?
Trigger được lưu trữ và quản lý trong SQL Server Database, nó được dùng trong trường hợp bạn muốn kiểm tra các ràng buộc toàn vẹn hoặc lưu log lịch sử thao tác dữ liệu trong Database.
Giả sử trong trường hợp cơ sở dữ liệu (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.
3.Trigger và bảng ảo Inserted và Deleted trong SQL Server.
SQL Server cung cấp 2 bảng ảo sử dụ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 bản ghi rows 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 |
|
|
|
4.Cách tạo Trigger trong SQL Server Database.
Sau đây là cú pháp tạo Trigger trong SQL Server Database.
4.1 Cú pháp tạo Trigger trong SQL Server
Cú pháp
CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER | BEFORE {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION] AS
BEGIN
{sql_statements}
END
Giải thích:
[schema_name.]trigger_name: là tên của trigger
table_name : là tên bảng mà có sự thay đổi dữ liệu.
AFTER | BEFORE {[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.
4.2 Tạo Trigger trong SQL Server Database.
Trước khi tạo Trigger chúng ta sẽ tạo bảng Product_Backup_HT để lưu dữ liệu lịch sử của bảng Product_t.
Tạo bảng Product_Backup_HT
CREATE TABLE Product_Backup_HT(
Probak_ID int IDENTITY(1,1) NOT NULL,
ProductID nvarchar(50) NOT NULL,
ProductName nvarchar(500) NULL,
ProductPrice decimal(18, 0) NULL,
ProductColor nvarchar(50) NULL,
updated_at datetime NOT NULL,
operation char(3) NOT NULL
)
-Ví dụ Tạo Trigger p_Trigge_Product_Backup_HT để lưu thông tin lịch sử thao tác dữ liệu khi thêm mới và xóa dữ liệu trên bảng Product_t như sau:
Tạo Trigger
CREATE TRIGGER p_Trigge_Product_Backup_HT
ON Product_t
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Product_Backup_HT (ProductID, ProductName,ProductPrice, ProductColor, updated_at,operation)
SELECT i.ProductID,i.ProductName,i.ProductPrice,i.ProductColor, GETDATE(),'INS' FROM inserted i
UNION ALL
SELECT d.ProductID,d.ProductName,d.ProductPrice,d.ProductColor,GETDATE(),'DEL' FROM deleted d;
END
Bây giờ chúng ta hãy thử kiểm tra hoạt động của Trigger bằng cách insert thêm 1 bản ghi vào bảng Product_t hoặc xóa dữ liệu trong bảng Product_t để xem Trigger sẽ hoạt động như thế nào.
Ví dụ thực hiện câu lệnh insert thêm 1 bản ghi vào bảng Product_t:
Ví dụ
INSERT INTO Product_t(ProductID,ProductName,ProductPrice,ProductColor)
VALUES ('P_0012','Product 12',12000,'IPhone 15');
-Kiểm tra dữ liệu thêm vào trong bảng Product_t
-Kiểm tra thời gian lịch sử thêm dữ liệu.
Ví dụ thực hiện xóa 2 bản ghi từ bảng Product_t :
Ví dụ
DELETE FROM Product_t WHERE ProductID in ('P_0011','P_0012');
Kiểm tra bảng Product_Backup_HT sẽ có các bản ghi được lưu trữ với thời gian và 2 trạng thái INS và DEL.
