Dvoituron.Tools.SqlTraceGenerator 1.2.0

SQL Server Trace Script Generator

Generate a SQL Script with triggers to trace data changes in 'Traces' tables.

After installation, use SqlTraceGenerator --config=[config_file.json] --output=[script.sql] --maxdays=[99]

Arguments:

--config    Configuration json file (see format below).
            If not set, use appsettings.json located in current folder.
--output    Generated SQL file to execute.
            Existing file will be overwritten.
--maxdays   Maximum days recorded for each traces
            when a new trace is saved.

In your application, call this SQL method to trace the user: EXEC sp_set_session_context 'user_id', 'denis' The colums __Columns contains the bit pattern indicating the updated columns. See COLUMNS_UPDATED().

Sample:

SqlTraceGenerator --config=myconfig.json

myconfig.json
{
   "ConnectionString": "Server=MyServer;Database=MyDatabase;",
   "Output": "scripts.sql",
   "Generate": {
      "Triggers": true,
      "Tables": true
   },
   "Purge": {
      "MaximumDays": 365
   },
   "Include": [
      "dbo.mytable1.*",
      "dbo.mytable2.myname",
   ],
   "Exclude": [
      "dbo.mytable1.id",
   ],
}

After data changes, the [Traces].[dbo_mytable1] contains:

__Operation  __TransactionId  __Updated           __Columns  __UpdatedBy    EMPNO    ENAME     
  U            147658           2019-07-12 16:21    0x0200     system       7369     Smith     
  U            148709           2019-07-12 16:23    0x0600     system       7369     Denis     
  U            148977           2019-07-12 16:24    0x0600     system       7369     Christophe
  D            149224           2019-07-12 16:28    0x         system       7369     Richard   

Install

dotnet tool install Dvoituron.Tools.SqlTraceGenerator -g

Uninstall

dotnet tool uninstall Dvoituron.Tools.SqlTraceGenerator -g

Release notes

• 2019-07-12 - version 1.1
  • First version
• 2019-07-22 - version 1.2
  • Fix the trace to delete a row. Now, two triggers are generated to traces Updates and Deletes separately.
  • Add the column __Columns with the COLUMNS_UPDATED() bit pattern indicating the updated columns.