SQL Multi User Procedures


  • 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.
    I have two workstations - one for Customer Service and one for Payment Processing.

    Here's a scenario -
    1. J-Smith calls Customer service to change his address and review Account Balance. Customer Service opens his account, displays his info, enters new street address but does not save changes to Customer Table immediately.

    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....
    How do we save only changes made (Address) without overwriting any other info for John Smith?

    Ideally Id like to do something like:

    Begin Tran
    UPDATE Customers SET Address="123 Main" WHERE Cust='JSmith'
    Commit 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

  • 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) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • 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.

    =======================================================================

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply