如何通过添加和减去带有时间戳的字段来更新数量字段? [英] How do I update a qty field from adding and subtracting fields with timestamps?

问题描述

我有三张桌子:

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"列中插入值.

I first create a new record in cartons_current inserting values in the "part_no" and "qty" columns.

接下来，我稍后需要使用另外两个用于添加和拉取的列从qty"中添加或拉取(减去).

Next, I later need to add or pull (subtract) from "qty" using two other columns for adding and pulling.

如何让qty"显示cartons_ added 和cartons_pulled 的最新结果?

How do I make "qty" show the latest result from cartons_added and cartons_pulled?

我还需要对每个新的、添加的和拉取的进行时间戳.

I also need to timestamp each new, add and pull.

推荐答案

这个问题有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:

If you follow @Michael's advice and put all data in one table, you can let MySQL do the calculation for the 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 触发器中，您可以更改值，因此您可以在其中执行时间戳魔术，您可以存储上次更改.

In a BEFORE UPDATE trigger you can change the values, so you can do your timestamp magic in those, you you store the last change.

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 ;

这篇关于如何通过添加和减去带有时间戳的字段来更新数量字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！