mysql - 我写了一个触发器。但我在使用 case 语句时遇到错误

Question

触发器:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `customersh`$$

CREATE
    TRIGGER `customersh` AFTER UPDATE ON `customers` 
    FOR EACH ROW BEGIN

IF old.customer_status_id !=new.customer_status_id  THEN
    INSERT INTO customers_h (
    status_changed_date,
    status_changed_by,
    loan_request_initial_id,
    verification_approval_id,
    ) VALUES
    ( 
    old.status_changed_date,
    old.status_changed_by,
    (CASE WHEN old.customer_status_id IN (2,3,4) AND new.customer_status_id IN (5,7,3,4)  
    THEN SELECT MAX(loan_request_initial_id) FROM loan_request_initial 
    WHERE customer_id = old.customer_id ELSE NULL END),

    old.verification_approval_id,
    );
END IF;
    END;
$$
DELIMITER ;

错误:

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(loan_request_initial_id) FROM loan_request_initial ' at line 106

请帮我解决这个错误。案例陈述可能是错误的。

Answer 1

正确检查括号

CASE WHEN old.customer_status_id IN (2, 3, 4)
  AND new.customer_status_id IN (5, 7, 3, 4) THEN (
    SELECT
      MAX(loan_request_initial_id)
    FROM
      loan_request_initial
    WHERE
      customer_id = old.customer_id
  ) ELSE NULL END