mysql - 创建触发器以影响特定行

Question

我想要它做的是在 B 上插入新行或编辑现有行时，特定的“last_seen”列将在 A 上更新。

这是我目前所拥有的:

CREATE TRIGGER insert_records
AFTER UPDATE ON loan_records
FOR EACH ROW
Update  temp_card A
INNER JOIN loan_records B
SET A.last_seen = CURRENT_TIMESTAMP
WHERE A.card_no = B.card_no

还有这个

CREATE TRIGGER insert_records2
AFTER INSERT ON loan_records
FOR EACH ROW
Update  temp_card A
INNER JOIN loan_records B
SET A.last_seen =CURRENT_TIMESTAMP
WHERE A.card_no = B.card_no

目前这会更新 A 中的所有“last_seen”列，无论它们是否已更新。

Answer 1

您不需要JOIN 两个表。只需在 UPDATE 语句的 WHERE 子句中使用 NEW.card_no。

下面是您的触发器(AFTER INSERT 和AFTER UPDATE)可能的样子

CREATE TRIGGER tg_loan_records_insert
AFTER INSERT ON loan_records
FOR EACH ROW
  UPDATE temp_card 
     SET last_seen = NOW()
   WHERE card_no = NEW.card_no;

CREATE TRIGGER tg_loan_records_update
AFTER UPDATE ON loan_records
FOR EACH ROW
  UPDATE temp_card 
     SET last_seen = NOW()
   WHERE card_no = NEW.card_no;

这是 SQLFiddle 演示