Cách tạo trình kích hoạt trong MySQL

Tìm hiểu về Trình kích hoạt MySQL trong hướng dẫn thực hành này với các ví dụ. Giải thích cách sử dụng Trình kích hoạt CREATE, INSERT, DELETE và UPDATE trong MySQL

MySQL TRIGGER là một đối tượng được liên kết với một bảng trong MySQL. Đó là một hành động được thực thi để phản hồi lại một sự kiện đã xác định trên bàn

Ví dụ: Bạn có thể chỉ định trình kích hoạt để được thực thi khi thao tác CHÈN, CẬP NHẬT hoặc XÓA xảy ra trong một bảng.

Cần lưu ý rằng yêu cầu chính để thực thi trình kích hoạt là SUPERUSER hoặc đặc quyền gốc từ tài khoản nơi trình kích hoạt đang được thực thi

=> Kiểm tra TẤT CẢ Hướng dẫn về MySQL tại đây

Bạn sẽ học được gì

• Trình kích hoạt MySQL
  • cú pháp
  • Các ứng dụng của việc sử dụng MySQL Triggers
  • Dữ liệu thử nghiệm
• TẠO KÍCH HOẠT trong MySQL
  • MySQL CHÈN KÍCH HOẠT
    • SAU KHI CHÈN KÍCH HOẠT
    • TRƯỚC KHI CHÈN KÍCH HOẠT
  • KÍCH HOẠT CẬP NHẬT MySQL
  • CÔNG CỤ XÓA MySQL
  • Liệt kê tất cả các trình kích hoạt được liên kết với bảng
  • Tạo Trigger bằng MySQL GUI Client – ​​MySQL Workbench
  • Xử lý lỗi trong quá trình thực hiện kích hoạt
  • Ưu điểm của việc sử dụng Trigger
• Các câu hỏi thường gặp
• Sự kết luận
  • đề xuất đọc

Trình kích hoạt MySQL

Nói chung, có 2 loại Trigger

• Trình kích hoạt cấp độ hàng. Các loại trình kích hoạt này được kích hoạt hoặc gọi cho mỗi lần chèn/cập nhật/xóa của mỗi hàng
• Trình kích hoạt mức tuyên bố. Trình kích hoạt mức câu lệnh được gọi cho mỗi lần thực hiện câu lệnh. Một câu lệnh có thể tác động đến 1 hoặc nhiều hàng nhưng các trình kích hoạt liên quan sẽ chỉ được gọi một lần. Các loại trình kích hoạt này được hỗ trợ bởi cơ sở dữ liệu SQL như Oracle, PL/SQL, v.v.

Mysql 8. 0 chỉ hỗ trợ các trình kích hoạt cấp hàng được gọi cho mỗi lần chèn/cập nhật/xóa hàng

cú pháp

Hãy xem cú pháp để xác định hoặc tạo TRIGGER trong MySQL

CREATE TRIGGER 
trigger_name 
trigger_time 
trigger_event
ON table_name FOR EACH ROW
[trigger_order]
body

trigger_name. Tên duy nhất cho đối tượng kích hoạt trong bảng

thời gian kích hoạt. Các giá trị được phép là TRƯỚC & SAU – Trường này cho biết liệu trình kích hoạt sẽ được kích hoạt TRƯỚC hay SAU sự kiện

trigger_event. Là sự kiện hoặc hành động thực tế sẽ dẫn đến việc gọi trình kích hoạt

Các giá trị được phép cho trường này là

• CHÈN. Đối với mỗi hàng chèn
• CẬP NHẬT. Đối với mỗi sửa đổi hàng tôi. e. cập nhật hàng bằng cách sử dụng câu lệnh CẬP NHẬT
• XÓA BỎ. Khi một hàng bị xóa khỏi bảng

tên_bảng. Tên của bảng MySQL mà trình kích hoạt đang được xác định

trigger_order. Đây là trường tùy chọn và được sử dụng để xác định thứ tự thực hiện trình kích hoạt. Điều này thường được sử dụng khi có nhiều trình kích hoạt được liên kết với cùng một sự kiện – Trong trường hợp đó, chúng ta có thể xác định thứ tự thực hiện

Nó sử dụng cú pháp bên dưới.
{SAU. PRECEDES} other_trigger

Ghi chú. Nếu trigger_body chứa nhiều câu lệnh, chúng ta có thể sử dụng cú pháp tương tự như cách chúng ta sử dụng nó để tạo THỦ TỤC LƯU TRỮ.

