MySQL trigger syntax and application examples

This article uses examples to illustrate the syntax and application of MySQL triggers. Share with you for your reference, the details are as follows:

Example: Create a trigger to record table addition, deletion, and modification operations

//Create user table;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `account` varchar(255) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `address` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
//Create a history table for user table operations DROP TABLE IF EXISTS `user_history`;
CREATE TABLE `user_history` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `operatetype` varchar(200) NOT NULL,
 `operatetime` datetime NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER: Change the input terminator. By default, the input terminator is a semicolon;. Here, it is changed to two semicolons;;. The purpose of this is to encapsulate multiple statements containing semicolons and execute them together after all are entered, rather than automatically executing them when the default semicolon terminator is encountered.

new: Available when insert and update events are triggered, pointing to the record being operated on
old: Available when delete and update events are triggered, pointing to the record being operated on

INSERT:

DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
  INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user', now());
end
;;
DELIMITER ;

UPDATE:

DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
  INSERT INTO user_history(user_id,operatetype,operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;

DELETE:

DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
  INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
end
;;
DELIMITER ;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in: