创建触发器时命令自动换行问题解决

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
delimiter //
CREATE DEFINER=`dba`@`10.96.%` TRIGGER `mostop_xiaodai_loan_repay_plan_AFTER_UPDATE` AFTER UPDATE ON `mostop_xiaodai_loan_repay_plan` FOR EACH ROW
BEGIN
DECLARE o, current_max, history_max INTEGER;
DECLARE ot, dt DECIMAL(11, 2);
DECLARE has_overdue INTEGER;
SET has_overdue = 0;
IF old.repay_status <> new.repay_status OR old.overdue_day <> new.overdue_day THEN
#SELECT loan_status INTO tmp_loan_status FROM mostop_xiaodai_loan_info WHERE agentid = new.agentid AND id = new.loan_id;
SELECT COUNT(IF(overdue_day > 0, 1, NULL)),
MAX(IF(repay_status = 2, overdue_day, 0)),
MAX(overdue_day),
SUM(IF(repay_status = 1, due_amount + due_interest + due_service_interest, 0)),
SUM(IF(repay_status = 2, due_amount + due_interest + due_service_interest, 0)),
SUM(IF((repay_status = 2) OR (DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP()), due_day) = 0 AND repay_status = 1) , 1, 0)) #主订单是否逾期
INTO o, current_max, history_max, dt, ot, has_overdue
FROM mostop_xiaodai_loan_repay_plan WHERE agentid=new.agentid
AND loan_id = new.loan_id;
IF EXISTS(SELECT loan_id FROM mostop_xiaodai_loan_info_ext WHERE agentid = new.agentid AND loan_id = new.loan_id) THEN
UPDATE mostop_xiaodai_loan_info_ext
SET overdue_num = o, max_overdue_day = IF(has_overdue > 0, current_max, history_max), due_total = dt, overdue_total = ot WHERE agentid = new.agentid AND loan_id = new.loan_id;
ELSE
INSERT INTO mostop_xiaodai_loan_info_ext (agentid, loan_id, overdue_num, max_overdue_day, due_total, overdue_total) VALUES (new.agentid, new.loan_id, o, IF(has_overdue > 0, current_max, history_max), dt, ot);
END IF;
UPDATE mostop_xiaodai_loan_info SET max_overdue_day = IF(has_overdue > 0, current_max, history_max) WHERE agentid = new.agentid AND id = new.loan_id;
END IF;
END //
delimiter ;