使用触发器插入后更新另一个表吗? [英] Update another table after insert using a trigger?

问题描述

我当前正在创建某种库存系统.

I'm currently creating some sort of inventory system.

我有tbl1个保存项目的位置.在tbl1中，我有qty列或剩余可用库存.

I have tbl1 where in I save the items. In tbl1, I have column qty or the available stock left.

tbl1:

id product  qty
1  mouse    10
2  keyboard 15
3  monitor  8

我还有表tbl2，在其中我将所有交易保存在发行项目中.在此表中有issued_qty.

I also have the table tbl2 where in I save all the transaction in issuing items. In this table there is issued_qty.

tbl2

id requested_by product  issued_qty
1  john         mouse    2

我的问题是如何在更新tbl1.qty (tbl1.qty - tbl2.issued_qty)的同时将一行插入到tbl2中.

My problem is how can I INSERT a row into tbl2 at the same time UPDATE the tbl1.qty (tbl1.qty - tbl2.issued_qty).

例如: 当我在request_by，product和qty_issued的tbl2中插入记录时，我希望tbl1被自动更新.因为我们向John发出了2(数量)的鼠标. tbl1中的鼠标记录应自动减少到"no". tbl2

for example: when I insert record in tbl2 in requested_by, product and qty_issued, I want the tbl1 to be automatically updated. since we issue 2 (qty) of mouse to John. the record of mouse in tbl1, should be automatically less to the no. of issued quantity in tbl2

我的想法是使用TRIGGER

但是不幸的是我没有成功..你能帮我这个忙吗?.

but unfortunately I have no success.. can you please help me with this one..

谢谢.

推荐答案

如果您不希望有大量数据，请使用视图或存储过程即时计算并返回实际数量.从长远来看，它可以为您节省很多头痛.

If you're not expecting huge amounts of data use a view or a stored procedure to calculate on the fly and return actual quantity. It may save you a lot of headache in the long run.

您的视图(实际上是视图)可能看起来像

Your view (actually views) might look like

CREATE VIEW vw_table2_sum AS
SELECT product, SUM(issued_qty) qty
  FROM Table2
 GROUP BY product;

CREATE VIEW vw_table1 AS
SELECT t.id, t.product, t.qty - COALESCE(v.qty, 0) qty
  FROM Table1 t LEFT JOIN vw_table2_sum v
    ON t.product = v.product;

当我们这样做

SELECT * FROM vw_table1;

将得到

| ID |  PRODUCT | QTY |
-----------------------
|  1 |    mouse |   8 | -- the quantity is current
|  2 | keyboard |  15 |
|  3 |  monitor |   8 |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

现在，如果您出于某种原因想要使用触发器来管理库存数量，则可能看起来像这样

Now if you for some reason want to manage your inventory quantity with a trigger it might look like this

CREATE TRIGGER tg_ai_table2
AFTER INSERT ON table2
FOR EACH ROW
  UPDATE Table1
     SET qty = qty - NEW.issued_qty
   WHERE product = NEW.product;

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于使用触发器插入后更新另一个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！