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 2005 Forums
 Transact-SQL (2005)
 How to auto update

Author  Topic 

keesvo
Starting Member

10 Posts

Posted - 2015-03-31 : 07:34:53
Hello,

I want to auto update one column in a table for every new record. I think I have to use a stored procedure but I have no experience with that.

table = items
column = location

So when user 'X' scan a new item it will be inserted into table items.
So the location must be updated to 'location X' for items scanned by user 'X'. Is this possible.

I can also make a query and schedule this but i want this to be updated realtime.

Thank you in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 09:03:34
You don't have to use a stored procedure, though it may be a good idea. Tell us more about the process. I guess it's like this:

A person in a store or warehouse with a hand-held scanner, scans an item. The scanner sends something to a central station that then updates the database.

Is that it?
Go to Top of Page

keesvo
Starting Member

10 Posts

Posted - 2015-03-31 : 09:18:01
yes, we have warehouse users which are linked to one dedicated stock location. So when they are scanning an item a new entry is inserted in the table. But in the software we can't assign an auto location for each user. So now we have to go to a different module to move the item to the right location.

The entry which is created has default no location so it's empty or NULL. Is there a way to fix this so every new entry gets the right location (depending on which user has created this entry) ?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 09:34:53
quote:
Originally posted by keesvo

yes, we have warehouse users which are linked to one dedicated stock location. So when they are scanning an item a new entry is inserted in the table. But in the software we can't assign an auto location for each user. So now we have to go to a different module to move the item to the right location.

The entry which is created has default no location so it's empty or NULL. Is there a way to fix this so every new entry gets the right location (depending on which user has created this entry) ?



Well, if you have the location info available at the time the data is inserted, then yes, of course. So, what exactly IS inserted into the table when the item is scanned?
Go to Top of Page

keesvo
Starting Member

10 Posts

Posted - 2015-03-31 : 09:56:42
The columns which are filled with a new entry are the following:

item_ref, dat_crea, hr__crea, usr_crea, amount, rowid

the column 'location' is also available in this table but is empty when creating (when item is scanned) a new entry.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 10:59:40
quote:
Originally posted by keesvo

The columns which are filled with a new entry are the following:

item_ref, dat_crea, hr__crea, usr_crea, amount, rowid

the column 'location' is also available in this table but is empty when creating (when item is scanned) a new entry.



OK, so from the columns that are filled, which one would you use to fill the 'location' column?
Go to Top of Page

keesvo
Starting Member

10 Posts

Posted - 2015-03-31 : 11:03:08
quote:
Originally posted by gbritton

quote:
Originally posted by keesvo

The columns which are filled with a new entry are the following:

item_ref, dat_crea, hr__crea, usr_crea, amount, rowid

the column 'location' is also available in this table but is empty when creating (when item is scanned) a new entry.



OK, so from the columns that are filled, which one would you use to fill the 'location' column?



the column 'location' must be filled like the following example:
if usr_crea = 'Person A' then column location = 'Stock A'
if usr_crea = 'Person B' then column location = 'Stock B' and so further.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-31 : 11:36:49
OK -- so if you want to, you can create a trigger to do it.

https://msdn.microsoft.com/en-us/library/ms189799.aspx

OTOH, you could redefine 'Location' to be a computed column and add the logic there.
Go to Top of Page
   

- Advertisement -