Architecure: .NET & SQL Server using the Cloud offering

What is the best way to implement logging of record changes?

This would be for a viewable record history and especially for system integrations.  How the log is stored isn't crucial because we would do the processing at a later point.  It could take the form of the straight-up raw SQL that is performing the Update (or Insert), or be the parameterized query using the stored procedure sp_executesql, or some XML storing logs with a before/after.

It seems to me there are 3 options:

  1. Does OutSystems already have a logging table for executed queries?
    1. I've looked and have been unable to find anything like this.  Because if they did, we could just tap into it and move any data to our own specific log table on a timer.
  2. OutSystems already does query optimizations, can we tap into that?
    1. When I modify an attribute's value for an entity that has 30 attributes, the update that is actually run is something like this pseudo-query "UPDATE Customer SET IsActive = 1 WHERE Id = 123".  
    2. From looking further into this it looks like in .NET Framework there is a IDbCommand that has a CommandText that houses the parameterized query.  Is there a way (preferrably documented/supported) to access anything like this through OutSystems API?  If not even this, is there a way to access the change object that is being used to track such changes to objects?
  3. Using DB triggers
    1. Adding Insert/Update triggers that would create a log record in another table based off of the new values.  This is relying on using OutSystems query optimizations so the generated SQL is already a partial-update of the changed attributes.

And a happy new year to all!

Hi Shawn, 

OutSystems do not have any built-in logging to track all those changes.

Regarding tapping into the database API. There's no documented/supported way of doing it.

Using triggers seem to be the winning option in my view, although since you're in the cloud offering, you don't have the correct privileges to manipulate the db at that level. You have to contact outsystems for them to apply your scripts/triggers.