MySQL 触发器不能在 UPDATE 语句中使用同一个表

Question

基本上，我尝试创建一个触发器，通过添加俯卧撑、引体向上和 3 英里运行的分数来更新我的 300 挑战表中的分数。 3 英里运行的分数在下面的大 SELECT 中。

create trigger update_wscore
AFTER INSERT
ON 300challenge FOR EACH ROW

Create table temp(3mile_score integer);
insert into temp(3mile_score) 
select 
  case 
    WHEN TIMEDIFF (3mile , '00:18:00') > '00:00:10' 
      THEN
         CASE 
            WHEN
              100 - (EXTRACT(MINUTE FROM TIMEDIFF (3mile, '00:18:00')) * 6 +
              FLOOR(EXTRACT(SECOND FROM TIMEDIFF (3mile, '00:18:00')) / 10)) < 0
            THEN 0
         ELSE 
              100 - (EXTRACT(MINUTE FROM TIMEDIFF (3mile, '00:18:00')) * 6 +
              FLOOR(EXTRACT(SECOND FROM TIMEDIFF (3mile, '00:18:00')) / 10))
         END
  ELSE 100
END
FROM 300challenge
WHERE wscore IS NULL;

UPDATE 300challenge
SET wscore = pushups * 2 + pullups * 5 + (select * from temp);

drop table temp;

我已经尝试在 UPDATE wscore 语句中直接使用 SELECT 替换 select * fom temp 但它不起作用说我不能在 UPDATE 语句内的 SELECT FROM 子句中使用 300challenge 表。

之后，我尝试了上面当前显示的内容，创建了一个临时表，我在其中转储分数，他们读取它以便在 UPDATE 中使用。

但显然这种方法不足以欺骗系统，因为它现在会抛出一个错误，指出 存储函数或触发器中不允许显式或隐式提交。

我的想法有点用完了，所以如果有人能提出一些建议，我们将非常欢迎。

更新:

我现在也尝试从触发器中删除 CREATE TABLE TEMP，在触发器外部创建表(一次)并替换触发器的最后一行，drop table temp 与 truncate table temp，但显然我的网络主机不允许触发器作为TRIGGER 命令拒绝用户 '--'@'localhost' 用于表 '300challenge'

我的代码现在是这样的

CREATE TABLE TEMP (3mile_score INT);

CREATE TRIGGER update_wscore
AFTER INSERT ON 300 challenge
FOR EACH ROW

INSERT INTO TEMP (3mile_score)
SELECT CASE 
    WHEN TIMEDIFF(3mile, '00:18:00') > '00:00:10'
      THEN CASE 
          WHEN 100 - (EXTRACT(MINUTE FROM TIMEDIFF(3mile, '00:18:00')) * 6 +
               FLOOR(EXTRACT(SECOND FROM TIMEDIFF(3mile, '00:18:00')) / 10)) < 0
            THEN 0
          ELSE 100 - (EXTRACT(MINUTE FROM TIMEDIFF(3mile, '00:18:00')) * 6 +
               FLOOR(EXTRACT(SECOND FROM TIMEDIFF(3mile, '00:18:00')) / 10))
          END
    ELSE 100
    END
FROM 300 challenge
WHERE wscore IS NULL;

UPDATE 300 challenge
SET wscore = pushups * 2 + pullups * 5 + (
    SELECT *
    FROM TEMP
    );

TRUNCATE TABLE TEMP;

Answer 1

尝试将其改写为 before insert 触发器并修改 new 记录中的值。

触发器的内部是这样的:

SET new.wscore = old.pushups * 2 + old.pullups * 5 +
    case 
    WHEN TIMEDIFF (old.3mile , '00:18:00') > '00:00:10' 
      THEN
         CASE 
            WHEN
              100 - (EXTRACT(MINUTE FROM TIMEDIFF (old.3mile, '00:18:00')) * 6 +
              FLOOR(EXTRACT(SECOND FROM TIMEDIFF (old.3mile, '00:18:00')) / 10)) < 0
            THEN 0
         ELSE 
              100 - (EXTRACT(MINUTE FROM TIMEDIFF (old.3mile, '00:18:00')) * 6 +
              FLOOR(EXTRACT(SECOND FROM TIMEDIFF (old.3mile, '00:18:00')) / 10))
         END
  ELSE 100
END;