Trigger trong MySQL

Phạm Minh Trí

05/08/2020•7 min read

Giới thiệu và hiện thực trigger trong MySQL, thực hiện các phương thức định sẵn khi dữ liệu có thay đổi.

Khi thao tác với cơ sở dữ liệu, đôi khi cần thực hiện 1 số thao tác định sẵn mỗi khi dữ liệu trong 1 bảng thay đổi. Trong các hệ quản trị cơ sở dữ liệu hiện đại đều hỗ trợ tính năng này - Trigger.

Trigger là một phương thức được lưu trữ sẵn, tự động thực thi khi một sự kiện xảy ra. Có thể là một trong các sự kiện sau:

1 câu lệnh thao tác dữ liệu DELETE, INSERT, hoặc UPDATE.
1 câu lệnh định nghĩa dữ liệu CREATE, ALTER, hoặc DROP.
1 hoạt động dữ liệu SERVERERROR, LOGON, LOGOFF, STARTUP, hoặc SHUTDOWN.

Tại sao cần Trigger?

Giả sử có trang web cho phép người dùng tạo và chia sẻ bài viết của họ như sau:

Khi người dùng đăng 1 bài viết, 1 record mới được tạo ra trong bảng articles.
- Đồng thời cần phải đưa id của bài viết mới này vào bảng articles_preview để chờ các editor kiểm tra nội dung và định dạng lại bài viết trước khi bài viết được công khai.
- Cần phải quản lý thêm thời điểm mà người dùng tạo bài viết created_at và lần cập nhật bài viết gần nhất updated_at.
Như vậy giá trị của id trong article_preview hoặc trường updated_at cần tự động thay đổi mỗi khi người dùng thực hiện các thao tác cập nhật.

Vấn đề trên xuất phát từ việc cần phải thực hiện 1 số thao tác định sẵn mỗi khi dữ liệu trong 1 bảng thay đổi. Các thao tác này có thể được thực hiện bằng cách thêm phần code để thêm id vào article_preview hoặc lấy thời gian hiện tại và ghi vào trường updated_at, nhưng phải chủ động thêm code cho tất cả những nơi có liên quan đến cập nhật dữ liệu cho các record trong bảng articles.

Do đó, cần tiếp cận các giải pháp cho vấn đề này theo hướng tự động, miễn là dữ liệu có cập nhật, 1 bộ phận định sẵn sẽ được kích hoạt thực hiện việc tự lấy id và ghi vào articles_preview khi 1 bài viết được thêm vào articles hoặc hoặc thay đổi thời gian updated_at khi có sự thay đổi bài viết (UPDATE).

Tiếp cận

Để thực hiện 1 số thao tác định sẵn khi có sự thay đổi trên 1 bảng của cơ sở dữ liệu có thể được xử lý thông qua 2 cách tiếp cận chính:

Sử dụng các ngôn ngữ lập trình cung cấp khả năng giao tiếp với với hệ quản trị cơ sở dữ liệu như PHP, C/C++, C#, Node.js, …
Sử dụng các cú pháp được hệ quản trị cơ sở dữ liệu cung cấp để hỗ trợ cho vấn đề này, cụ thể ở đây là Trigger.

Ở cách tiếp cận đầu tiên, cho phép chúng ta thực hiện các thao tác phức tạp mà 1 ngôn ngữ lập trình bậc cao có thể cung cấp, tuy nhiên lại đánh đổi về hiệu năng do việc thông qua các lớp giao tiếp với hệ quản trị cơ sở dữ liệu và đòi hỏi ngôn ngữ này phải trực tiếp quản lý được (hoặc trung gian thông qua) các thao tác truy cập đến cơ sở dữ liệu. Dưới đây là mô hình và đoạn code bằng PHP (Laravel 5.4) được dùng để xử lý vấn đề đầu tiên ở trên:

<?php

function new_post(Request $request) {
  // Get request's body
  $body = json_decode($request->getContent(), true);

  // Insert new post into articles table & retrieve its id
  $id = DB::table('articles')->insertGetId($body);

  // Insert new post's id into articles_preview table
  DB::table('articles_preview')->insert([
    'article_id' => $id
  ]);
}

Ở cách tiếp cận thứ 2, cho phép chúng ta thực hiện các thao tác này ở mức độ cơ sở dữ liệu, từ đó đưa ra 1 giải pháp tự nhiên và cho phép hiệu năng ở mức cao nhất mà hệ quản trị cơ sở dữ liệu có thể cung cấp. Tuy nhiên, phương pháp này lại bị giới hạn bởi các cú pháp truy vấn mà hệ quản trị cơ sở dữ liệu đó cho phép.

