有两个数据库的表 filemanage的tbluser表和ishelves的admin表,我想让tbluser表变化是admin表跟着变化,不知错在那里,请高手指教啊
tbluser:suer spwd spower ueverdata
admin:suer spwd spower stelDELIMITER |
CREATE TRIGGER tg_tbluser_admin_insert
AFTER INSERT
ON filemanage.tbluser
FOR EACH ROW
BEGIN
INSERT INTO ishelves.admin(suser,spwd,spower) SELECT (suser,spwd,spower) FROM filemanage.tbluser WHERE filemanage.tbluser.suser=NEW.suser;
END;|
DELIMITER ; DELIMITER |
CREATE TRIGGER tg_tbluser_admin_delete
AFTER DELETE
ON filemanage.tbluser
FOR EACH ROW
BEGIN
DELETE FROM ishelves.admin WHERE filemanage.tbluser.suser=OLD.suser;
END;
|
DELIMITER ;
DELIMITER |
CREATE TRIGGER tg_tbluser_admin_update
AFTER UPDATE
ON filemanage.tbluser
FOR EACH ROW
BEGIN
DELETE FROM ishelves.admin WHERE ishelves.admin.suser=OLD.suser;
INSERT INTO ishelves.admin SELECT (suser,spwd,spower,stel) FROM mother WHERE filemanage.tbluser.suser = OLD.suser;
END;
|
DELIMITER ;
tbluser:suer spwd spower ueverdata
admin:suer spwd spower stelDELIMITER |
CREATE TRIGGER tg_tbluser_admin_insert
AFTER INSERT
ON filemanage.tbluser
FOR EACH ROW
BEGIN
INSERT INTO ishelves.admin(suser,spwd,spower) SELECT (suser,spwd,spower) FROM filemanage.tbluser WHERE filemanage.tbluser.suser=NEW.suser;
END;|
DELIMITER ; DELIMITER |
CREATE TRIGGER tg_tbluser_admin_delete
AFTER DELETE
ON filemanage.tbluser
FOR EACH ROW
BEGIN
DELETE FROM ishelves.admin WHERE filemanage.tbluser.suser=OLD.suser;
END;
|
DELIMITER ;
DELIMITER |
CREATE TRIGGER tg_tbluser_admin_update
AFTER UPDATE
ON filemanage.tbluser
FOR EACH ROW
BEGIN
DELETE FROM ishelves.admin WHERE ishelves.admin.suser=OLD.suser;
INSERT INTO ishelves.admin SELECT (suser,spwd,spower,stel) FROM mother WHERE filemanage.tbluser.suser = OLD.suser;
END;
|
DELIMITER ;
tbluser:suer spwd spower ueverdata
admin:suer spwd spower stel2. SELECT (suser,spwd,spower) FROM filemanage.tbluser 这种改成 SELECT suser,spwd,spower FROM filemanage.tbluser WHEREINSERT触发器就应该没问题了,但你的UPDATE中又出现了个 FROM mother WHERE ,无法调试。建议楼主自己改一下。另外提问时也请细心,不要提供一些错误东西让别人来测试!
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
那请问斑竹大人,mysql中实现两个数据库中的表的同步咋搞呢?教教咱们,这个案例值得研究!
所以查了一下mysql版本原来是4.7
mysql版本5.以上才支持触发器,换了一个mysql5问题解决
感谢ACMAIN_CHM宝贵意见