Dvoituron.Tools.SqlTraceGenerator 1.1.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'

Sample:

SqlTraceGenerator --config=myconfig.json --output=myscript.sql

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              __UpdatedBy    EMPNO    ENAME 
  U            147658           2019-07-12 16:21:51    system       7369     Smith 
  U            148709           2019-07-12 16:21:56    system       7369     Denis 
  U            148977           2019-07-12 16:21:58    system       7369     Christophe
  U            149224           2019-07-12 16:22:00    system       7369     Richard

Install

dotnet tool install Dvoituron.Tools.SqlTraceGenerator -g

Uninstall

dotnet tool uninstall Dvoituron.Tools.SqlTraceGenerator -g