mysql - 通过 mySQL 触发器将行插入另一个表

Question

尝试创建触发器以将一个表中的行插入到另一个表中时遇到语法错误...在此过程中更新日期和时间。这是我的查询:

INSERT INTO `second_table` (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12)
  (SELECT `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9` FROM `first_table` WHERE `field1` = NEW.`field1`), CURDATE(), NOW(), (SELECT `field12` FROM `second_table` WHERE `field1` = NEW.`field1`);

此触发器将在 first_table 更新时运行。我不确定这是否是完成任务的正确方法。为了简单起见，我概括了字段和表名称。

编辑:

两个表中的列是相同的。我只希望能够在触发发生时更新日期和时间。这可行，但显然只是复制之前的日期和时间:

INSERT INTO `second_table` (SELECT * FROM `first_table` WHERE `field1` = NEW.`field1`);

Answer 1

尝试:

INSERT INTO `second_table` (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12)
 (SELECT `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`, CURDATE() AS `field10`, NOW() AS `field11`, `field12` FROM `first_table` WHERE `field1` = NEW.`field1`);