Node's Perpetuum Mobile

    yact

    0.2.0 • Public • Published

    yact

    Yet another change tracker for SQL tables. Most software systems require a Change Tracker component for the auditors. This component should record the 5Ws (who, when, where, why and what) of any database table change.

    Microsoft's SQL Server has an out of the box solution called Change Data Capture, which is great. However, you need an Enterprise license to enable this feature and its very expensive; at least for us startups and open sorcerers. yact implements a poor man's change tracking by using SQL triggers to insert an audit row.

    yact's twist is to store the before and after images of the change as XML content as opposed to most other implementations that add a row for each field that was changed. I believe this gives a light weight and flexible (read efficient) to change tracking.

    Getting started

    Install with npm

    > npm install -g yact
    

    Generate the script for the table triggers and the audit table into yact.sql

    > yact -a Employee Payroll
    

    Usage

    > yact -help
    
    Usage: yact-cl [options] [table[:key]...]
    
     Generate the audit trigger script for the table(s)
    
     Options:
    
       -h, --help           output usage information
       -V, --version        output the version number
       -a, --audit          include the script to create the Audit table
       -i, --individual     save the script(s) as individual files.
       -o, --output [file]  save the script(s) to the specified file, the default is "yact.sql"
    
     Examples:
    
       # create trigger script for contact table, primary key is contact_id
       yact contact
    
       # create trigger script for contact table, primary key is id
       yact contact:id
    

    The trigger

    The magic in the trigger is to join the inserted and deleted tables and then convert the rows to XML. These tables are supplied by the SQL server when the trigger is invoked. Each table has the same columns as table_name.

    insert into audit (table_name, old_content, new_content) 
      select 
        @table_name,
        case when d.table_id is null then null else (select d.* for xml raw) end,
        case when i.table_id is null then null else (select i.* for xml raw) end
      from inserted as i
        full outer join deleted as d on i.table_id = d.table_id

    All you need to change is

    • declare\set @table_name
    • change table_id to the name of table's primary key.

    Caveat Emptor

    Triggers that insert (as yact does) change the @@identity value. All stored procedures should at least use scope_identity() instead of @@identity; see how not to retrieve identity value for more issues.

    audit table

    All changes (insert, update or delete) to a yact monitored table are stored in the audit table.

    Column Description
    audit_id A unique key for this audit entry. Keeps an ORM happy.
    operation The SQL operation (insert, update or delete) performed on the table_name.
    table_name What information was changed.
    old_content What was the old information; formatted as XML. Each column of the row is an XML attribute.
    new_content What is the new information; foratted as XML. Each column of the row is an XML attribute.
    who Who changed this information.
    when When was the information changed.
    where Where was the change performed from (IP address of the SQL client).
    why Why was this change performed. Not yet implemented.

    Who are you

    Identity is hard, queue Keith Moon's drum roll. yact uses the system_user for the default who value. This is appopriate for client/server (2-tier) systems; where each user logs into the database. But, for most 3-tier systems, the server has its own account for the database. In this scenario the who must be supplied by the trigger.

    Many 3-tier systems have a modifiedBy column in each table. In this case the trigger can be changed to use this column

    insert into audit (table_name, who, old_content, new_content) 
      select 
        @table_name,
        IsNull(i.modifiedByd.ModifiedBy),
        case when d.table_id is null then null else (select d.* for xml raw) end,
        case when i.table_id is null then null else (select i.* for xml raw) end
      from inserted as i
        full outer join deleted as d on i.table_id = d.table_id

    Time is relative

    yact defaults when to sysdatetimeoffset, which includes the time zone offset of the SQL server. This works if all users are in the same time zone. If the software system has the user's time, then this value should be used in trigger's insert.

    By using the time zone offset, its possible to determine if the user accessed the system after working hours.

    However, its not possible to determine if the user accessed the system during a public holiday.

    Install

    npm i yact

    DownloadsWeekly Downloads

    3

    Version

    0.2.0

    License

    MIT

    Last publish

    Collaborators

    • makaretu