mysql - mySQL 中的触发器更新列值

Question

我编写了此触发器来根据客户的收入更新列...

CREATE TRIGGER `TRG_CUST_CODE` BEFORE INSERT ON `CUSTOMER_DIM`
 FOR EACH ROW SET NEW.CUST_INCOME_AM = IF(NEW.CUST_INCOME_AM >= 55000, "H",
                        IF(NEW.CUST_INCOME_AM >= 40000 and NEW.CUST_INCOME_AM < 55000, "M",
                            IF(NEW.CUST_INCOME_AM >= 15000 AND NEW.CUST_INCOME_AM < 40000, "L","P"))
                        )

但是所有值都设置为 P...我是否遗漏了一些我忽略的内容？

Answer 1

我不能代表你的数据，但我会将此触发器写为:

CREATE TRIGGER `TRG_CUST_CODE` BEFORE INSERT ON `CUSTOMER_DIM`
FOR EACH ROW
BEGIN
    SET NEW.CUST_INCOME_AM = (CASE WHEN NEW.CUST_INCOME_AM >= 55000
                                   THEN 'H'
                                   WHEN NEW.CUST_INCOME_AM >= 40000 and NEW.CUST_INCOME_AM < 55000
                                   THEN 'M'
                                   WHEN NEW.CUST_INCOME_AM >= 15000 AND NEW.CUST_INCOME_AM < 40000
                                   THEN 'L'
                                   ELSE 'P'
                              END);
END;