Safe to convert Sage 100 Premium to Temporal Tables in MS SQL?

Currently on Sage 100 Premium 2021; I need to track changes to AR_RepetitiveInvoiceHeader and AR_RepetitiveInvoiceDetail, but no built-in audit log exists. I've toyed with scripting to a UDT but now that we've moved to Premium, the power of MS SQL is attractive because of the ability to trigger SQL scripts to automate the task (the audit table does not need to be accessible in Sage 100, so does not need to be a UDT and could be completely invisible to Sage 100).

However, during my research I realized an even easier approach might be to add the necessary date/time fields to allow SQL to track changes using temporal tables. It appears the needed date/time fields can be invisible to Sage 100, so in theory it shouldn't cause problems with the application itself.

Before spending a lot of time testing this theory I thought I'd throw it out to the community to see if anyone else is using temporal tables in SQL to track changes to any of their Sage 100 tables, or if there might be reasons to avoid this of which I am unaware. Your thoughts?

  • 0

    Avoid triggers on the SQL tables.  That can cause serious problems if not done absolutely perfectly.

    If your concern is "who did what?", a BOI script (post-write) is your best bet.

    If your concern is more "what changed today?" an external mirror table with scripted external log table could work nicely.

  • 0 in reply to Kevin M

    Okay, good to know... triggers would have been my second choice as it could be done strictly in SQL, and as data would be written to a table not even visible to Sage seemed preferable and rather straightforward. 

    But my original question remains: what about adding two DATETIME2 columns (SysStartTime and SysEndTime) within SQL to store Period data, then turning on system versioning. Per MS SQL documentation, setting these columns as HIDDEN would prevent Sage from being aware of their existence, but I would hesitate to do that as I don't know how Sage would react during future customizations (add/remove UDFs to such a table) or upgrades, and I'm not sure it would be necessary. Ideally I would create the columns as UDFs in Sage Custom Office, so Sage is aware of but ignores them, while SQL maintains them.

    i would really love for Sage to weigh in on this question, because adding two fields and allowing SQL to automatically record changes to a history table would be SO easy... and no easy means exists to create audit logs for all the tables I need. I'm sure we're not the only users with the desire to track changes (especially to repetitive invoice tables), so if external mirror tables are the recommended route is there a deep dive available to jump start this process? I see no need to reinvent the wheel...

    Thank you!

  • 0 in reply to jcbaker

    I remember a case where creating a table in a MAS_TST database blocked company copy functions (refreshing the test company data).

    I've never added a column to a Sage table manually, because I can always just create a UDF.  Sage Date field come as DateTime in SQL, and you can set DateTime values using SQL, but be aware the time portion may be reset when the record is edited by Sage 100 business objects.

    BOI scripts are safe, because they are designed to work with Sage programming.  SQL scripts against Sage tables can cause problems with table locks (been there, done that, got the T-shirt).

    I had one upgrade recently where a 3rd party put SQL triggers into critical tables, breaking the posting process (data corruption) after an upgrade (because the database their trigger was writing to wasn't there on the new server / SQL instance).  Don't just think about what you can do quickly... think of the big picture of what could go wrong.