Giới thiệu Trigger trong SQL Server

Trong bài này chúng ta sẽ tìm hiểu một chức năng rất đặc biệt trong hệ quản trị CSDL SQL Server đó là TRIGGER. Trigger được xem như là một chức năng quan trọng giúp bảo đảm các ràng buộc toàn vẹn.

1. Trigger trong SQL Server

Trigger dịch 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ẵn sàng kích hoạt bất cứ lúc nào khi có một hành động kéo cò tác động vào.

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.

Ví dụ bạn thiết kế cho bảng product và category, trong đó product sẽ có một column tên là total_product dùng để lưu trữ tổng số sản phẩm của category đó. Khi thêm một product thì ta phải tăng column đó lên một, đơn vị, khi update phải kiểm tra có thay đổi category không để tăng hoặc giảm cho hợp lý, khi delete thì bớt đi một. Việc này hoàn toàn có thể code bằng các ngôn ngữ đang sử dụng SQL Server, tuy nhiên bạn có thể sử dụng trigger để giúp hệ thống dữ liệu hoạt động tốt hơn.

Bài viết này được đăng tại [free tuts .net]

Như vậy chúng ta sẽ có tổng cộng 3 loại trigger như sau:

• Update: Loại trigger sẽ được kích hoạt khi có hành động cập nhật dữ liệu.
• Insert: Loại trigger sẽ được kích hoạt khi có hành động thêm dữ liệu.
• Delete: Loại trigger sẽ được kích hoạt khi có hành động xóa dữ liệu.

2. 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 vaf DELETED, hai 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. Hãy xem bảng dưới đây để biết thông tin của 2 virtual table này.

3. Cách tạo một trigger trong SQL Server

Để tạo một trigger thì bạn sử dụng cú pháp sau:

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

Hãy xem ví dụ tạo một trigger dưới đây, chúng ta sẽ sư dụng table products trong database mẫu  nhé. Cấu trúc của bảng này như sau:

Bước 1: Tạo table lưu trữ history

Mình sẽ tạo một table để lưu trữ những thay đổi trên bảng products với cấu trúc như sau:

CREATE TABLE production.product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);

Bảng này sẽ có tất cả các column của bảng products, ngoài ra mình còn bổ sung một số column để ghi lại lịch sử hoạt động như: updated_at, operation, và the change_id.

Nhiệm vụ bây giờ của mình là viết trigger để lưu trữ lại những 

Bước 2: Viết trigger

Hãy xem câu lệnh SQL tạo trigger như sau:

CREATE TRIGGER production.trg_product_audit
ON production.products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END

Mình sẽ giải thích đoạn code này như sau:

Đầu tiên là lệnh khai báo tên trigger như sau:

CREATE TRIGGER production.trg_product_audit

Tiếp theo chỉ định bảng sẽ ảnh hưởng bởi trigger này.

ON production.products

Tiếp theo là hành độc nào sẽ kích hoạt trigger, mình sẽ chọn là INSERT và DELETE.

AFTER INSERT, DELETE

Bắt đầu phần code chính của trigger.

AS
BEGIN

Thường khi bạn chạy một trigger thì kết quả trả về là tin nhắn kèm số lượng row bị ảnh hưởng, mình muốn tắt cái này để trả về một cái khác nên thiết lập thông số SET NOCOUNT ON.

SET NOCOUNT ON;

Nội dung của trigger sẽ thêm một row mới vào bảng product_audits 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 ta phải dùng toán tử UNION để gộp dữ liệu của hai virtual table inserted và deleted.

INSERT INTO
    production.product_audits
        (
            product_id,
            product_name,
            brand_id,
            category_id,
            model_year,
            list_price,
            updated_at,
            operation
        )
SELECT
    i.product_id,
    product_name,
    brand_id,
    category_id,
    model_year,
    i.list_price,
    GETDATE(),
    'INS'
FROM
    inserted AS i
UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        getdate(),
        'DEL'
    FROM
        deleted AS d;

Đáng lẽ ra phải viết hai trigger cho hai hành động để nó hoạt động riêng biệt thì dễ hiểu hơn.

Hãy chạy lệnh tạo trigger trên và sau đó vào SSMS bạn sẽ thấy có một trigger vừa xuất hiện như ở hình dưới đây.

Bước 3: Chạy trigger

Bạn hãy chạy câu lệnh Insert dưới đây.

INSERT INTO production.products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);

Sau đó hãy xem trong bảng product_audits thì bạn sẽ thấy dữ liệu như sau:

Hãy thử xóa sản phẩm có product_id là 322.

DELETE FROM 
    production.products
WHERE 
    product_id = 322;

Xem lại bảng product_audits thì kết quả như sau:

Lời kết: Như vậy trong bài này mình đã giới thiệu vè trigger và cách tạo một trigger trong SQL Server, đây là kiến thức nâng cao nên hơi khó hiểu, hy vọng sẽ giúp ích được cho bạn.