Dynamic table problem - SQL Server Forums

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

SQL Server 2008 Forums

SQL Server Administration (2008)

Dynamic table problem

Author

Topic

maniac
Starting Member

3 Posts

Posted - 2010-07-15 : 13:28:51

Hi,

Just so you know, I searched on internet about this topic few times, tried doing it myself but nothing so I'm posting my problem here.

I have one database its for selling some stuff, and i made desktop application for it and an web application. The problem i have now is that i want to make table in database that sums the number of items in some other table, with same ID.

Like this:
merchandise table
merchandiseID name etc...
1
3
4

table bought_merchandise:

tableID merchandiseID quantity
1 1 10
2 3 2
3 4 5
4 1 8

table merchandise_in_stock

tableID merchandiseID number_in_stock
1 1 18
2 3 2
3 4 5

Is it possible that this table merchandise_in_stock, changes values every time i buy merchandise and add it into table bought_merchandise:. I did this with SELECT INTO clause but, it only saves query as a table, and table is static...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 14:27:17

I don't understand what you want to do with a SELECT INTO in this case...
IMHO you have an application which is saving data.
So if your app is going to save a record in table bought_merchandise then why not make an update to your stock table after that?

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

maniac
Starting Member

3 Posts

Posted - 2010-07-15 : 14:34:20

I just said, that I need select into type of solution, but so it is dynamic. Select into is just saving query result.., and i need something like that but with dynamic result, so it can change if i change data.

I could do that, make update in app, but I wanted to try doing that in sql, because it would be a lot easier for later use.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 14:39:31

create a trigger on the table bought_merchandise.
The trigger has do to:
update m
set number_in_stock = number_in_stock - i.quantity
from merchandise_in_stock as m
join inserted as i
on i.merchandiseID = m.merchandiseID

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 14:41:22

But I really would say that the application should have control about that and not the database.

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

maniac
Starting Member

3 Posts

Posted - 2010-07-15 : 14:47:51

Thanks, I'll try this.

And later I'll try doing this in app. One thing I want in app, is that it doesn't have anything to do with user, so if user is entering some quantity number of items that he bought, it adds that number on the number that was in stock.

And in another case, if a costumer buys some item, stock number of those items is reduced by the number that costumer bought. And that all that is done automatically without user knowing it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources