mysql - 如何通过添加和减去带有时间戳的字段来更新数量字段？

Question

我有三个表:

CREATE TABLE cartons_current (
    part_no varchar(20) NOT NULL PRIMARY KEY,
    qty INT(8) NOT NULL,
    qty_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cartons_added
    part_no VARCHAR(20) NOT NULL PRIMARY KEY,
    add_qty INT(8) NOT NULL,
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cartons_pulled (
    part_no VARCHAR(20) NOT NULL PRIMARY KEY,
    pull_qty INT(8) NOT NULL,
    pull_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

我首先在 cartons_current 中创建一条新记录，并在“part_no”和“qty”列中插入值。

接下来，我稍后需要使用另外两列进行添加和拉取，从“qty”中添加或拉取(减去)。

如何让“qty”显示 cartons_added 和 cartons_pulled 的最新结果？

我还需要为每个新增、添加和拉取添加时间戳。

Answer 1

这个问题已经写满了trigger:

DELIMITER $$

CREATE TRIGGER ai_cartons_added_each AFTER UPDATE ON cartons_added 
FOR EACH ROW
BEGIN
  UPDATE cartons_current cc SET cc.qty = cc.qty + NEW.qty_added - OLD.qty_added;
END $$

DELIMITER ;

如果你遵循@Michael的建议，将所有数据放在一张表中，你可以让MySQL计算current_qty:

DELIMITER $$

CREATE TRIGGER ai_cartons_each BEFORE UPDATE ON cartons 
FOR EACH ROW
BEGIN
  SET new.current_qty = old.current_qty 
                      + (new.qty_added - old.qty_added)
                      - (new.qty_pulled - old.qty_pulled);
END $$

DELIMITER ;

在 BEFORE UPDATE 触发器中，您可以更改值，这样您就可以在其中发挥时间戳魔法，存储最后的更改。

CREATE TRIGGER ai_cartons_each BEFORE UPDATE ON cartons 
FOR EACH ROW
BEGIN
  IF new.qty_added <> old.qty_added THEN 
    SET new.added_timestamp = NOW();
  END IF;
END $$

DELIMITER ;