Log changes to database table with trigger

Question

I am looking for a good way to log changes that occur on a particular set of tables in my SQL Server 2005 database. I believe the best way to do this is through a trigger that get's executed on updates and deletes. Is there anyway to grab the actual statement that is being ran? Once I have the statement I can easily log it someplace else (other DB table). However, I have not found an easy way (if possible) to grab the SQL statement that is being ran.

Answer 1

If you just want to keep a log of all transactions (insert, update and delete) in some database tables, then you can run the following script:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
  CREATE TABLE LogTable
  (
    LogID [int]IDENTITY(1,1) NOT NULL,
    Type char(1), 
    TableName varchar(128), 
    PrimaryKeyField varchar(1000), 
    PrimaryKeyValue varchar(1000), 
    FieldName varchar(128), 
    OldValue varchar(1000), 
    NewValue varchar(1000), 
    UpdateDate datetime DEFAULT (GetDate()), 
    UserName varchar(128)
  )
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables 
WHERE 
--query for table that you want to audit
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'LogTable'
AND TABLE_NAME!= 'one table to not record de log';

WHILE @TABLE_NAME IS NOT NULL
  BEGIN

  SELECT 'PROCESANDO ' + @TABLE_NAME;

  EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')


  SELECT @sql = 'create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
    as
      declare 
        @bit int ,
        @field int ,
        @maxfield int ,
        @char int ,
        @fieldname varchar(128) ,
        @TableName varchar(128) ,
        @PKCols varchar(1000) ,
        @sql varchar(2000), 
        @UpdateDate varchar(21) ,
        @UserName varchar(128) ,
        @Type char(1) ,
        @PKFieldSelect varchar(1000),
        @PKValueSelect varchar(1000)

        select @TableName = ''' + @TABLE_NAME+ '''

        -- date and user
        select @UserName = system_user ,
        @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

        -- Action
        if exists (select * from inserted)
          if exists (select * from deleted)
            select @Type = ''U''
          else
            select @Type = ''I''
        else
          select @Type = ''D''

        -- get list of columns
        select * into #ins from inserted
        select * into #del from deleted

        -- Get primary key columns for full outer join
        select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
          from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
          where pk.TABLE_NAME = @TableName
          and CONSTRAINT_TYPE = ''PRIMARY KEY''
          and c.TABLE_NAME = pk.TABLE_NAME
          and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        -- Get primary key fields select for insert(comma deparated)           
        select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '','''''' 
          from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
          where pk.TABLE_NAME = @TableName
          and CONSTRAINT_TYPE = ''PRIMARY KEY''
          and c.TABLE_NAME = pk.TABLE_NAME
          and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        -- Get primary key values for insert(comma deparated as varchar)           
        select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))'' + ''+'''','''''' 
          from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,    
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
          where  pk.TABLE_NAME = @TableName   
          and CONSTRAINT_TYPE = ''PRIMARY KEY''   
          and c.TABLE_NAME = pk.TABLE_NAME   
          and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 

        if @PKCols is null
        begin
          raiserror(''no PK on table %s'', 16, -1, @TableName)
          return
        end

        select @sql = ''insert LogTable(Type, TableName, PrimaryKeyField, PrimaryKeyValue, UserName)''
        select @sql = @sql + '' select '''''' + @Type + ''''''''
        select @sql = @sql + '','''''' + @TableName + ''''''''
        select @sql = @sql + '','' + @PKFieldSelect
        select @sql = @sql + '','' + @PKValueSelect
        select @sql = @sql + '','''''' + @UserName + ''''''''

        select @sql = @sql + '' from #ins i full outer join #del d''
        select @sql = @sql + @PKCols        

        exec (@sql)
    ';
  SELECT @sql
  EXEC(@sql)


  SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables 
    WHERE TABLE_NAME> @TABLE_NAME
    --query for table that you want to audit
    AND TABLE_TYPE= 'BASE TABLE' 
    AND TABLE_NAME!= 'sysdiagrams'
    AND TABLE_NAME!= 'LogTable'
    AND TABLE_NAME!= 'one table to not record de log';
END

Answer 2

You should be able to accomplish this using the system management views.

An example would be something like this:

SELECT er.session_id,
  er.status,
  er.command,
  DB_NAME(database_id) AS 'DatabaseName',
  user_id,
  st.text
FROM sys.dm_exec_requests AS er
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = @@SPID;

I'm not sure this will be as useful to you as a more data-centric logging mechanism might be, though.

Answer 3

Don't forget that your logging will be part of the transaction so if there is an error and you rollback the transaction, your log will also be deleted.

Answer 4

MSSQL has virtual tables named 'Inserted' and 'Deleted', which contain newly-inserted and/or newly-deleted and/or newly-updated data records, which you can access from a trigger ... I use these, to know what data has changed (that's instead of being told what statement changed the data).

Answer 5

There is a pattern for creating such these triggers called Log Trigger. This is vendor independent and very simple. It is described in here.

The changes are recorded on another history table. There is no way to grab the exact statement, but it is possible to detect if it was an insertion, and update or a deletion because it creates a "chained" set of records. An insertion is a record with no predecessor, a deletion is a record with no successor, intermediate records are updates. Changes can be detected comparing a record against its predecessor.

It is very easy to get a snapshot of a single entity (or the whole table) at a given point of time.

As a bonus, the syntax of this pattern for SQL Server happens to be the simplest one, compared to Oracle, DB2 and MySQL.

Answer 6

Use a Log Trigger

There is little reason to capture the actual SQL as there can many different statements that change data in the same way.

Answer 7

do you really need to log the statement that ran, most people log the changed data (INSERTED and DELETED tables within the trigger).

Answer 8

Triggers are bad, I'd stay away from triggers.

If you are trying to troubleshoot something, attach Sql Profiler to the database with specific conditions. This will log every query run for your inspection.

Another option is to change to calling program to log its queries. This is a very common practice.

Answer 9

Triggers are a good way to ensure that any changes are logged, since they will almost always fire regardless of how the updates are performed - e.g. ad-hoc connections as well as application connections.

As suggested by @mwigdahl, the system management views look like a good way to capture the current running batch. Whether that's particularly useful to log in the trigger is another thing.

One downside to using triggers is that you can only identify the source of the update from the database connection. Many applications don't have any user information associated with the connection, to facilitate connection pooling, so you don't know which user is performing the action. ie the Login used by the connection is a generic application login rather than the person using the application. The normal way to get around this is to use stored procedures as the interface to all database interaction, and then ensure that a UserId is passed with all procedure calls. You can then perform your logging via the stored procedure instead of a trigger. Clearly this is only useful if you know people won't update tables directly without using the procedures, or don't need to log that situation.

The ability to get the currently executing batch might provide an even better mechanism: if you ensure that all your sql batches contain a UserId you could then extract this from the sql within your trigger. That would allow you to do all logging with triggers, which means you capture everything, but also let you associate changes with a particular user.

If you're going down the trigger route it's worth checking the situations triggers aren't fired (maybe bulk loaded data? or if people have permission to disable triggers).

Also consider as @idstam pointed out that trigger code will be within your transaction so will normally be logged and rolled back along with it.

Another thing to consider when writing triggers is the behaviour of @@IDENTITY: if you have procedures using @@IDENTITY you might accidentally change their behaviour.

Answer 10

Try installing some trigger based third party tool such as ApexSQL Audit and then reverse engineering how they do it. Just install it in trial mode and see how it generates triggers for capturing all different sorts of information.

Several other things to consider are:

Storage planning – if you have a lot of updates that means you’ll have a ton of audit data. I’d consider storing that data in separate databases. Especially if you plan on auditing more than one database.

Managing the amount of data – over time you probably won’t be in a need to keep some very old records. Plan on easy deletion of old data

Schema changes – what if schema is updated. In worst case your triggers will stop working and throw an error if not created correctly. At best case you’ll miss some of the data. This is also something to consider.

Taking all of this into account it is probably the most time effective to go with some already developed solution vs creating this from scratch yourself.

Answer 11

This is adapted from Juan Carlos Velez's answer. I modified it to account for compound primary keys, and for column names that include spaces. Also, I commented it throughout so that someone who wants to modify it for their purposes can understand what is happening at each step, if the code is not clear to them.

-- This stops the message that shows the count of the number of rows affected from being returned as part of the result set.
set nocount on

-- If the Audit table doesn't exist, create it.
if not exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Audit')
create table Audit
(
    AuditID [int] identity(1,1) not null,
    [Type] char(1), 
    TableName nvarchar(128), 
    PKFields nvarchar(max),
    PKValues nvarchar(max),
    FieldName nvarchar(128), 
    OldValue nvarchar(max), 
    NewValue nvarchar(max), 
    UpdateDate datetime, 
    UserName nvarchar(128)
)
go

-- Variables for the dynamic SQL and table name.
declare @tr nvarchar(max),
@tableName sysname

-- Get the first table in database. Skip over views and a few specified tables.
select @tableName = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'sysdiagrams' and TABLE_NAME <> 'Audit'
---- If you want to specify certain tables, uncomment the next line and add your table names.
--and (TABLE_NAME = 'IGAs' or TABLE_NAME = 'IGABudgets' or TABLE_NAME = 'Resolutions' or TABLE_NAME = 'RTAProjects' or TABLE_NAME = 'RTASubProjects')

-- Loop through the tables in the database and create an audit trigger on each one.
while @tableName is not null
begin

    -- If a trigger of the same name already exists, delete it.
    exec('if OBJECT_ID (''' + @tableName + '_ChangeTracking'', ''TR'') is not null drop trigger ' + @tableName + '_ChangeTracking')

    -- Check if there is a primary key. If not, throw an error.
    if (select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u where c.TABLE_NAME = @tableName and c.CONSTRAINT_TYPE = 'PRIMARY KEY' and u.TABLE_NAME = c.TABLE_NAME and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME) = 0
    begin
        raiserror('Error: There is no primary key on table %s', 16, -1, @tableName)
        return
    end

    -- Create the trigger.
    select @tr = 'create trigger ' + @tableName + '_ChangeTracking on ' + @tableName + ' for insert, update, delete as

    -- Misc variables.
    declare @table nvarchar(128),
    @fieldName nvarchar(128) = '''',
    @type char(1),
    @pkJoin nvarchar(max),
    @pkSelect nvarchar(max),
    @pkFields nvarchar(max),
    @pkValues nvarchar(max),
    @updateDate nvarchar(30) = convert(varchar(30), getdate(), 22),
    @user nvarchar(128) = system_user,
    @sql nvarchar(max),
    @params nvarchar(max) = N''@out nvarchar(max) output'',
    @fieldIndex int = 0,
    @maxField int,
    @bit int,
    @char int

    -- Get the table name.
    select @table = object_name(parent_id) from sys.triggers where object_id = @@PROCID

    -- Get the modification type: U = update, I = insert, D = delete
    if exists (select * from inserted)
        if exists (select * from deleted)
            select @type = ''U''
        else select @type = ''I''
    else select @type = ''D''

    -- Save the inserted and deleted values into temp tables.
    select * into #ins from inserted
    select * into #del from deleted

    -- Get the number of columns in the table.
    select @maxField = max(columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'')) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table

    -- Get the primary key join relationship(s).
    select @pkJoin = coalesce(@pkJoin + '' and'', '' on'') + '' i.['' + u.COLUMN_NAME + ''] = d.['' + u.COLUMN_NAME + '']''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    where c.TABLE_NAME = @table
    and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
    and u.TABLE_NAME = c.TABLE_NAME
    and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME

    -- Get the primary key field name(s).
    select @pkFields = coalesce(@pkFields + '', '', '''') + ''['' + u.COLUMN_NAME + '']''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    where c.TABLE_NAME = @table
    and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
    and u.TABLE_NAME = c.TABLE_NAME
    and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME

    -- Get the primary key field(s) for select statement.
    select @pkSelect = coalesce(@pkSelect + '' + '''', '''' + '', '''') + ''convert(nvarchar(max), ['' + u.COLUMN_NAME + ''])''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS c, INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
    where c.TABLE_NAME = @table
    and c.CONSTRAINT_TYPE = ''PRIMARY KEY''
    and u.TABLE_NAME = c.TABLE_NAME
    and u.CONSTRAINT_NAME = c.CONSTRAINT_NAME

    -- Get the primary key field value(s).
    if (@type = ''D'')
    begin
        set @sql = ''select @out = '' + @pkSelect + '' from #del''
        exec sp_executesql @sql, @params, @out = @pkValues output
    end
    else
    begin 
        set @sql = ''select @out = '' + @pkSelect + '' from #ins''
        exec sp_executesql @sql, @params, @out = @pkValues output
    end

    -- Loop through each field in the inserted table.
    while @fieldIndex < @maxField
    begin

        -- Iterate the fieldIndex.
        select @fieldIndex = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table and columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'') > @fieldIndex 

        -- If the column in scope has been modified, insert a record into the Audit table.
        select @bit = (@fieldIndex - 1)% 8 + 1
        select @bit = POWER(2, @bit - 1)
        select @char = ((@fieldIndex - 1) / 8) + 1
        if substring(columns_updated(), @char, 1) & @bit > 0 or @Type IN (''I'', ''D'')
        begin

            -- Get the name of the field whose ColumnID equals the current fieldIndex.
            select @fieldName = ''['' + COLUMN_NAME + '']'' from INFORMATION_SCHEMA.COLUMNS 
            where TABLE_NAME = @table and columnproperty(object_id(@table), COLUMN_NAME, ''ColumnID'') = @fieldIndex '

    -- Select statements have a length limitation. End the statement, then add the rest.
    select @tr = @tr + '

            set @sql = ''insert into Audit (Type, TableName, PKFields, PKValues, FieldName, OldValue, NewValue, UpdateDate, UserName) select '''''' + @type + '''''', '''''' + @table + '''''', '''''' + @pkFields + '''''', '''''' + @pkValues + '''''', '''''' + @fieldName + '''''', convert(nvarchar(max), d.'' + @fieldName + ''), convert(nvarchar(max), i.'' + @fieldName + ''), '''''' + @updateDate + '''''', '''''' + @user + '''''' from #ins i full outer join #del d'' + @pkJoin + '' where i.'' + @fieldName + '' <> d.'' + @fieldName + '' or (i.'' + @fieldName + '' is null and d.'' + @fieldName + '' is not null) or (i.'' + @fieldName + '' is not null and d.'' + @fieldName + '' is null)''

            --print(@sql)
            exec(@sql)

        end
    end'

    ---- This is if you want to see the statement that is generated rather than execute it.
    --select @tr

    -- Execute the trigger statement.
    exec(@tr)

    -- Iterate the table name.
    select @tableName = min(TABLE_NAME) from INFORMATION_SCHEMA.TABLES 
    where TABLE_NAME > @tableName and
    TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'sysdiagrams' and TABLE_NAME <> 'Audit'
    ---- If you want to specify certain tables, uncomment the next line and add your table names.
    --and (TABLE_NAME = 'IGAs' or TABLE_NAME = 'IGABudgets' or TABLE_NAME = 'Resolutions' or TABLE_NAME = 'RTAProjects' or TABLE_NAME = 'RTASubProjects')

end

Answer 12

Be careful here, since triggers fire at the ROW level, not the SQL STATEMENT level. So, if someone does "DELETE FROM BIGTABLE", your trigger will fire for each row in that table (this specifically in regard to the fact that you want to know the SQL statement that performed the operation, so you'll need to "figure that out" for every row the statement affects).