MySQL数据库触发器
文章描述：
mysql触发器实现在操作A表的时候，B表数据也会跟着变化
创建数据库与数据表
主数据库
CREATE DATABASE main_db;主数据库user表
CREATE TABLE main_db.users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
副数据库
CREATE DATABASE replica_db;副数据库表
CREATE TABLE replica_db.users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
创建触发器
INSERT触发器
DELIMITER $$
CREATE TRIGGER `sync_users` AFTER INSERT ON main_db.`users` FOR EACH ROW
BEGIN
INSERT INTO replica_db.users (id, name) VALUES (NEW.id, NEW.name);
END
$$
DELIMITER ;
UPDATE触发器
DELIMITER $$
CREATE TRIGGER `sync_users_update` AFTER UPDATE ON `users` FOR EACH ROW
BEGIN
UPDATE replica_db.users SET `name` = NEW.name WHERE id = OLD.id;
END
$$
DELIMITER ;
DELETE触发器
DELIMITER $$
CREATE TRIGGER `sync_users_delete` AFTER DELETE ON `users` FOR EACH ROW
BEGIN
DELETE FROM replica_db.users WHERE id = OLD.id;
END
$$
DELIMITER ;
添加
INSERT INTO main_db.users (name) VALUES ('Alice');
INSERT INTO main_db.users (name) VALUES ('Bob');
INSERT INTO main_db.users (name) VALUES ('Charlie');
更新
UPDATE `users` SET `name` = 'xiaohuaidan' WHERE `users`.`id` = 1;
删除
DELETE FROM `users` WHERE `users`.`id` = 3;
触发器编辑
触发器导出
发布时间：2023/11/03
发表评论