Solved

Need help with audit trigger

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

914 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

16 Experts available now in Live!

Get 1:1 Help Now