MySQL:为什么更新触发器有效，而类似的插入触发器无效

Question

我正在尝试创建两个触发器(插入和更新(，这两个触发器将计算与客户端的会话的价格，然后将其存储在会话表中。

更新触发器起作用，我不明白为什么插入触发器不起同样的作用(触发器没有效果(。

更新触发器：

CREATE TRIGGER triggerCalculateTotalAfterUpdateSession
BEFORE UPDATE
ON session FOR EACH ROW
BEGIN
DECLARE TOTAL decimal(13,2);
IF (OLD.session_units <> new.session_units or OLD.therapist_id <> new.therapist_id or OLD.codeOfService_id <> new.codeOfService_id)  THEN
set NEW.session_total = (
select
if(
NEW.`codeOfService_id` = 8
or NEW.`codeOfService_id` = 10
or NEW.`codeOfService_id` = 13
or NEW.`codeOfService_id` = 22
or NEW.`codeOfService_id` = 23,
`rates`.`rates_rate`,
NEW.`session_units` * `rates`.`rates_rate`
) AS `total`
from
(
(
`session`
left join `therapist` on(
NEW.`therapist_id` = `therapist`.`therapist_id`
)
)
left join `rates` on(
NEW.`codeOfService_id` = `rates`.`codeOfService_id`
and `therapist`.`level_id` = `rates`.`level_id`
)
)where session.session_id = NEW.session_id
);
END IF;
END

插入触发器：

CREATE TRIGGER triggerCalculateTotalAfterInsertSession
before INSERT
ON session FOR EACH ROW
BEGIN
set NEW.session_total = (
select
if(
NEW.`codeOfService_id` = 8
or NEW.`codeOfService_id` = 10
or NEW.`codeOfService_id` = 13
or NEW.`codeOfService_id` = 22
or NEW.`codeOfService_id` = 23,
`rates`.`rates_rate`,
NEW.`session_units` * `rates`.`rates_rate`
) AS `total`
from
(
(
`session`
left join `therapist` on(
NEW.`therapist_id` = `therapist`.`therapist_id`
)
)
left join `rates` on(
NEW.`codeOfService_id` = `rates`.`codeOfService_id`
and `therapist`.`level_id` = `rates`.`level_id`
)
)where session.session_id = NEW.session_id
);
END

表结构会话

表结构治疗师

表结构费率

Answer 1

我用以下方法解决了它：

CREATE TRIGGER triggerCalculateTotalAfterInsertSession before
INSERT
ON session FOR EACH ROW BEGIN
set
NEW.session_total = (
select
if(
NEW.`codeOfService_id` = 8
or NEW.`codeOfService_id` = 10
or NEW.`codeOfService_id` = 13
or NEW.`codeOfService_id` = 22
or NEW.`codeOfService_id` = 23,
`rates`.`rates_rate`,
NEW.`session_units` * `rates`.`rates_rate`
) AS `total`
from
`rates`
left join therapist on NEW.therapist_id = therapist.therapist_id
where
NEW.`codeOfService_id` = `rates`.`codeOfService_id` and rates.level_id = therapist.level_id 
);
END