468,321 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,321 developers. It's quick & easy.

Updating one table when Insert in other table

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
1 1177
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.

Similar topics

11 posts views Thread by Jason | last post: by
1 post views Thread by Roy Adams | last post: by
3 posts views Thread by Andreas | last post: by
4 posts views Thread by Geoff | last post: by
3 posts views Thread by RSH | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.