MySQL触发器实战-优快云博客

本文链接：https://blog.youkuaiyun.com/javahepeng/article/details/7920143

本文介绍了一个MySQL数据库中关于触发器的实际应用案例。通过创建表tab1和tab2，并定义了在增删改操作时同步数据到tab2的触发器bbs1、bbs2和bbs3，实现了数据的自动同步。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP TABLE IF EXISTS `tab1`;
CREATE TABLE `tab1` (
`tab1_id` varchar(11) default NULL,
`name` varchar(45) default NULL,
`pass` varchar(45) default NULL,
`tel` varchar(45) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*!添加表1数据*/;
INSERT INTO `tab1` (`tab1_id`,`name`,`pass`,`tel`) VALUES
('tteeyy','test','yyrr','13545789545');

#创建插入表数据触发器
DROP TRIGGER /*!50030 IF EXISTS */ `bbs2`;

DELIMITER $$

CREATE DEFINER = `root`@`localhost` TRIGGER `bbs2` AFTER INSERT ON `tab1` FOR EACH ROW insert into tab2(tab2_id,name,pass,phone) values(NEW.tab1_id,new.name,new.pass,new.tel) $$

DELIMITER ;

#创建更新表数据触发器
DROP TRIGGER /*!50030 IF EXISTS */ `bbs1`;

DELIMITER $$

CREATE DEFINER = `root`@`localhost` TRIGGER `bbs1` AFTER UPDATE ON `tab1` FOR EACH ROW update tab2 set tab2_id=NEW.tab1_id,name=new.name,pass=new.pass,phone=new.tel where tab2_id=NEW.tab1_id $$

DELIMITER ;

#创建删除表数据触发器
DROP TRIGGER /*!50030 IF EXISTS */ `bbs3`;

DELIMITER $$

CREATE DEFINER = `root`@`localhost` TRIGGER `bbs3` AFTER DELETE ON `tab1` FOR EACH ROW delete from tab2 where tab2_id=old.tab1_id $$

DELIMITER ;

#建表

DROP TABLE IF EXISTS `tab2`;
CREATE TABLE `tab2` (
`tab2_id` varchar(11) default NULL,
`name` varchar(45) default NULL,
`pass` varchar(45) default NULL,
`phone` varchar(45) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tab2` (`tab2_id`,`name`,`pass`,`phone`) VALUES
('tteeyy','test','yyrr','13545789545');

--------------------------------------------------------------------------

#修改的触发器
CREATE TRIGGER bbs1 AFTER UPDATE ON tab1 FOR EACH ROW update tab2 set

tab2_id=NEW.tab1_id,name=new.name,pass=new.pass,phone=new.tel where

tab2_id=NEW.tab1_id;
END;

#添加的触发器
CREATE TRIGGER bbs2 AFTER insert ON tab1 FOR EACH ROW insert into tab2

(tab2_id,name,pass,phone) values(NEW.tab1_id,new.name,new.pass,new.tel);
END;

#删除的触发器
CREATE TRIGGER bbs3 AFTER delete ON tab1 FOR EACH ROW delete from tab2

where tab2_id=old.tab1_id;
END;

#删除触发器

drop trigger 触发器名字