mysql - 如何创建mysql触发器来更新列

Question

我想根据另一个表中设置的值更新新插入行的列。

我创建了这样的东西:

BEGIN
DECLARE value float;
DECLARE comission float;    
DECLARE c float;

select value=`uc_amount` from inserted;

select comission=`config_value` from fixed_configs WHERE `config_key` = 'deal_comission';

c = value - (value * (comission/100));

update user_credits set `uc_admin_amount`=value;

END

但它显示错误

这里是表结构

用户信用:

uc_amount   float(10,2) 
uc_admin_amount float(5,2)
uc_created  timestamp
uc_extra    text

固定配置:

config_key varchar(225) config_value varchar(225)

Answer 1

您基本上需要 2 个触发器，一个在插入之前，一个在更新之前

插入之前将获取uc_amount并计算uc_admin_amount，而更新之前将检查旧的uc_amount code> 与 new uc_amount 不同，如果是，则进行计算。

delimiter //

create trigger ins_user_credit before insert on user_credit
for each row
begin
 declare comission float(5,2);
 select config_value into comission from fixed_configs WHERE config_key = 'deal_comission';
 set new.uc_admin_amount = new.uc_amount - (new.uc_amount * (comission/100));
end; //

delimiter ;


delimiter //

create trigger upd_user_credit before update on user_credit
for each row
begin
 declare comission float(5,2);
 if(old.uc_amount <> new.uc_amount)
   select config_value into comission from fixed_configs WHERE config_key = 'deal_comission';
   set new.uc_admin_amount = new.uc_amount - (new.uc_amount * (comission/100));
 end if ;
end; //

delimiter ;