Kích hoạt trigger AFTER DELETE trong MySQL

Dùng AFTER DELETE để kích hoạt trigger sau khi thao tác DELETE thực thi. Cú pháp:

DELIMITER // CREATE TRIGGER tenTriggerCuaBan AFTER DELETE ON tenBangCuaBan FOR EACH ROW BEGIN cauLenh1, ... N END; //

Tạo bảng:

mysql> CREATE TABLE DemoTable(Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(100)); Query OK, 0 rows affected (0.62 sec)

Chèn bản ghi:

mysql> INSERT INTO DemoTable(FirstName) VALUES('John'); Query OK, 1 row affected (0.20 sec) mysql> INSERT INTO DemoTable(FirstName) VALUES('Mike'); Query OK, 1 row affected (0.12 sec)

Hiển thị:

mysql> SELECT * FROM DemoTable;

Kết quả:

+----+-----------+ | Id | FirstName | +----+-----------+ | 1  | John      | | 2  | Mike      | +----+-----------+ 2 rows in set (0.00 sec)

Tạo trigger dùng AFTER DELETE:

mysql> DELIMITER // mysql> CREATE TRIGGER history_of_Table AFTER DELETE ON DemoTable FOR EACH ROW BEGIN SELECT USER() INTO @userName; SELECT NOW() INTO @deleteDatetime; END; // Query OK, 0 rows affected (0.18 sec) mysql> DELIMITER ;

Kiểm tra trigger bằng cách xóa bản ghi đầu:

mysql> DELETE FROM DemoTable WHERE Id=1; Query OK, 1 row affected (0.26 sec)

Kiểm tra biến userName:

mysql> SELECT @userName;

Kết quả:

+----------------+ | @userName      | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)

Kiểm tra biến deleteDatetime:

mysql> SELECT @deleteDatetime;

Kết quả:

+---------------------+ | @deleteDatetime     | +---------------------+ | 2019-07-09 21:06:31 | +---------------------+ 1 row in set (0.00 sec)