如何扣除数据库中的项目数量？ [英] How to deduct quantity of items in database?

问题描述

我正在创建一个系统，如果项目被借用/退回，我必须扣除并添加一个系统。在我的系统中，我有2个表：



tblItems（_itemnumber_，_itemname_，_quantity_）

tblBorrow（_dateborrowed_，_datedue_，_status _）



如果我借用了一件物品，将借入状态，数量将扣除1，如果状态被退回，数量将加1。这是我的想法（我已经如何添加和更新）但我不知道如何编码这个想法。我非常感谢你的帮助。



我使用的是进口系统.data.oledb

解决方案

实际上，我看不到你表格之间的关系..



所以，例如，使用'伪'SQL，我认为它就像' update tblItems set _quantity_ = _quantity_ - 1'，并且，你希望这样做''tblBorrow._status_ ='借用''; （??）



如果你在这两个表之间有一个链接，比如_itemnumber_，我可能会说写2个存储过程，



一个用于'借用（_itemnumber）'设置_status_和_quantity_，然后另一个

用于'返回（_itemnumber_）'同上状态和数量适当



但我不太确定你的架构



'g'

< blockquote>您需要使用itemId定义这两个表之间的关系，以便您可以识别借用的项目或返回的项目。之后在表tblBorrow上定义插入/更新触发器。



请在以下博客中查看此示例



在Sql Server中触发 [ ^ ]

I'm creating a system where I must deduct and add by one if the item is borrowed/returned. In my system I have 2 tables:

tblItems(_itemnumber_, _itemname_, _quantity_)
tblBorrow(_dateborrowed_, _datedue_, _status_)

Where if I borrowed an item the status will be borrowed and the quantity will deducted by 1 and if the status is returned the quantity will added by 1. This is my idea(I already how to add and update) but i don`t know how to code this idea. I'd really appreciate your help.

I`m using imports system.data.oledb

解决方案

as it is, I cant see a relation in between the tables you show ..

so, for instance, using 'pseudo' SQL, Im thinking its like 'update tblItems set _quantity_ = _quantity_ - 1', and, you wish to do that 'where tblBorrow._status_ = 'borrowed''; (??)

If you had a link between those two tables, for say an _itemnumber_, I'd probably write say 2 stored procedures,

one for 'borrowed(_itemnumber)' which sets the _status_ and _quantity_, then another
for 'returned(_itemnumber_)' ditto status and quantity appropriately

but Im not very sure of your schema

'g'

You need to define relationship between these two tables using itemId so you can identify which item borrowed or which returned. After that define insert/update trigger on table tblBorrow.

please see this example in following blog

Trigger in Sql Server[^]

这篇关于如何扣除数据库中的项目数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！