触发器应用
目的
- insert数据
- document新增数据时候在dadata表里面新增数据并且状态设置为0
- update数据
- document的状态由0变成1触发更新dadata表的操作
- document的状态为1时其他字段发生变化
- delete数据
- document的数据删除则对应的dadata表数据删除
- insert数据
建表sql
document
SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `document`; CREATE TABLE `document` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `title` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; INSERT INTO document VALUES ('1', '触发器测试1'); INSERT INTO document VALUES ('2', '触发器测试2');
dadata
DROP TABLE IF EXISTS `dadata`; CREATE TABLE `dadata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pid` varchar(50) DEFAULT NULL, `status` varchar(10) DEFAULT '0000000000', PRIMARY KEY (`id`), UNIQUE KEY `1` (`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
触发器
insert触发器
CREATE TRIGGER test_insert AFTER INSERT ON document FOR EACH ROW BEGIN if(NEW.status=1) THEN INSERT INTO dadata(pid) VALUES (NEW.id); END IF; END;
update触发器
CREATE TRIGGER test_updata AFTER UPDATE ON document FOR EACH ROW BEGIN if(OLD.status!=NEW.status && NEW.status=1) THEN INSERT INTO dadata(pid) VALUES (NEW.id) ON DUPLICATE KEY UPDATE status='00000000'; elseif(OLD.status=NEW.status && NEW.status=1 && (NEW.title!=OLD.title)) THEN UPDATE dadata SET status="0000000000" where pid=NEW.id; END IF; END;
delete触发器
CREATE TRIGGER test_delete AFTER DELETE ON document FOR EACH ROW BEGIN UPDATE dadata SET status="3333333333" where pid=OLD.id; END;
测试
请在document表中新增数据 , 修改数据 ,删除数据,在操作后查询dadata表的status的变化
此处忽略sql