Dvoituron.Tools.SqlTraceGenerator 1.2.0

dotnet tool install --global Dvoituron.Tools.SqlTraceGenerator --version 1.2.0
This package contains a .NET tool you can call from the shell/command line.
dotnet new tool-manifest # if you are setting up this repo
dotnet tool install --local Dvoituron.Tools.SqlTraceGenerator --version 1.2.0
This package contains a .NET tool you can call from the shell/command line.
#tool dotnet:?package=Dvoituron.Tools.SqlTraceGenerator&version=1.2.0
nuke :add-package Dvoituron.Tools.SqlTraceGenerator --version 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.
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.2 is compatible.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

This package has no dependencies.

Version Downloads Last updated
1.2.0 573 7/22/2019
1.1.0 472 7/12/2019