April 19, 2018 at 4:03 pm
|
Im new to SQL and am using MS SQL 2014 and have multi user question... I have a Customer Database with Customer Table and Payment Table. Here's a scenario - 2. In meantime, payment clerk process payment from J-Smith and also changes his Phone Number in customer Table, Account Balance and saves it. 3. Now, Customer Service rep saves changes made to Customer Table. Now here's the issue.... Ideally Id like to do something like: Begin Tran Is there a way in SQL to find that columns that have changed so we only update one column (address) for Smith rather than attempt to replace all columns for Smith and overwrite Phone Number and Customer_Account_Balance that was changed by Payment Clerk while Customer data was on screen on Customer Service Terminal? Looking or suggestions.... Any help is appreciated |
April 20, 2018 at 8:53 am
First order of business is to understand what kind of business process you want to have surrounding that kind of issue. Last one in wins can be painful, as you've already discovered, and to your customers, it can appear that your company doesn't know what they're doing. The question is, can either of the two business processes wait for the other to complete? The idea is that you add a bit flag column that when someone goes into an edit screen, is checked first before the edits are allowed to take place. That check sets that bit flag to 1 immediately, and perhaps you also add another column to hold the user identifier for who is doing the editing. All update screens have to then check that bit flag to ensure it's 0 before updating it to 1 and populating the user identifying column as well. Any kind of edit screen has to do this, as well as update that edit flag to 0 right after saving the updated record. One would also then have to ensure that any attempt to update that flag to 1 would say WHERE that flag = 0, and then validate @@ROWCOUNT >0 before proceeding. This same logic has to apply to all your edit screens. And all save operations have to be sure to set the bit flag back to 0. It's a fairly extensive set of changes, but it's one of the few ways to handle concurrency effectively without buying something off the shelf that already has that kind of logic built-in.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
May 2, 2018 at 2:53 am
This is where we come across job responsibilities or separation of duties. Only few selected individuals or a group in an organization must have the ability to change the data for the customer. A payment clerk can process the payments but cannot change the personal data for the customer, hence the customer has to be redirected to the other group where they can make changes to customer address, phone # or any other details.
OR
one more option is to use Change Data Capture which will provide info. which columns are changed or the complete row with net changes. This technique is another beast to implement.
---------------------------------------------------------------------------------------------------------------------------------
IrfanHyd
May 7, 2018 at 10:20 pm
You can also use a proc to check if something changed. As sgmuson noted, you can use a semaphore (flag) to not editing. however, what can also be done is that you allow both users to edit. When a user submits, you use a query to check if the values have changed from the original ones. If so, warn the user.
If you want something automatic, there isn't anything. You can use flags or checks, but you have to code them.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply