mysql - 带有 IF-THEN 条件的 Mysql AF​​TER UPDATE 触发器，但没有数据插入

Question

我使用 IF-THEN 条件在表manage_transaction上创建了以下 AFTER UPDATE 触发器，以便将特定数据插入表 manage_site_income_details 但是，既没有将单行插入到manage_site_income_details中，也没有遇到任何错误。但是，我多次修改了我的 IF-THEN 条件，但都没有成功。

DROP TRIGGER IF EXISTS
        `upon_subscription_payment`;
CREATE DEFINER = `test-db-ru-admin`@`%` TRIGGER `upon_subscription_payment` 
AFTER UPDATE
    ON
        `manage_transaction` 
FOR EACH ROW 
    IF 
       OLD.about LIKE 'SUB' AND NEW.status = 'Completed' 
    THEN
    INSERT INTO 
                manage_site_income_details
    VALUES(
        NULL,
        OLD.t_id,
        OLD.txn_id,
        'SUB_Fee',
        OLD.fee,
        NOW());
    END IF

标签： mysqlif-statementtriggersinsert

Answer 1

这里或https://www.db-fiddle.com/f/8UYVnC2xQsWuSC9VBZz6BN/0没问题

DROP TRIGGER IF EXISTS T;
drop table if exists `manage_transaction`,manage_site_income_details;
create table manage_transaction(t_id int,txn_id int,fee int,about varchar(3),status varchar(15));
create table manage_site_income_details(id int,t_id int,txn_id int,txt varchar(20),fee int,dt datetime);
insert into manage_transaction values (1,1,10,'sub',null);

CREATE  TRIGGER T 
AFTER UPDATE ON `manage_transaction` 
FOR EACH ROW 
    IF OLD.about LIKE 'SUB' AND NEW.status = 'Completed' THEN
       INSERT INTO manage_site_income_details
       VALUES(
        NULL,
        OLD.t_id,
        OLD.txn_id,
        'SUB_Fee',
        OLD.fee,
        NOW());
    END IF;

update manage_transaction
    set status = 'completed';

select * from manage_site_income_details;

+------+------+--------+---------+------+---------------------+
| id   | t_id | txn_id | txt     | fee  | dt                  |
+------+------+--------+---------+------+---------------------+
| NULL |    1 |      1 | SUB_Fee |   10 | 2020-05-29 09:58:04 |
+------+------+--------+---------+------+---------------------+
1 row in set (0.001 sec)