Solved

Need help with audit trigger

Posted on 2008-06-10
2
565 Views
Last Modified: 2008-06-28
Hi Experts,
I need help with an audit trigger. I was using one previously that I thought was pretty sweet but was informed that it was actually wrong on many levels..... Some of you may recognize it.

ALTER        trigger tr_Audit_Students on dbo.Students for update
as
declare
    @bit int ,
    @field int ,
    @maxfield int ,
    @char int ,
    @studID varchar(50),
    @fieldname varchar(128) ,
    @Students varchar(128) ,
    @PKCols varchar(1000) ,
    @sql varchar(4000),
    @dtUpdated varchar(21) ,
    @RacfID varchar(128)



    select @Students = 'Students'
    select @studID = (Select studID FROM inserted i)  

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

      
 

    -- 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 = @Students
    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, @Students)
        return
    end
   
    select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Students
    while @field < @maxfield
    begin
        select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Students and ORDINAL_POSITION > @field
        select @bit = (@field - 1 )% 8+ 1
        select @bit = power(2,@bit - 1)
        select @char = ((@field - 1) / 8) + 1
        select @studID  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Students and COLUMN_NAME = @studID
        if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0
        begin
            select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Students and ORDINAL_POSITION = @field
            select @sql =         'insert AUDIT (TableName, PK_ID, FieldName,OldValue, NewValue, dtUpdated, RacfID )'
            select @sql = @sql +     ' select ''' + @Students + ''''
            select @sql = @sql +     ',''' + @studID + ''''
            select @sql = @sql +     ',''' + @fieldname + ''''
            select @sql = @sql +     ',convert(varchar(1000),d.' + @fieldname + ')'
            select @sql = @sql +     ',convert(varchar(1000),i.' + @fieldname + ')'
            select @sql = @sql +     ',''' + @dtUpdated + ''''
            select @sql = @sql +     ',''' + @RacfID + ''''
            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

I need to create a new trigger that will insert into the Audit table the following fields when only an update occurs to a record...

TableName
PrimaryKey
FieldName
OldValue
NewValue
Date Updated
UserId of the person who changed it.

Another question, should I have a different audit table for each table in the database that I need to track?

Thanks






0
Comment
Question by:tis9700
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Expert Comment

by:ev72178
ID: 21752642
I recommed reading the following article:
http://www.sqlservercentral.com/articles/Auditing/63247/
There's a Part 2 to the article as well.
The author recommends using the log files instead of procs or triggers. He even recommends some apps that can read them. Way more efficient in terms of databse overhead.
0
 

Accepted Solution

by:
tis9700 earned 0 total points
ID: 21752762
Thanks ev72178,
 But we're not allowed to purchase third-party apps that aren't on contract.

I was thining of a trigger that uses the Inserted and Deleted tables. I created a separate table called dbo.StudentAudit so I could eliminate the need to store the tablename.

Alter trigger tr_Audit_Students on dbo.Students
For Update
as
Begin
     If (Select Count(*) From inserted > 0
          Begin
                If (Select Count(*) From deleted) > 0
                   Begin Insert Into dbo.StudentAudit
                      (PK_ID, FieldName, OldValue, NewValue, dtUpdated, RacfID)
This is where it starts getting fuzzy for me

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question