Trong bài viết này, chúng ta sẽ tìm hiểu về Trigger trong MySQL.

Giải pháp sử dụng Trigger trong MySQL

Cấu trúc câu lệnh Trigger

CREATE TRIGGER [trigger_name] [BEFORE | AFTER] [INSERT | DELETE | UPDATE] ON [table_name]
FOR EACH
	[sql_statements]

Trong đó:

CREATE TRIGGER là câu lệnh dùng để khai báo Trigger.
[trigger_name] là tên của Trigger.
[BEFORE | AFTER] MySQL cung cấp 2 thời điểm để Trigger được kích hoạt khi có sự thay đổi dữ liệu trên bảng là trước (BEFORE) hoặc sau (AFTER) khi dữ liệu thay đổi.
[INSERT | DELETE | UPDATE] Trigger được kích hoạt khi 1 trong 3 thao tác chính là INSERT, DELETE, UPDATE xảy ra trên bảng.
[table_name] là tên của bảng sử dụng Trigger, khi 1 trong 3 thao tác INSERT, DELETE, UPDATE được Trigger xác định xảy ra trên bảng này, Trigger đó sẽ được kích hoạt.
FOR EACH khai báo phần thân của Trigger, kể từ câu lệnh này trở đi, định nghĩa những thao tác mà Trigger này sẽ thực hiện khi được kích hoạt.

Ví dụ (Trigger thực hiện việc thêm id của bài viết mới vào bảng articles):

CREATE TRIGGER pre_check_post AFTER INSERT ON articles
FOR EACH ROW
    INSERT INTO articles_review (article_id)
    VALUES (NEW.id);

Môi trường thực nghiệm

Windows 10.
MySQL Community 5.*.
MySQL Workbench 6.*.

Tạo dữ liệu mẫu

Sử dụng MySQL Workbench 6 để chạy các câu lệnh SQL

Khởi chạy MySQL Workbench.
Kết nối đến MySQL.

Đầu tiên tạo 1 kết nối tới MySQL.

Nhập địa chỉ IP, Port, Username và Password (nếu có).

Và kết quả:

Mở tab mới để chạy các câu lệnh SQL.

Copy các câu lệnh SQL cần chạy vào Query Tab, nhấn Ctrl + Shift + Enter để chạy tất cả câu lệnh SQL trong tab, hoặc nhấn Ctrl + Enter để chạy chỉ những câu lệnh được chọn.

Tạo database và các bảng

CREATE DATABASE IF NOT EXISTS trigger_example;

USE trigger_example;

CREATE TABLE IF NOT EXISTS articles (
    id int(10) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    content mediumtext NOT NULL,
    author varchar(255) NOT NULL,
    status tinyint(1) DEFAULT 0 NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW(),
    updated_at timestamp NOT NULL DEFAULT NOW() ON UPDATE NOW(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS articles_preview (
    id int(10) NOT NULL AUTO_INCREMENT,
    article_id int(10) NOT NULL,
    inserted_at timestamp NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

Tạo Trigger

USE trigger_example;

CREATE TRIGGER pre_check_post AFTER INSERT ON articles
FOR EACH ROW
    INSERT INTO articles_preview(article_id)
    VALUES (NEW.id);

Thêm dữ liệu mới vào bảng articles

USE trigger_example;
INSERT INTO articles (title, content, author) VALUES ('IPHONE 7', 'iPhone 7 dramatically improves the most important aspects of the iPhone experience. It introduces advanced new camera systems. The best performance and battery life ever in an iPhone. Immersive stereo speakers. The brightest, most colorful iPhone display. Splash and water resistance. And it looks every bit as powerful as it is. This is iPhone 7.', 'User 1');
INSERT INTO articles (title, content, author) VALUES ('Galaxy S7', 'The Galaxy S7 is an evolution of the prior years model, with upgraded hardware, design refinements, and the restoration of features removed from the Galaxy S6, such as IP certification for water and dust resistance, as well as expandable storage. As with the S6, the S7 is produced in a standard model with a display size of 5.1-inch (130 mm), as well as an Edge variant whose display is curved along the wide sides of the screen. Unlike the S6, the S7 Edge also utilizes a larger 5.5-inch (140 mm) display rather than matching the screen size of the base models.', 'User 2');

Kiểm tra kết quả

Sau khi thực hiện thực hiện thêm dữ liệu mới vào bảng articles. Kiểm tra dữ liệu của bảng articles_preview bằng truy vấn SELECT.

SELECT * FROM trigger_example.articles_preview

Kết quả:

Như vậy, mỗi khi thực hiện INSERT dữ liệu mới vào bảng articles, thì id của bài viết mới đã tự động được thêm vào bảng articles_preview.