Trigger trong SQL Server: Hướng dẫn từ A-Z về Trigger

Trigger trong SQL Server là các stored procedure đặc biệt được thực thi tự động để phản hồi với các đối tượng cơ sở dữ liệu, cơ sở dữ liệu và các sự kiện máy chủ. SQL Server cung cấp ba loại trigger:

  • Trigger dữ liệu ngôn ngữ thao tác (Trigger DML) kích hoạt khi xảy ra sự kiện INSERT, UPDATE và DELETE dữ liệu xảy ra trên bảng.
  • Trigger dữ liệu ngôn ngữ định nghĩa (Trigger DDL) kích hoạt khi xảy ra các câu lệnh CREATE, ALTER và DROP.
  • Trigger đăng nhập (Trigger  Logon) kích hoạt khi xảy ra các sự kiện LOGON.

Tạo trigger trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng câu lệnh CREATE TRIGGER trong SQL Server để tạo một trigger mới.

Giới thiệu về câu lệnh CREATE TRIGGER trong SQL Server

Câu lệnh CREATE TRIGGER cho phép bạn tạo một trigger mới sẽ được kích hoạt tự động bất cứ khi nào một sự kiện như INSERT, DELETE hoặc UPDATE xảy ra đối với một bảng.

Sau đây minh họa cú pháp của câu lệnh CREATE TRIGGER:

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER  {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

Trong cú pháp này:

  • schema_name là tên của lược đồ mà trigger thuộc. Tên lược đồ là tùy chọn.
  • trigger_name là tên do người dùng chỉ định cho trigger.
  • table_name là bảng sẽ áp dụng trigger.
  • Sự kiện kích hoạt trigger được liệt kê trong câu lệnh AFTER. Sự kiện này có thể là INSERT, UPDATE hoặc DELETE. Một trigger có thể kích hoạt với một hoặc nhiều sự kiện xảy ra trên bảng.
  • Tùy chọn NOT FOR REPLICATION chỉ thị SQL Server không được kích hoạt trigger khi sửa đổi dữ liệu được thực hiện như một phần của quá trình sao chép.
  • sql_statements là một hoặc nhiều câu lệnh Transact-SQL được sử dụng để thực hiện các hành động sau khi một sự kiện xảy ra.

Các bảng ảo cho trigger: INSERTED và DELETED

SQL Server cung cấp hai bảng ảo đặc biệt cho trigger là bảng INSERTED và DELETED. SQL Server sử dụng các bảng này để thu thập dữ liệu của bản ghi đã sửa đổi trước và sau khi sự kiện xảy ra.

Bảng dưới đây cho thấy nội dung của bảng INSERTED và DELETED trước và sau mỗi sự kiện:

Sự kiện Bảng INSERTED chứa Bảng DELETED chứa
INSERT Các bản ghi đã được insert Trống
UPDATE Các bản ghi sau khi được update Các bản ghi trước khi được update
DELETE Trống Các bản ghi đã được delete

Hãy xem một số ví dụ tạo trigger trong SQL Server. Chúng tôi sẽ sử dụng bảng production.products trong cơ sở dữ liệu mẫu BikeStores để minh họa.

Bảng products trong cơ sở dữ liệu mẫu trong SQL Server

Ví dụ tạo bảng để ghi nhật ký thay đổi

Câu lệnh sau đây tạo một bảng có tên production.product_audits để ghi thông tin khi một sự kiện INSERT hoặc DELETE xảy ra đối với bảng production.products:

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 production.product_audits có tất cả các cột từ bảng production.products. Bên cạnh đó, nó được bổ sung thêm một vài cột để ghi lại những thay đổi ví dụ updated_at, operation và change_id.

Ví dụ tạo trigger DML trong SQL Server

Đầu tiên, để tạo một trigger mới, bạn chỉ định tên của trigger và lược đồ mà trigger thuộc về sau mệnh đề CREATE TRIGGER như sau:


CREATE TRIGGER production.trg_product_audit

Tiếp theo, bạn chỉ định tên của bảng trong mệnh đề ON, trigger sẽ kích hoạt khi xảy ra sự kiện trên bảng này:

ON production.products

Sau đó, bạn liệt kê một hoặc nhiều sự kiện sẽ kích hoạt trigger trong mệnh đề AFTER:

AFTER INSERT, DELETE

Phần thân của trigger bắt đầu bằng từ khóa AS:

AS
BEGIN

Sau đó, bên trong thân của trigger, bạn thiết lập SET NOCOUNT ON để ngăn chặn thông báo số lượng bản ghi bị ảnh hưởng bất cứ khi nào trigger được kích hoạt.

SET NOCOUNT ON;

Trigger sẽ INSERT một bản ghi vào bảng production.product_audits bất cứ khi nào một bản ghi được chèn vào hoặc xóa khỏi bảng production.products. Dữ liệu để chèn vào bảng production.product_audits được cung cấp từ bảng INSERTED và DELETED thông qua toán tử UNION ALL như sau:

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;

Sau đây câu lệnh tạo trigger hoàn chỉnh:

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

Cuối cùng, bạn thực thi câu lệnh trên để tạo trigger. Khi trigger được tạo, bạn có thể tìm thấy nó trong thư mục Triggers của bảng như trong hình sau:

Tạo trigger DML trong SQL Server

Kiểm tra hoạt động của trigger trong SQL Server

Câu lệnh sau đây chèn một bản ghi mới vào bảng production.products:

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

Vì sự kiện INSERT này, trigger production.trg_product_audit đã được kích hoạt trên bảng production.products và chèn một bản ghi vào bảng production.product_audits.

Hãy xem nội dung của bảng production.product_audits:

SELECT 
    * 
FROM 
    production.product_audits;

Đây là đầu ra:

Kiểm tra hoạt động của trigger trong SQL Server

Câu lệnh sau xóa một bản ghi khỏi bảng production.products:

DELETE FROM 
    production.products
WHERE 
    product_id = 322;

Đúng như dự đoán, trigger đã được kích hoạt và chèn bản ghi đã xóa vào bảng production.product_audits:

SELECT 
    * 
FROM 
    production.product_audits;

Hình ảnh sau đây cho thấy đầu ra:

Kiểm tra hoạt động của trigger trong SQL Server

Trong hướng dẫn này, bạn đã học cách tạo một kích hoạt trong SQL Server để đáp ứng với một hoặc nhiều sự kiện như chèn và xóa.

Trigger INSTEAD OF trong SQL Server

Trong phần này, bạn sẽ tìm hiểu cách sử dụng trigger INSTEAD OF trong SQL Server để chèn dữ liệu vào bảng thông qua view.

Trigger INSTEAD OF là gì?

Trigger INSTEAD OF là một trigger cho phép bạn bỏ qua một câu lệnh INSERT, DELETE hoặc UPDATE một bảng hoặc một view và thực thi câu lệnh khác được định nghĩa trong trigger. Các thao tác chèn, xóa hoặc cập nhật thực tế hoàn toàn không xảy ra.

Nói cách khác, trigger INSTEAD OF bỏ qua một câu lệnh DML và thực thi các câu lệnh khác được định nghĩa trong trigger.

Cú pháp trigger INSTEAD OF trong SQL Server

Dưới đây minh họa cú pháp về cách tạo trigger INSTEAD OF:


CREATE TRIGGER [schema_name.] trigger_name
ON {table_name | view_name }
INSTEAD OF {[INSERT] [,] [UPDATE] [,] [DELETE] }
AS
{sql_statements}

Trong cú pháp này:

  • Đầu tiên, chỉ định tên của trigger và lược đồ (tùy chọn) mà trigger thuộc về sau mệnh đề CREATE TRIGGER.
  • Thứ hai, chỉ định tên của bảng hoặc view liên kết với trigger.
  • Thứ ba, xác định một sự kiện để kích hoạt trigger INSTEAD OF như INSERT, DELETE hoặc UPDATE. Trigger có thể được kích hoạt với một hoặc nhiều sự kiện.
  • Thứ tư, định nghĩa phần thân của trigger sau từ khóa AS. Phần thân của trigger có thể bao gồm một hoặc nhiều câu lệnh Transact-SQL.

Ví dụ về trigger INSTEAD OF trong SQL Server

Một ví dụ điển hình của việc sử dụng trigger INSTEAD OF là ghi đè thao tác chèn, cập nhật hoặc xóa trên view.

Giả sử, một ứng dụng cần chèn các thương hiệu mới vào bảng production.brands. Tuy nhiên, các thương hiệu mới nên được lưu trữ trong một bảng khác được gọi là production.brand_approvals để phê duyệt trước khi chèn vào bảng production.brands.

Để thực hiện điều này, bạn tạo một view có tên là production.vw_brands cho ứng dụng để chèn các thương hiệu mới. Nếu các thương hiệu được chèn vào view, một trigger INSTEAD OF sẽ được kích hoạt để chèn các thương hiệu vào bảng production.brand_approvals.

Hình ảnh sau đây minh họa quá trình:

Giải thích về trigger INSTEAD OF trong SQL Server

Sơ đồ này không hiển thị tên lược đồ của tất cả các đối tượng cơ sở dữ liệu vì mục đích đơn giản.

Câu lệnh sau đây tạo một bảng mới có tên production.brand_approvals để lưu trữ các thương hiệu đang chờ được phê duyệt:


CREATE TABLE production.brand_approvals(
    brand_id INT IDENTITY PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

Câu lệnh sau đây tạo ra một view mới tên là production.vw_brands dựa vào bảng production.brands và production.brand_approvals:

CREATE VIEW production.vw_brands 
AS
SELECT
    brand_name,
    'Approved' approval_status
FROM
    production.brands
UNION
SELECT
    brand_name,
    'Pending Approval' approval_status
FROM
    production.brand_approvals;

Khi một bản ghi được chèn vào view production.vw_brands, chúng ta cần chuyển nó tới bảng production.brand_approvals thông qua trigger INSTEAD OF như sau:

CREATE TRIGGER production.trg_vw_brands 
ON production.vw_brands
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO production.brand_approvals ( 
        brand_name
    )
    SELECT
        i.brand_name
    FROM
        inserted i
    WHERE
        i.brand_name NOT IN (
            SELECT 
                brand_name
            FROM
                production.brands
        );
END

Trigger chèn tên thương hiệu mới vào bảng production.brand_approvals nếu tên thương hiệu không tồn tại trong bảng production.brands.

Bây giờ chúng ta hãy chèn một thương hiệu mới vào view production.vw_brands để xem chuyện gì xảy ra nhé:


INSERT INTO production.vw_brands(brand_name)
VALUES('Eddy Merckx');

Câu lệnh INSERT này đã kích hoạt trigger INSTEAD OF để chèn một bản ghi mới vào bảng production.brand_approvals.

Nếu bạn truy vấn dữ liệu từ view production.vw_brands, bạn sẽ thấy một bản ghi mới xuất hiện:

SELECT
    brand_name,
    approval_status
FROM
    production.vw_brands;
Ví dụ về trigger INSTEAD OF trong SQL Server

Câu lệnh sau đây cho thấy nội dung của bảng production.brand_approvals:

SELECT 
    *
FROM 
    production.brand_approvals;
Ví dụ về trigger INSTEAD OF trong SQL Server

Trong phần này, bạn đã tìm hiểu về trigger INSTEAD OF trong SQL Server và cách tạo trigger INSTEAD OF để chèn dữ liệu vào bảng thông qua view.

Còn nữa...

SQL Server
Prev
Function trong SQL Server: Hướng dẫn từ A-Z về function
Next
HAVING trong SQL Server
Bài Viết Liên Quan:
INTERSECT trong SQL Server
Trung Nguyen 22/11/2020
INTERSECT trong SQL Server

Tìm hiểu cách sử dụng toán tử INTERSECT trong SQL Server để trả về phần giao nhau của các tập kết quả của hai truy vấn.

  • 2 min read
Xem Thêm
EXCEPT trong SQL Server
Trung Nguyen 22/11/2020
EXCEPT trong SQL Server

Tìm hiểu cách sử dụng EXCEPT trong SQL Server để loại trừ tập kết quả của một truy vấn ra khỏi tập kết quả của một truy vấn khác.

  • 3 min read
Xem Thêm
SQL Server: Hướng dẫn subquery trong SQL Server từ A-Z
Trung Nguyen 22/11/2020
SQL Server: Hướng dẫn subquery trong SQL Server từ A-Z

Tìm hiểu về subquery, toán tử EXISTS, ANY, ALL trong SQL Server và cách sử dụng truy vấn con để truy vấn dữ liệu.

  • 9 min read
Xem Thêm
UNION trong SQL Server
Trung Nguyen 22/11/2020
UNION trong SQL Server

Tìm hiểu cách sử dụng UNION trong SQL Server để kết hợp kết quả của hai hoặc nhiều truy vấn thành một tập kết quả duy nhất.

  • 4 min read
Xem Thêm