SQLite触发器:在另一个表上插入后更新一个表

Question

我有三个主表来跟踪products、location和它们之间的logistics，其中包括将产品往返于不同位置。我制作了另一张表balance，以保持各个位置的每个产品数量的最终平衡。

以下是模式：

products(prod_id INTEGER PRIMARY KEY AUTOINCREMENT,
prod_name TEXT UNIQUE NOT NULL,
prod_quantity INTEGER NOT NULL,
unallocated_quantity INTEGER)

最初，添加产品时，prod_quantity和uncated_quantities具有相同的值。每次分配一定数量的相应产品时，都会从中减去unallocated_quantity。

location(loc_id INTEGER PRIMARY KEY AUTOINCREMENT,
loc_name TEXT UNIQUE NOT NULL)
logistics(trans_id INTEGER PRIMARY KEY AUTOINCREMENT,
prod_id INTEGER NOT NULL,
from_loc_id INTEGER NULL,
to_loc_id INTEGER NOT NULL,
prod_quantity INTEGER NOT NULL,
trans_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(prod_id) REFERENCES products(prod_id),
FOREIGN KEY(from_loc_id) REFERENCES location(loc_id),
FOREIGN KEY(to_loc_id) REFERENCES location(loc_id))
balance(prod_id INTEGER NOT NULL,
loc_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
FOREIGN KEY(prod_id) REFERENCES products(prod_id),
FOREIGN KEY(loc_id) REFERENCES location(loc_id))

在logistics中的每个条目中，我都希望有一个触发器来更新balance中的值，从而保持所有交易(在位置之间移动产品(的摘要

我想到了一个触发器解决方案，它检查对于表logistics上的每个插入，balance表中是否已经存在相同的prod_id, loc_id条目，如果存在，将适当更新该条目。然而，我没有在SQLite中实现这个想法的经验。

Answer 1

我相信您的触发器将沿着以下两种方式之一：-

CREATE TRIGGER IF NOT EXISTS logistics_added AFTER INSERT ON logistics
BEGIN
UPDATE balance SET quantity = ((SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id) - new.prod_quantity) WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id;
UPDATE balance SET quantity = ((SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id) + new.prod_quantity) WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id;
END;

或：-

CREATE TRIGGER IF NOT EXISTS logistics_added AFTER INSERT ON logistics
BEGIN
INSERT OR REPLACE INTO balance VALUES(new.prod_id,new.from_loc_id,(SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.from_loc_id) - new.prod_quantity);
INSERT OR REPLACE INTO balance VALUES(new.prod_id,new.to_loc_id,(SELECT quantity FROM balance WHERE prod_id = new.prod_id AND loc_id = new.to_loc_id) + new.prod_quantity);
END;

请注意，第二个依赖于通过使用PRIMARY KEY (prod_id,loc_id)或交替使用UNIQUE (prod_id,loc_id)向平衡表添加UNIQUE约束。UNIQUE约束可能是必需的/需要的。

微妙的区别在于，如果不存在适当的平衡行，第二个会插入一个平衡行。如果不存在适当的余额行，则后者将不起任何作用。