mysql - 在更新 MySQL 触发器之前将旧记录插入其他表

Question

我想在我的数据库中复制一份旧记录。我试过使用触发器，但出了点问题。我正在使用 MySQL Workbench，所以我发布了这段代码:

USE `secus_21`;
DELIMITER $$

CREATE TRIGGER `tsm_tasks_BUPD` BEFORE UPDATE ON tsm_tasks FOR EACH ROW
INSERT INTO `tsm_tasks_history`
    ( `ID_task` , `ID_user` , `ID_project` , `name` , `add_time` , `comment` )
VALUES
    ( OLD.`ID` , OLD.`ID_user` , OLD.`ID_project` , OLD.`name` , NOW() , OLD.`comment` );

更新事件，添加新记录，并尝试改变一些东西。我没有收到任何错误，所以我确信添加已成功完成，但在 tsm_tasks_history 表中仍然没有任何内容。

编辑:

这里是所有的触发语句

-- Full Trigger DDL Statements
-- Note: Only CREATE TRIGGER statements are allowed
DELIMITER $$

USE `secus_21`$$

CREATE TRIGGER `tsm_tasks_BUPD` BEFORE UPDATE ON tsm_tasks FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO `tsm_tasks_history`
    ( `ID_task` , `ID_user` , `ID_project` , `name` , `add_time` , `comment` )
VALUES
    ( OLD.`ID` , OLD.`ID_user` , OLD.`ID_project` , OLD.`name` , NOW() , OLD.`comment` )

$$

CREATE TRIGGER `tsm_tasks_BDEL` BEFORE DELETE ON tsm_tasks FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
INSERT INTO `tsm_tasks_history`
    ( `ID_task` , `ID_user` , `ID_project` , `name` , `add_time` , `comment` )
VALUES
    ( OLD.`ID` , OLD.`ID_user` , OLD.`ID_project` , OLD.`name` , NOW() , OLD.`comment` )

$$

Answer 1

您将分隔符更改为 $$ 但未使用 $$ 完成触发器的创建。所以语句没有完成。

USE `secus_21`;
DELIMITER $$

DROP TRIGGER IF EXISTS tsm_tasks_BUPD;
CREATE TRIGGER `tsm_tasks_BUPD` BEFORE UPDATE ON tsm_tasks FOR EACH ROW
BEGIN
INSERT INTO `tsm_tasks_history`
    ( `ID_task` , `ID_user` , `ID_project` , `name` , `add_time` , `comment` )
VALUES
    ( OLD.`ID` , OLD.`ID_user` , OLD.`ID_project` , OLD.`name` , NOW() , OLD.`comment` );
END $$

DROP TRIGGER IF EXISTS tsm_tasks_BDEL;
CREATE TRIGGER `tsm_tasks_BDEL` BEFORE DELETE ON tsm_tasks FOR EACH ROW
BEGIN
INSERT INTO `tsm_tasks_history`
    ( `ID_task` , `ID_user` , `ID_project` , `name` , `add_time` , `comment` )
VALUES
    ( OLD.`ID` , OLD.`ID_user` , OLD.`ID_project` , OLD.`name` , NOW() , OLD.`comment` );
END $$

DELIMITER ;