using insert and update to manipulate stock table

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

using insert and update to manipulate stock table

Author

Topic

facemeguru
Starting Member

1 Post

Posted - 2010-01-16 : 11:57:09

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

22859 Posts

Posted - 2010-01-16 : 12:42:42

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!

bijayani
Starting Member

2 Posts

Posted - 2010-01-16 : 23:03:49

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-17 : 07:31:24

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