Post reply

Determine User for Update Audit

  • August 14, 2004 at 6:50 pm

    #102157

    Greetings,

    I've created a trigger on a table that monitors updates to certain columns in the table.  Specifically, a table named "tblProject" has 3 "starting price" fields: start_cost, start_sell, and start_list.  The trigger works quite well, creating 2 records in a table named "tblProjectTblAudit".  Each update to a record in the Project table creates a "before" record and an "after" record to document the values before and after a user modifies the record.

    However, I've got one missing piece: I want to be able to add the user's name (or login name), to the Audit table so I know who made the change.  Does anyone out there know how to determine which user made the change? 

    Some specifics on our system:

    • SQL Server 2000 running on Windows Server 2003
    • Front end of the database is Microsoft Access XP.  Users do not log in to the database, their Windows login provides login rights.
    • I'm not concerned about the creation of a new record, just updates to an existing record.  The values I'm monitoring are generally not supposed to be changed, but there are rare instances where they must be changed.
    • We took over a competitor late last year, and basically starting on Jan. 1 of this year (2004), we upsized our database system from MS Access, to being an Access front end/SQL Server back end, and we added an entire company of new users.  The new company is on the west coast, we're on the east.  The west coast users access the system via a Citrix server (basically Windows Terminal Services).  Users in our east coast office access the system over the local lan, not via an application server.

    Unfortunately, I don't have the code in front of me, but it uses 2 INSERT and SELECT statements to gather information from the logical DELETED and INSERTED tables to form the records for the Audit table.

    Any help would be greatly appreciated!

    Regards,

    Paul J. Sweeney

  • August 14, 2004 at 7:07 pm

    #519005

    suser_sname() in the trigger will return the Windows login. Insert that into the field you want to keep it in.

  • August 16, 2004 at 8:10 pm

    #519236

    Steve:

    Thanks very, very much!  I'll give this a shot.  I'm fairly new to SQL Server so I'm going through a real learning process, but it's great.  I'm trying to learn as much as possible about this database. 

    Regards,

    Paul J. Sweeney

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply