Tìm hiểu INSTEAD OF Trigger trong SQL Server

Trong bài này chúng ta sẽ học cách sử dụng INSTEAD OF trigger trong SQL Server, đây là tính năng cho phép bạn bỏ qua câu lệnh SQL INSERT, UPDATE hoặc DELETE.

1. INSTEAD OF trigger là gì?

INSTEAD OF trigger là một loại trigger đặc biệt, nó cho phép bạn bỏ qua câu lệnh INSERT, UPDATE hoặc DELETE trên một table hoặc view. Ví dụ bạn muốn khi có hành động delete trên table product thì bạn không xóa sản phẩm mà sẽ chạy một câu lệnh UPDATE status của product đó sang chế độ ẩn. Mặc dù bạn có thể không sử dung lệnh DELETE mà UPDATE trực tiếp cũng được, tuy nhiên để đảm bảo mọi câu truy vấn đều đúng thì nên sử dụng trigger.

Cú pháp của nó cũng không khá gì trigger:

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

Mình sẽ không giải thích các tham số nữa vì bạn đã học nó rồi. Bây giờ ta sẽ thực hành một ví dụ để bạn dễ hiểu hơn.

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

2. Ví dụ INSTEAD OF trigger

Trong ví dụ này sử dụng database mẫu.

Một ví dụ điển hình của trigger đó là ghi đè dữ liệu của các thao tác insert, update, or delete trên view.

Giả sử một ứng dụng cần thêm một thương hiệu mới vào trong bảng brands, tuy nhiên các thương hiệu mới thì nên lưu trữ ở một bảng khác tên là brand_approvals để được phê duyệt, sau khi phê duyệt xong thì sẽ thêm nó vào bảng brands.

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

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

Các bước thực hiện như sau:

Bước 1: Tạo bảng brand_approvals

Hãy sử dụng câu lệnh SQL dưới đây để tạo bảng brand_approvals, bảng này sẽ lưu trữ các thương hiệu chưa được phê duyệt.

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

Bước 2: Tạo View vw_brands

Bây giờ bạn hãy tạo view tên là vw_brands bằng câu SQL sau.

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;

Dữ liệu ban dầu của view chính là dữ liệu của cả hai bảng brands và brand_approvals. 

Như vậy chúng ta sẽ có ba cách dùng:

• Nếu muốn lấy tất cả thương hiệu thì lấy trong view vw_brands
• Nếu muốn lấy thương hiệu đã được phê duyệt thì lấy trong bảng brands
• Nếu muốn lấy thương hiệu chưa phê duyệt thì lấy trong bảng brand_approvals

Thực tế bạn có thể không sử dụng cách này mà thêm một fỉed status vào table brands, như vậy sẽ đơn giản hơn rất nhiều. Tuy nhiên vì các bạn đang học nên hãy tham khảo ví dụ này.

Bước 3: Tạo trigger

Ta sẽ cần một trigger gán vào view vw_brands, mỗi khi có thao tác insert vào view thì sẽ insert nó vào table brand_approvals.

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

Bây giờ bạn hãy thử thêm một dòng dữ liệu vào view xem sao nhé.

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

Chạy câu truy vấn sau để xem dữ liệu hiện có trong view.

SELECT
 brand_name,
 approval_status
FROM
 production.vw_brands;

Hãy xem dữ liệu trong bảng brand_approvals.

SELECT 
 *
FROM 
 production.brand_approvals;

Như vậy trong bài này đã giới thiệu với các bạn cách sử dụng INSTEAD OF trigger trong SQL Server, đây là một chức năng rất hay và giúp các nhà thiết kế database có nhiều tùy chọn hơn trong việc phân tích mô hình CSDL.