Mysql 双向关联触发器

最新推荐文章于 2021-10-23 14:57:41 发布

转载最新推荐文章于 2021-10-23 14:57:41 发布 · 273 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/chen-lhx/p/5601249.html

文章标签：

#数据库

本文介绍了一种通过MySQL触发器实现的双向关联机制，当在vaccine.demo表中插入、更新或删除数据时，同步操作wechat.demo表，反之亦然。这种机制确保了两个表之间的数据一致性。

双向关联触发器

//增加
delimiter // 
create trigger InsertDemo
AFTER insert on vaccine.demo
for each row 
Begin
if not exists (select * from wechat.demo where wechat.demo.id = new.id) then
INSERT INTO wechat.demo(wechat.demo.`name`,wechat.demo.`password`) VALUES(new.name,new.password);
end if; 
end;//
delimiter ;


delimiter // 
create trigger InsertDemo
AFTER insert on wechat.demo
for each row 
Begin
if not exists (select * from vaccine.demo where vaccine.demo.id = new.id) then
INSERT INTO vaccine.demo(vaccine.demo.`name`,vaccine.demo.`password`) VALUES(new.name,new.password);
end if; 
end;//
delimiter ;

 

//删除
CREATE TRIGGER de
After DELETE ON vaccine.demo
FOR EACH ROW
BEGIN
if exists (select * from wechat.demo where wechat.demo.id = old.id) then
DELETE FROM wechat.demo WHERE wechat.demo.id= OLD.id;
end if;
END;

 

CREATE TRIGGER de
After DELETE ON wechat.demo 
FOR EACH ROW
BEGIN
if exists (select * from vaccine.demo where vaccine.demo.id = old.id) then
DELETE FROM vaccine.demo WHERE vaccine.demo.id = OLD.id;
end if;
END;

//修改

CREATE TRIGGER up
After UPDATE ON vaccine.demo
FOR EACH ROW
BEGIN
if not exists (select * from wechat.demo where wechat.demo.id = old.id and wechat.demo.`name`=new.name and wechat.demo.`password`=new.password) then
update wechat.demo set wechat.demo.`name`=new.name ,wechat.demo.`password`=new.password where wechat.demo.id =old.id;
end if;
END;

 

CREATE TRIGGER up
After UPDATE ON wechat.demo 
FOR EACH ROW
BEGIN
if not exists (select * from vaccine.demo where vaccine.demo.id = new.id and vaccine.demo.`name`=new.name and vaccine.demo.`password`=new.password) then
update vaccine.demo set vaccine.demo.`name`=new.name ,vaccine.demo.`password`=new.password where vaccine.demo.id =old.id;
end if;
END;