[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2612
  • Last Modified:

How to set up an audit trail in SQL 2005

I have a large database with a very key table (Tbl_General) that lots of people can update.  I need to record all changes done by whom, and when.  I do NOT need to worry about Inserts because they are very carefully controlled, and no DELETEs are allowed.

Is there a setting in SQL 2005 I can use to do this for me?

If not, I was thinking I could add 2 fields to the table (Last_User doing a change, and Date_Time_Changed), then make a copy of the table structure and call it Tbl_Audit_Trail.  A trigger could then copy all of the fields for the record being updated to Tbl_Audit_Trail OR could copy only the fields being changed to Tbl_Audit_Trail.

What SQL statement would I use?  Would this work?
INSERT into Tbl_Audit_Trail (*)
            SELECT * FROM inserted

0
wsturdev
Asked:
wsturdev
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
nathana21Commented:
0
 
nathana21Commented:
0
 
SQL_SERVER_DBACommented:
Have Fun
/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/

--Set up the tables
if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
go
create table Audit (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10))
go
alter table trigtest add constraint pk primary key (i, j)
go

create trigger tr_trigtest on trigtest 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) ,
      @PKSelect varchar(1000)
      
      select @TableName = 'trigtest'

      -- 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 select for insert
      select @PKSelect = coalesce(@PKSelect+'+','') + '''<' + COLUMN_NAME + '=''+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 @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
      while @field < @maxfield
      begin
            select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
            select @bit = (@field - 1 )% 8 + 1
            select @bit = power(2,@bit - 1)
            select @char = ((@field - 1) / 8) + 1
            if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
            begin
                  select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
                  select @sql =             'insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'
                  select @sql = @sql +       ' select ''' + @Type + ''''
                  select @sql = @sql +       ',''' + @TableName + ''''
                  select @sql = @sql +       ',' + @PKSelect
                  select @sql = @sql +       ',''' + @fieldname + ''''
                  select @sql = @sql +       ',convert(varchar(1000),d.' + @fieldname + ')'
                  select @sql = @sql +       ',convert(varchar(1000),i.' + @fieldname + ')'
                  select @sql = @sql +       ',''' + @UpdateDate + ''''
                  select @sql = @sql +       ',''' + @UserName + ''''
                  select @sql = @sql +       ' from #ins i full outer join #del d'
                  select @sql = @sql +       @PKCols
                  select @sql = @sql +       ' where i.' + @fieldname + ' <> d.' + @fieldname
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)'
                  select @sql = @sql +       ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)'
                  exec (@sql)
            end
      end
go


insert trigtest select 1,1,'hi', 'bye'
insert trigtest select 2,2,'hi', 'bye'
insert trigtest select 3,3,'hi', 'bye'
select * from Audit
select * from trigtest
update trigtest set s = 'hibye' where i <> 1
update trigtest set s = 'bye' where i = 1
update trigtest set s = 'bye' where i = 1
update trigtest set t = 'hi' where i = 1
select * from Audit
select * from trigtest
delete trigtest
select * from Audit
select * from trigtest

go
drop table Audit
go
drop table trigtest
go
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dqmqCommented:
The SQL looks OK, but I would call that a "weak" audit trail.  It is easily circumvented just by updating Last_User to junk. I would pick up Last_user and date_time_changed from system variables rather than data content.

INSERT into Tbl_Audit_Trail
      SELECT i.*, system_user, getdate()
      FROM inserted i inner join deleted d on i.pk = d.pk


0
 
SQL_SERVER_DBACommented:
your moms "WEAK" dgmg
0
 
wsturdevAuthor Commented:
dqmq -- your solution was exactly what I had been thinking of...

BUT

SQL_SERVER_DBA -- Your solution might be more useful...

SO

I am trying out both methods to see which I prefer.

In both cases, I am getting the following messages:

On      select * into #ins from inserted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."

On       select * into #del from deleted
I get:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."

I have numerous Text and NText fields in the table I am trying to audit trail (but no image fields).  What do I do.
0
 
dqmqCommented:
Fields with those datatypes are not present in the inserted and deleted tables.  You can leave them out of your audit, like using an explicit list of columns (instead of *) which omits the text columns:

 select col1, col2, col2  into #ins from inserted

If you want them in the audit table, then it's more complicated.  First, it would be difficult capture the "after image" for your audit, but you can capture the "before image", like this:

select D.*, system_user UpdateBy, getdate() UpdateTime into #ins
  from YourTable T inner join Deleted D on D.PK = T.PK
 

 


 
0
 
wsturdevAuthor Commented:
I used the suggestions provided by dqmq to develop one trigger (Scenario 1), and also adapted the solution provided by SQL_SERVER_DBA into another trigger (Scenario 2).

FYI -- Two things to know about this application:

A. Updates to the database can only be done using a front-end executable -- no one has the ability to go directly into the database, such as with Management Studio.  The front-end allows Inserts and Updates, but does not allow any Deletes (instead, we just mark the records as "Out of Scope").

B. Since each record in the target table is assigned to a specific user, the likelihood of 2 people actually updating the same record simultaneously is so close to zero as to not be a concern.

Scenario 1 (using the dqmq solution): The concept of this scenario (and of my original idea) was to create Audit Trail records that contained the entire updated record from the target table.  Later, if necessary, I would be able to select all records for a PK and see the progression of changes.  The problem was that the target table included "text" fields.  

I set up the trigger on the target table to select all columns by name from "inserted" (eliminating the 6 that were "text", which I understand were not in "inserted") and put the selected values into a new record in the Audit Trail table.  

Then, I created a trigger on the Audit Trail table that, on insert, uses the primary key just inserted into the Audit Trail table to grab the now-updated values of those 6 text fields from the target table and update the recorded just inserted into the Audit Trail table.  (Pretty slick, I thought!)

Scenario 2 (using the SQL_SERVER_DBA solution): The concept of this scenario was to create Audit Trail records that contained both the old and new values from only those fields being updated for a given record in the target table.  Later, if necessary, I would be able to select all records for a PK and a given field to follow the trail of changes.

Because of the "text" fields, I had to also select fields from "inserted" by name.  However, because this scenario records both the old and new values, while I knew I could grab the new values, I could figure out no way to grab the old values.

This prompted me to look into why we are using "text" fields instead of "varchar" fields.  It turns out that the maximum length provided by varchars (8,000 chars) is much more than adequate.  So, I changed the data type of the 6 fields to varchar.  Then I went back in to the trigger and did "SELECT *" instead of selecting specific fields.

BOTTOM LINE: While I found Scenario 1 to be an acceptable solution (and the implementation could have been improved from the change of datatype I discovered while working on Scenario 2, thus eliminating the second trigger on the Audit Trail table), I chose Scenario 2 because it only collects the fields that actually change, versus Scenario 1 collecting every field.  The net result is my database will grow more slowly.

Because both of you helped me, and prompted me to learn more than I would have otherwise, I am splitting the points, giving the larger share to SQL_SERVER_DBA for turning me on to a more economical solution.
0
 
dqmqCommented:
Thanks for the followup. It's always nice to see how our contributions played out.

dQm
0
 
wsturdevAuthor Commented:
dqmq - I always try to do that, but sometimes I fail or forget.  When that happens, it is frequently because I am working 29-hour days, 7 days a week!
0
 
wsturdevAuthor Commented:
I need to ask an URGENT follow-up question...

Since I had to do this:
select col1, col2, col2  into #ins from inserted
Rather than this:
select *  into #ins from inserted

Does this mean when I attempt to update the table, I have to always include all of those columns in the update command?

0
 
wsturdevAuthor Commented:
Hit the Submit button too soon....

If that is true, then how can I build a single trigger that will handle multiple types of updates, each with different columns involved?
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now