Trong trường hợp đó, phần thân trình kích hoạt sẽ được đặt giữa các lệnh BEGIN và END và toàn bộ định nghĩa Trình kích hoạt sẽ nằm giữa các lệnh DELIMITER để có nhiều câu lệnh được chỉ định với các khối BEGIN…END

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Các ứng dụng của việc sử dụng MySQL Triggers

Có rất nhiều ứng dụng thực tế của trình kích hoạt MySQL

• Kiểm tra tính toàn vẹn của dữ liệu / Xử lý lỗi. Điều này được thực hiện đặc biệt bằng cách sử dụng trình kích hoạt TRƯỚC – giả sử bạn muốn kiểm tra xem giá trị cột được chèn có tuân theo cú pháp thích hợp hay giá trị đó nằm trong một phạm vi nhất định, v.v.
• Tạo mục nhật ký thay đổi. Trình kích hoạt được sử dụng rộng rãi để tạo các bản kiểm tra hoặc nhật ký thay đổi của hoạt động trong cơ sở dữ liệu hoặc bảng. Ví dụ: nếu bạn muốn theo dõi tất cả các cập nhật cho một bảng, thì chúng tôi có thể tạo trình kích hoạt CẬP NHẬT trên bảng đó để tạo một mục nhập khác trong quá trình kiểm tra .
• Khai thác/Cập nhật trường dữ liệu bổ sung. Đôi khi, có thể phải cập nhật một hoặc nhiều cột để đáp ứng với CẬP NHẬT hoặc CHÈN ban đầu. Kích hoạt có thể hữu ích trong những tình huống đó
• Sao chép dữ liệu trên các bảng khác nhau. Đôi khi, chúng tôi có thể muốn chèn/cập nhật dữ liệu vào các bảng khác do việc chèn hoặc cập nhật dữ liệu trên một bảng khác. Trong những trường hợp như vậy, chúng tôi có thể xác định trình kích hoạt để đạt được các bản cập nhật dữ liệu bảng mong muốn

Dữ liệu thử nghiệm

Tên bảng – item_inventory
Thuộc tính – tên – VARCHAR(30), giá – DECIMAL(5,2), số lượng INTEGER
Tập lệnh tạo bảng

CREATE TABLE item_inventory (id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(30), price DECIMAL(5,2), quantity INTEGER);

TẠO KÍCH HOẠT trong MySQL

Hãy xem một số ví dụ về việc sử dụng MySQL CREATE trigger

MySQL CHÈN KÍCH HOẠT

Chúng ta có thể tạo trình kích hoạt cho hàng INSERTS trong một bảng hiện có. Có thể có 2 loại trình kích hoạt như vậy là SAU KHI CHÈN và TRƯỚC KHI CHÈN

SAU KHI CHÈN KÍCH HOẠT

Chúng tôi sẽ tạo trình kích hoạt SAU KHI CHÈN trên bảng được đề cập trong dữ liệu thử nghiệm – item_inventory

Trước tiên hãy tạo một bảng kiểm tra hàng tồn kho

CREATE TABLE item_inventory_audit (id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
item_id VARCHAR(30), price DECIMAL(5,2), created_on DATETIME(6), quantity INTEGER);

Chúng tôi sẽ tạo một TRIGGER để thêm một mục trong bảng item_inventory_audit sau mỗi INSERT trong bảng item_inventory

CREATE TRIGGER trigger_tutorial.trigger_item_insert_audit
AFTER INSERT
ON trigger_tutorial.item_inventory
FOR EACH ROW
INSERT INTO trigger_tutorial.item_inventory_audit(item_id,price,created_on,quantity) VALUES(new.item_id, new.price, now(), new.quantity);

Trong định nghĩa TRIGGER ở trên

• Chúng tôi đã tạo trình kích hoạt có tên – trigger_item_insert_audit trong cơ sở dữ liệu – trigger_tutorial
• Trình kích hoạt được tạo trên bảng – item_inventory FOR EACH INSERT
• Phần thân trình kích hoạt – chứa câu lệnh INSERT có nghĩa là sau khi INSERT hàng trong bảng item_inventory, một hàng mới sẽ được chèn vào bảng item_inventory_audit

