Skip to main content

Field Change, table update




  • Best answer
    Jon Green

    You have a few options here that you could use to capture this information. 

    1. Within the DSP framework we have auditing capability that can be switch on for any page and that would capture any changes made to data in a page and record this to an audit table, which is not exactly what you are asking for but would capture this information and more.

    2. You can create and register a Stored Procedure as a business rule that would be assigned to the onValidate event for the page.  Within the stored procedure you could compare the value in the source table to that in the target and if missing insert the record or if existing but changed then update the value and date of the change.

    3. If you just want to capture the change to one or more specific columns, you have the option to just put a trigger on the table within SQL Server so that the on insert / on update events are captured and then written to the specific target table you want to write the entry to.  This is not specifically within the DSP framework but another option used when developing apps in the DSP.

  • Kurt Vandergriend


    If you want to track when the latest change is made to ANY field, you can use standard DSP, which will record this in the ChangedOn field.  Also, all custom webapp tables should contain these additional columns to support auditing, locking, and record status: boaStatus INT, AddedBy nvarchar(50), AddedOn smalldatetime, AddedVia nvarchar(50), ChangedBy nvarchar(50), ChangedOn smalldatetime, ChangedVia nvarchar(50), LockedBy nvarchar(50), LockedBy nvarchar(50), LockedOn smalldatetime. These columns can easily be added to all tables by using the Append Columns toolbar button on the Vertical view of the Data Source in Admin > Data Sources.

    In addition, you can turn on auditing for any table, which will track all changes.  This is done in Admin > Datasource > Audit > Tables.  Go to the online help in DSP (click on ?), and search for "Enable Auditing" to get details of how to proceed from there.

  • Torch Locklear

    Hi Kurt,

    Yes, I have appended all of the default fields to all tables.  The problem with using those for this purpose is that I would wind up with multiple rows for each record on the primary table, whereas what I am trying to do is to have a single change table just for the statuses.  

    Thank you though!

  • Torch Locklear

    Thanks, Jon.  I'll look at putting together the SQL for option 2 as it looks like my best option.


Please sign in to leave a comment.