<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>
mysql——两张表(双向触发器)增、删、更新完美测试通过,互不影响
最新推荐文章于 2024-07-29 03:57:40 发布