Thứ Ba, 25 tháng 7, 2023

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.

Mục lục

  • 1. Trigger trong SQL Server
  • 2. Bảng ảo INSERTED và DELETED
  • 3. Cách tạo một trigger trong SQL Server
    • Bước 1: Tạo table lưu trữ history
    • Bước 2: Viết trigger
    • Bước 3: Chạy trigger

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.

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.

EventINSERTEDDELETED
INSERTDữ liệu của row vừa insertRỗng
UPDATEDữ liệu mới của row vừa updateDữ liệu cũ của row vừa update
DELETERỗngDữ liệu của row bị xóa

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:

1
2
3
4
5
6
7
8
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:

products png

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:

1
2
3
4
5
6
7
8
9
10
11
12
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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:

1
CREATE TRIGGER production.trg_product_audit

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

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

1
AFTER INSERT, DELETE

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

1
2
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.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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.

SQL Server Create Trigger Example jpg

Bước 3: Chạy trigger

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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:

SQL Server Create Trigger After Insert Example jpg

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

1
2
3
4
DELETE FROM
    production.products
WHERE
    product_id = 322;

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

SQL Server Create Trigger After delete Example jpg

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.

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels:

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master