Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger

Author  Topic 

Joetaniarto00
Starting Member

12 Posts

Posted - 2014-09-10 : 21:55:39
Hi All,

I'm new on sql server please tell me how to get best way in creating inventory card trigger. My case is :

I have some tables those are :
1. Item
fields :
ID (master ID)
Name ( Item Name's)
Unit ( Item Unit )

2. Purchase Table
ID (Trans.ID)
Date (Trans.Date)
Supplier (Supplier)
Item ID (Item)
Qty

3. Sales Table
ID (Trans ID)
Cust (Customer)
Date ( trans.date )
qty (number of quantity)

4. Stock Table
Item (Item ID)
Purchase (Qty Purchasing)
Sales (Qty of Sales)
Stock (purchasing-sales)

Please tell the best way for the solution..

thanks a lot..

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-11 : 09:22:51
What do you want the trigger to do?
Go to Top of Page

Joetaniarto00
Starting Member

12 Posts

Posted - 2014-09-11 : 22:17:13
I want to make ,
every purchasing always adding or edit value on the field purchase on table stock and then do formula Stock=Purchasing-Sales. This also happen on the field sales on table stock, So the stock will give me an information about the latest position of purchasing, Sales, and also the stock.

Thx
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 08:53:58
You don't really need a trigger for that. You could put the logic in a proc that must be used to add a new purchase. In that proc, begin a transaction that updates the tables and doesn't commit until all updates are done. Doing it this way gives you some flexibility and added control. Only allow applications to use the proc to do the updates -- no direct table updates. Write one proc to add a purchase, a second to update a purchase and maybe a third to delete a purchase, if that fits your application requirments
Go to Top of Page
   

- Advertisement -