Mysql #1235 错误代码，当创建 2 个具有不同目的的触发器时

Question

我有一个在 phpmyadmin 中创建的触发器，例如:

BEGIN
UPDATE bands SET 
bands.status = "active"
where bands.id IN(
SELECT * FROM
(select bands.id 
from bands where bands.id IN 
(select bandId from invites join bands 
on(invites.bandId = bands.id) 
where invites.status="approved" 
group by bandId having count(invites.status)=bands.total_personel))AS X);
end

此触发器会将我的手环状态更改为事件状态。

但如果不满足 where 子句，我也想将状态自动更改为“非事件”。

如果我创建另一个触发器，例如:

BEGIN
UPDATE bands SET 
bands.status = "inactive"
where bands.id IN(
SELECT * FROM
(select bands.id 
from bands where bands.id IN 
(select bandId from invites join bands 
on(invites.bandId < bands.id) 
where invites.status="approved" 
group by bandId having count(invites.status)=bands.total_personel))AS X);
end

Mysql 给我 #1235 错误代码。

如何解决这个问题？或者有没有一种方法可以将这些目的组合到一个触发器中？

Answer 1

我已经找到答案了。我之前不知道UPDATE命令可以与WHEN条件结合起来。

这里是代码:

BEGIN
UPDATE bands SET status = CASE
WHEN bands.id IN
(SELECT * FROM
(select bands.id from bands where bands.id IN 
(select bandId from invites join bands 
 on(invites.bandId = bands.id) 
 where invites.status="approved" 
 group by bandId having count(invites.status)=bands.total_personel))AS X) 
 then "active"
 ELSE "inactive"
 END
 WHERE bands.id in (select distinct invites.bandId from invites);
 END

该Mysql代码如果满足when条件则将状态更改为active，如果不满足WHEN条件则自动将状态更改为inactive。