SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 using insert and update to manipulate stock table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

facemeguru
Starting Member

India
1 Posts

Posted - 01/16/2010 :  11:57:09  Show Profile  Reply with Quote
hi friends,
im new to sql. im working on sql server 2000. i got a scenario to be solved in two days which was assigned by my staff.
create two tables namely stock and sales. so that if an insert statement in sales table(i mean if a product is sold)is executed ,then it should automatically update the appropriate quantity in stock table. how can i write query for this scenario? plz help me......

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/16/2010 :  12:42:42  Show Profile  Reply with Quote
You could write a Stored Procedure that inserted a row in the SALES table and adjusted the quantity in the stock table.

Trouble is, any other insert in the SALES table (by someone else) might not also update the Quantity in the stock table ... and then you will have a mess!

The alternative is to create an "AFTER INSERT" Trigger on the SALES table. This trigger will fire WHENEVER a row is inserted in the SALES table - which will mean for any process that inserts a row in the SALES table - so that will cover you if anyone else writes some SQL to insert in the SALES table.

That Trigger can adjust the Quantity in the STOCK table.

If you are not familiar with triggers you will need to read up on them. In particular you need to understand that the trigger fires ONCE PER STATEMENT, and NOT once per row inserted. So if you insert 100 rows, in bulk, in one single SQL statement, the trigger will only fire ONCE. So your Trigger Code must update the Quantity in SALE using SET methodology, and not assume it only has to update one row

Have fun!
Go to Top of Page

bijayani
Starting Member

India
2 Posts

Posted - 01/16/2010 :  23:03:49  Show Profile  Visit bijayani's Homepage  Reply with Quote
Hi,

Though I am not a technical person, I happened to see your post and wanted to share a link where a Software Engineer of my company has shared a tip on SQL Server which is quite similar to your query.

Here is the link:
(Spam removed)

Hope you find it useful.

Thanks,
Bijayani
Proud to be a part of Team Mindfire!
Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
(Spam removed)

Regards
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8683 Posts

Posted - 01/17/2010 :  07:31:24  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by bijayani

Hi,

Though I am not a technical person, I happened to see your post and wanted to share a link where a Software Engineer of my company has shared a tip on SQL Server which is quite similar to your query.

Here is the link:
(spam removed)

Hope you find it useful.

Thanks,
Bijayani
Proud to be a part of Team Mindfire!
Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
(spam removed)

Regards


As the OP stated he is working on SQL Server 2000.
The solution in your provided link needs SQL Server 2008.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 01/17/2010 :  11:05:04  Show Profile  Reply with Quote
Seems bijayani is advertising his company.

PBUH
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 01/18/2010 :  10:04:51  Show Profile  Visit graz's Homepage  Reply with Quote
quote:
Originally posted by Idera

Seems bijayani is advertising his company.

PBUH



Not anymore he's not :)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000