How to realize inventory function by trigger in sql

This article mainly shows you the "sql trigger how to achieve inventory function", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "sql trigger how to achieve inventory function" this article.

Table 1, in-warehouse table column: in-warehouse order number part number in-warehouse quantity table 2, out-of-library table column: out-of-warehouse order number part number out-of-warehouse quantity table 3: part information table column: part number `inventory quantity I want to realize each time when entering the warehouse, the "inventory quantity" in the "part information table" should be added "incoming quantity", and when leaving the warehouse, "inventory quantity" to subtract "outgoing quantity", that is, what should be done to update inventory? Write triggers? how to write triggers? There is still a better way, please master to help me, it is better to have a detailed code, thank you!

Best answer

Generally speaking, the connection between inventory operation documents and inventory balance is realized through the client software, that is, the SQL code is added to the audit operation button of the inventory receipt. If this is not the case, you can also achieve it through triggers, but a sound process should include ensuring changes to the parts information table when inserting, modifying and deleting. 1. CREATE TRIGGER Tr_ entry table ON entry table after insert,update,deleteASbegin update a set a. Inventory quantity = a. Stock quantity + b. Incoming quantity from part information table a, (select part number, sum (incoming quantity) from inserted group by part number) b-if the part number is not repeated each time, it is not necessary to aggregate where a. Part number = b. Part number; go update a set a. Inventory quantity = a. Inventory quantity-b. Incoming quantity from part information table a, (select part number, sum (incoming quantity) from deleted group by part number) b-if the part number is not repeated each time, it is not necessary to aggregate where a. Part number = b. Part number: goend2, CREATE TRIGGER Tr_, ON, after insert,update,deleteASbegin update a set a. Inventory quantity = a. Inventory quantity-b. Outgoing quantity from part information table a, (select part number, sum (outgoing quantity) outgoing from inserted group by part number) b-if the part number is not repeated each time, it is not necessary to aggregate where a. Part number = b. Part number; go update a set a. Inventory quantity = a. Stock quantity + b. Outgoing quantity from part information table a, (select part number, sum (outgoing quantity) outgoing from deleted group by part number) b-if the part number is not repeated each time, it is not necessary to aggregate where a. Part number = b. Part number; goend above is all the content of this article "how to achieve inventory function in sql trigger". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!