请大神帮忙,我想写个更新触发器,即在update某一字段后,修改另一表中的字段数据,但无法获取所修改的字段所在id,所以请问怎样方式获取?注意是mysql,所以代码最好符合mysql规范,有代码优先给分,谢谢啦。
写了个,看适合你不
DELIMITER $$
DROP TRIGGER IF EXISTS `update_test` $$
CREATE
TRIGGER `update_test` AFTER UPDATE
ON `test`
FOR EACH ROW BEGIN
-- SELECT id FROM test WHERE yl = new.yl 获取test表中yl字段修改后的对应id
UPDATE test2 SET cl = new.yl WHERE id = (SELECT id FROM test WHERE yl = new.yl);
END$$
DELIMITER ;
DELIMITER $$
DROP TRIGGER IF EXISTS `update_test` $$
CREATE
TRIGGER `update_test` AFTER UPDATE
ON `test`
FOR EACH ROW BEGIN
-- SELECT id FROM test WHERE yl = new.yl 获取test表中yl字段修改后的对应id
UPDATE test2 SET cl = new.yl WHERE id = (SELECT id FROM test WHERE yl = new.yl);
END$$
DELIMITER ;
原test表
原test2表
修改test表中yl字段
现在test表
现test2表
至于如何修改test2表中的字段值,按你需求写吧。
温馨提示:答案为网友推荐,仅供参考
第1个回答 推荐于2017-08-22
mysql触发器更新获取更新行所在id
写了个,看适合你不
DELIMITER $$
DROP TRIGGER IF EXISTS `update_test` $$
CREATE
TRIGGER `update_test` AFTER UPDATE
ON `test`
FOR EACH ROW BEGIN
-- SELECT id FROM test WHERE yl = new.yl 获取test表中yl字段修改后的对应id
UPDATE test2 SET cl = new.yl WHERE id = (SELECT id FROM test WHERE yl = new.yl);
END$$
DELIMITER ;本回答被网友采纳
写了个,看适合你不
DELIMITER $$
DROP TRIGGER IF EXISTS `update_test` $$
CREATE
TRIGGER `update_test` AFTER UPDATE
ON `test`
FOR EACH ROW BEGIN
-- SELECT id FROM test WHERE yl = new.yl 获取test表中yl字段修改后的对应id
UPDATE test2 SET cl = new.yl WHERE id = (SELECT id FROM test WHERE yl = new.yl);
END$$
DELIMITER ;本回答被网友采纳