MySQL触发器在插入之前修改col

Question

我想在插入记录之前修改col!我有交易表，对于每笔新交易，我想设置 0.5% 佣金。

问题是:他总是在 amount 列中插入 0。但我想要当前当前值 x 0.50%

DROP TRIGGER IF EXISTS `viptest`.`testing`;
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `viptest`.`commission_before_insert` BEFORE INSERT
    ON `viptest`.`transaction_history`
    FOR EACH ROW        
    BEGIN
       SET NEW.amount = NEW.amount % 0.50';
    END$$

DELIMITER ;

Answer 1

试试这个

DROP TRIGGER IF EXISTS `viptest`.`testing`;
DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `viptest`.`commission_before_insert` BEFORE INSERT
ON `viptest`.`transaction_history`
FOR EACH ROW        
BEGIN

   -- If you just want to modify current row, this line should work
   SET NEW.amount = NEW.amount * 0.5 / 100;

   -- If you want to process this commission into another table, you can use this line
   insert into db_name.table_name (col1, col2, col3)
   select (val1, NEW.amount * 0.5 / 100, val3);

END$$

DELIMITER ;