By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

Updating one table when Insert in other table

P: 1
Hi,

I am having two tables Stock and OrderIssue as follow

Stock
ItemID, ItemName , Qty ,
1 , a , 50 ,
2 , b, 50,
3 , c , 50 ,


OrderIssue

OrderID, ItemID, Qty,
1 , 1, 5,
2 , 2, 22,
3 , 1 , 20,



After Inserting order 1 with Qty 5 in Item 1 then Stock Qty should be 50-5=45 for Item 1.
this should be same for each order for each Item

Please help me in designing this query using Trigger.
Mar 7 '07 #1
Share this Question
Share on Google+
1 Reply


P: 62
Dear use this code

Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE TRIGGER [trgSale] ON [dbo].[Sale] 
  3. FOR INSERT
  4. AS
  5.  
  6. Begin
  7.     declare @ItemID int
  8.     declare @Qty float
  9.  
  10.     select @Qty=Qty,@ItemID=ItemID  from inserted
  11.  
  12.     update item Set Qty= isnull(Qty,0)  + @Qty where               ItemID=@ItemID
  13.  
  14. End    
  15.  
  16.  
  17.  
  18.  
Mar 7 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.