Chúng tôi cũng có thể kiểm tra xem trình kích hoạt đã được tạo thành công hay chưa bằng cách điều hướng đến thư mục kích hoạt trong bảng tương ứng trên bất kỳ máy khách MySQL GUI nào như MySQL Workbench

Hãy thử chạy cái này

Chúng tôi sẽ CHÈN một bản ghi trong bảng item_inventory

INSERT INTO trigger_tutorial.item_inventory(name, price, quantity) VALUES("Henko Washing Powder",150.50,100);

Bây giờ chúng ta có thể kiểm tra xem bản ghi tương ứng trong bảng item_inventory_audit đã được tạo hay chưa

SELECT * FROM trigger_tutorial.item_inventory_audit;

TRƯỚC KHI CHÈN KÍCH HOẠT

Kích hoạt Before INSERT sẽ được gọi TRƯỚC mỗi hàng INSERT trên bảng đích

Cú pháp tạo Trigger vẫn giống như chúng ta đã thấy trong phần trước

TRƯỚC KHI CHÈN có thể được sử dụng khi bạn muốn cập nhật các giá trị được chèn vào một số giá trị khác hoặc giá trị mặc định, v.v.

DELIMITER $$
CREATE TRIGGER trigger_tutorial.trigger_item_before_insert
BEFORE INSERT
ON trigger_tutorial.item_inventory
FOR EACH ROW
BEGIN
IF new.price > 100 THEN SET new.price = 1.02 * new.price;
END IF;
END $$ 
DELIMITER ;

Giả sử chúng ta muốn giá trong bảng kiểm toán cao hơn 2% so với dữ liệu giá thực tế được nhập

Ở đây, chúng tôi đã tạo trình kích hoạt TRƯỚC KHI CHÈN, điều này sẽ thay đổi giá của hàng mới được thêm vào 1. 02 lần giá gốc

KÍCH HOẠT CẬP NHẬT MySQL

Chúng tôi có thể tạo trình kích hoạt cho hàng CẬP NHẬT của một bảng hiện có. Tương tự như INSERT, đối với UPDATE cũng vậy, chúng ta có thể có 2 loại trigger i. e. TRƯỚC CẬP NHẬT và SAU CẬP NHẬT. Hãy xem ví dụ cho cả hai trường hợp như vậy

Trong trình kích hoạt CẬP NHẬT – chúng tôi có thể truy xuất cả giá trị MỚI và CŨ cho mục đang được cập nhật bằng từ khóa mới và cũ tương ứng

Hãy tạo Trình kích hoạt TRƯỚC CẬP NHẬT để đặt số lượng được cập nhật trong hàng MỚI thành chênh lệch giữa giá trị CŨ và MỚI. Ví dụ, nếu ai đó đang cập nhật cột số lượng trong bảng hiện có, thì

• Nếu mới. số lượng < cũ. số lượng => thiết lập mới. số lượng = cũ. số lượng – mới. số lượng
• Khác mới. số lượng = 0

DELIMITER $$
CREATE TRIGGER trigger_tutorial.trigger_item_before_update
BEFORE UPDATE
ON trigger_tutorial.item_inventory
FOR EACH ROW
BEGIN
	IF old.quantity > new.quantity THEN SET new.quantity = old.quantity - new.quantity;
    ELSE SET new.quantity = 0;
    END IF;
END $$ 
DELIMITER ;

Chúng ta cũng hãy tạo một Trình kích hoạt SAU CẬP NHẬT sẽ CHÈN một hàng mới trong bảng kiểm toán với giá trị cột số lượng mới được tính trong BỘ KÍCH HOẠT TRƯỚC CẬP NHẬT như minh họa ở trên

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Bây giờ chúng ta hãy hiểu điều này với sự trợ giúp của một ví dụ

Giả sử chúng ta đã thực hiện INSERT ban đầu trong bảng item_inventory bằng truy vấn bên dưới

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Điều này sẽ kích hoạt Kích hoạt TRƯỚC CHÈN và SAU KHI CHÈN tương ứng

Bây giờ, hãy chạy CẬP NHẬT cho Mục này (có ID mục – 2)

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Bây giờ trình tự thực hiện kích hoạt cho CẬP NHẬT sẽ là

=> BÁO CÁO CẬP NHẬT => TRƯỚC KHI CẬP NHẬT KÍCH HOẠT => SAU KHI CẬP NHẬT KÍCH HOẠT

