mysql触发器数据库同步数据

-- 创建users表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(50)
);

-- 创建user_logs表
CREATE TABLE user_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(10),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建AFTER INSERT触发器
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'INSERT');
END;
//
DELIMITER ;

-- 创建AFTER UPDATE触发器
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'UPDATE');
END;
//
DELIMITER ;

-- 创建AFTER DELETE触发器
DELIMITER //
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (OLD.id, 'DELETE');
END;
//
DELIMITER ;