Need help with audit trigger

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






Start Free Trial