Logic trong TRƯỚC CẬP NHẬT KÍCH HOẠT – sẽ cập nhật số lượng hàng mới thành chênh lệch giữa giá trị mới và giá trị gốc

Vì chúng tôi đang cập nhật số lượng thành 20 và giá trị ban đầu là 60, nên giá trị thực sẽ được chèn vào bảng sẽ là 40

Hãy thực hiện CHỌN trên bảng inventory_audit để xác minh kết quả

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Hàng đầu tiên bạn nhìn thấy ở trên được thêm bởi INSERT Trigger

Hàng thứ hai được thêm vào như một phần của CẬP NHẬT

1. số lượng CẬP NHẬT = 20
2. TRƯỚC KHI CẬP NHẬT KÍCH HOẠT => thay đổi mới. số lượng = 60 – 20 => 40
3. SAU KHI CẬP NHẬT KÍCH HOẠT => Chèn một hàng trong audit_table với số lượng được cập nhật – i. e 40

CÔNG CỤ XÓA MySQL

Để xóa hoặc bỏ MySQL TRIGGER, chúng ta có thể sử dụng lệnh DROP TRIGGER

cú pháp

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Hãy xem một lệnh ví dụ

Giả sử chúng tôi muốn xóa trình kích hoạt TRƯỚC KHI CHÈN mà chúng tôi đã tạo trong các phần trước. Trình kích hoạt được đặt tên là trigger_tutorial. trigger_item_b Before_insert

Hãy sử dụng lệnh DROP để xóa cái này

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Sau khi trình kích hoạt bị xóa, mọi hành động được liên kết với trình kích hoạt sẽ bị vô hiệu và không có sự kiện nào như vậy xảy ra

Liệt kê tất cả các trình kích hoạt được liên kết với bảng

Để liệt kê tất cả các trình kích hoạt được liên kết với lược đồ hoặc cơ sở dữ liệu, bạn có thể sử dụng lệnh SHOW TRIGGER

Ví dụ. Nếu chúng ta muốn liệt kê tất cả các trình kích hoạt trong lược đồ trigger_tutorial, thì chúng ta có thể sử dụng lệnh bên dưới.

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

//Đầu ra

Bạn có thể thấy nhiều chi tiết khác nhau liên quan đến các trình kích hoạt khác nhau có sẵn trong lược đồ

Một vài chi tiết cột như vậy được hiển thị bên dưới

Tạo Trigger bằng MySQL GUI Client – ​​MySQL Workbench

Tương tự như việc tạo bằng sự kiện CREATE TRIGGER, các trình kích hoạt cũng có thể được tạo bằng cách sử dụng MySQL GUI Clients như MySQL Workbench

Hãy xem các bước để tạo Trình kích hoạt TRƯỚC KHI CHÈN bằng cách sử dụng MySQL Workbench

#1) Nhấp chuột phải vào tên bảng cần tạo trình kích hoạt

#2) Chọn tùy chọn “Alter Table”

#3) Tạo “Tab kích hoạt”

#4) Nhấp vào dấu ‘+’ bên cạnh phần TRƯỚC CHÈN để tạo trình kích hoạt

#5) Ở bên phải, cập nhật/chỉnh sửa định nghĩa của trình kích hoạt. Chúng tôi sẽ thêm một câu lệnh IF để đặt giá = 1. 02 * giá cho giá > 100

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

#6) Nhấp vào “Áp dụng” để Lưu Trình kích hoạt

#7) Trình kích hoạt hiện đã được tạo. Để xác minh, bạn có thể chạy lệnh SHOW TRIGGERS và xác thực xem trình kích hoạt mới tạo có hiển thị không

DELIMITER $$
// Trigger Syntax
BEGIN
	-- Statements
END $$
DELIMITER ;

Tương tự như TRƯỚC CHÈN, chúng ta có thể thêm/xóa các loại trigger khác như SAU KHI CHÈN, TRƯỚC/SAU CẬP NHẬT, TRƯỚC/SAU KHI DELETE thông qua MySQL Workbench GUI Client cũng như

Xử lý lỗi trong quá trình thực hiện kích hoạt

Điều quan trọng là phải hiểu cách MySQL xử lý các lỗi xảy ra trong quá trình thực thi trình kích hoạt

• Đối với các trình kích hoạt thực thi TRƯỚC hoặc SAU một thao tác – Nếu thực thi trình kích hoạt không thành công, thao tác trên hàng sẽ không được thực hiện
• Đối với các trình kích hoạt được xác định cho cả hoạt động TRƯỚC và SAU – Nếu trình kích hoạt TRƯỚC không thành công, thì hoạt động sẽ không thành công và trình kích hoạt SAU sẽ không được gọi

Ưu điểm của việc sử dụng Trigger

• Phát triển ứng dụng và tái sử dụng mã nhanh hơn. Vì các trình kích hoạt được đặt tên là các đối tượng trong cơ sở dữ liệu, sau khi được tạo, chúng có thể được sử dụng lại và cũng có thể được thay đổi dễ dàng theo nhu cầu kinh doanh
• Cải thiện hiệu suất. Tất cả các quy tắc được xác định trong trình kích hoạt sẽ thực thi trên máy chủ trước khi trả về kết quả. Điều này ngăn chặn lặp lại qua lại giữa máy khách và máy chủ

Các câu hỏi thường gặp

Q #1) Trình kích hoạt trong MySQL là gì?

Câu trả lời. Nói một cách đơn giản, Trình kích hoạt trong MySQL là một loại hành động để phản hồi lại bất kỳ sự kiện nào được xác định

Ví dụ: Giả sử chúng ta muốn thực hiện một số hành động (dưới dạng thực thi một số câu lệnh/chèn dữ liệu vào một bảng khác) khi CHÈN hoặc CẬP NHẬT một hàng trong một số bảng khác, .

Q #2) Trình kích hoạt được triển khai trong MySQL như thế nào?

câu trả lời. Kích hoạt được đặt tên đối tượng trong MySQL DATABASE i. e. Trình kích hoạt được liên kết với một bảng trong cơ sở dữ liệu MySQL

Các trình kích hoạt có thể được tạo bằng lệnh CREATE TRIGGER và có thể là một trong 2 loại

• Cấp hàng. Chúng được gọi cho mỗi hàng. Ví dụ: trên mọi CHÈN của hàng trong bảng.
• mức tuyên bố. Chúng được gọi một lần trong quá trình thực thi câu lệnh. Câu lệnh có thể tác động đến một hoặc nhiều hàng nhưng trình kích hoạt liên quan sẽ chỉ được gọi một lần

Câu hỏi 3) Có thể có nhiều Trình kích hoạt được liên kết với cùng một sự kiện không?

Trả lời. Có, hoàn toàn ổn nếu có nhiều trình kích hoạt được liên kết với cùng một sự kiện. Ví dụ: chúng ta có thể xác định nhiều trình kích hoạt để thực thi khi một hàng được CHÈN vào một bảng.

Thứ tự thực thi trigger mặc định theo thứ tự tạo trigger. Trình tự này cũng có thể được thay đổi bằng cách sử dụng trường trigger_order trong khi tạo trình kích hoạt

Q #4) Có thể cập nhật trình kích hoạt hiện tại không?

Câu trả lời. Trong MySQL, không có câu lệnh nào để cập nhật hoặc thay đổi trình kích hoạt hiện có. Nếu sau khi tạo trigger, bạn cần thay đổi trigger. Bạn có thể DROP trình kích hoạt hiện có bằng cách sử dụng lệnh DROP TRIGGER và tạo một trình kích hoạt mới có cùng tên

Sự kết luận

Trong hướng dẫn này, chúng ta đã tìm hiểu về khái niệm sử dụng TRIGGERS trong MySQL. Trình kích hoạt cho phép người dùng xác định một hành động tùy chỉnh để phản hồi một sự kiện được xác định trước như CHÈN hoặc CẬP NHẬT một hàng trong bảng khác, v.v.

Kích hoạt rất hữu ích trong các trường hợp sử dụng khác nhau, chẳng hạn như

• Tuân thủ các giá trị được hỗ trợ/xác thực dữ liệu
• xử lý lỗi
• Bật nhật ký kiểm tra cho tất cả các sự kiện thay đổi xảy ra trên một bảng cụ thể

Trình kích hoạt cũng hỗ trợ phần lớn việc sử dụng lại mã và tối ưu hóa hiệu suất bằng cách giảm các cuộc gọi mạng qua lại cho các bản cập nhật riêng lẻ

Trình kích hoạt trong MySQL với ví dụ là gì?

Cú pháp chính xác để tạo trình kích hoạt trong MySQL là gì?

Trình kích hoạt trong MySQL có nghĩa là gì?

Lệnh nào tạo trình kích hoạt?