mysql——两张表（双向触发器）增、删、更新完美测试通过，互不影响

<span style="font-size:18px;color:#FF0000;"><strong>先建数据库：</strong></span><span style="font-size:18px;">
create database mytest;
use mytest;

DROP TABLE IF EXISTS `user1`;
CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `phone` varchar(30) NOT NULL,
  `password` varchar(50) NOT NULL,
  `aaaa` varchar(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

DROP TABLE IF EXISTS `user2`;
CREATE TABLE `user2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ue_name` varchar(30) NOT NULL,
  `ue_phone` varchar(30) NOT NULL,
  `ue_password` varchar(50) NOT NULL,
  `aaaa` varchar(11) NOT NULL,
  `sssss` varchar(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

<span style="color:#FF0000;"><strong>/*插入双向触发*/</strong></span>
DELIMITER //
CREATE TRIGGER tr_Insert_user1
  AFTER INSERT ON user1
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 插入目标表
           insert into user2(ue_name,ue_phone,ue_password) values(new.name,new.phone,new.password);
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_Insert_user2
  AFTER INSERT ON user2
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 插入目标表
        insert into user1(name,phone,password) values(new.ue_name,new.ue_phone,new.ue_password);
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

<strong>/*删除双向触发*/</strong>
DELIMITER //
CREATE TRIGGER tr_Delete_user1
  AFTER DELETE ON user1
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 删除目标表
            delete from user2 where id=old.id;
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_Delete_user2
  AFTER DELETE ON user2
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 删除目标表
        delete from user1 where id=old.id;
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

/*更新双向触发*/
DELIMITER //
CREATE TRIGGER tr_Update_user1
  AFTER UPDATE ON user1
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 更新目标表
            update user2 set ue_name=NEW.name,ue_phone=NEW.phone,ue_password=NEW.password where id=OLD.id;
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER tr_Update_user2
  AFTER UPDATE ON user2
FOR EACH ROW
BEGIN
  -- 检查当前 环境，避免递归.
  IF @disable_trigger IS NULL THEN
    -- 设置禁用触发器标志.
    SET @disable_trigger = 1;
    -- 更新目标表
        update user1 set name=NEW.ue_name,phone=NEW.ue_phone,password=NEW.ue_password where id=OLD.id;
    -- 恢复禁用触发器标志.
    SET @disable_trigger = NULL;
  END IF;
END;
//
DELIMITER ;</span>