Question

求一个自动执行的MYSQL存储过程的写法

数据库表名 user sys_kaka

需要在存储过程中执行的更新语句：
update user set jifen=(select sum(level) from sys_kaka where id=user.id and pid=2)*10;

要求：当 sys_kaka表里数据 发生变化的时候 数据库自动执行以上更新语句 该怎么 写呢？求高手回答下！！！

Answer 1

用触发器来完成DELIMITER |CREATE TRIGGER trigger_insert AFTER INSERT ON sys_kakaFOR EACH ROW BEGIN update user set jifen=(select sum(level) from sys_kaka where id=user.id and pid=2)*10;

END |CREATE TRIGGER trigger_insert AFTER update ON sys_kakaFOR EACH ROW BEGIN
update user set jifen=(select sum(level) from sys_kaka where id=user.id and pid=2)*10;
END |

追问

这样当 sys_kaka 更新的是 就可以自动触发了吗？

追答

是的
用触发器来完成
DELIMITER |
CREATE TRIGGER trigger_insert AFTER INSERT ON sys_kaka
FOR EACH ROW
BEGIN
update user set jifen=(select sum(level) from sys_kaka where id=user.id and pid=2)*10;

END |

CREATE TRIGGER trigger_insert AFTER update ON sys_kaka
FOR EACH ROW BEGIN
update user set jifen=(select sum(level) from sys_kaka where id=user.id and pid=2)*10;
END |