Solved

Need help with audit trigger

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now