Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is how to use mysql triggers. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

Example: create a trigger to record the operations of adding, deleting and changing the table

/ / create user table DROP TABLE IF EXISTS `user`; CREATE TABLE `user` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `account` varchar (255,255) DEFAULT NULL, `address` varchar (25551) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8; / / create a history table of operations on user tables DROP TABLE IF EXISTS `user_ roomy`; CREATE TABLE `user_ opery` (`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; the purpose of this is to encapsulate multiple statements with semicolons and execute them together after they have been typed, rather than automatically as soon as the default semicolon Terminator is encountered.

New: available when the insert and update events are triggered, pointing to the operated record old: available when the delete and update events are triggered, pointing to the operated record

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`user` FOR EACH ROW begin INSERT INTO user_history (user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now ()); end;;DELIMITER. After reading this article on how to use mysql triggers, if you think the article is well written, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.