Post reply

Observe db field changes for reporting

  • September 17, 2016 at 6:03 am

    #330295

    What is the best way to identify fields that are updated upon completing a transaction, in an ERP subsystem that uses SQL Server db?

    I will be designing reports for well-documented requirements, that for now will go against a DW populated from the normalized application database. The vendor only provided table descriptions and I need to identify tables.fields to bring over to the DW.

    We've profiled the tables but to pare down what to bring to the DW I would like to be able to perform a transaction, then identify the tables.fields that were updated.

    Simplified use case would be something like this:

    - user hits Add in the application

    - system updates table1.field1, table2.field2

    - I view the list of tables.fields and data that were just updated.

    I'll analyse/search data in that list to identify tables and fields I want.

    I'd like to go with tools/functionality that are delivered with SQL Server, rather than buy a third-party tool.

    Thank you!

  • September 17, 2016 at 3:01 pm

    #1901511

    Create a "universal" trigger to capture the table schema name, the table name, the PK column(s) value(s), and the value of the COLUMNS_UPDATED(). Translate the values of COLUMNS_UPDATED() after you've captured the information so that the triggers don't slow things down.

    Also, read your agreement with the vendor. They may not allow for such "Reverse Engineering" and, if they get wise to it, could nullify any support agreements you may be paying for.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 17, 2016 at 3:30 pm

    #1901512

    As a, perhaps, easier alternative, fire up SQL Server Profiler and look for RPC complete for the login of the app.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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