Solved

Need help with audit trigger

Posted on 2008-06-10
2
581 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 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