Results 1 to 4 of 4
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    Creating a Trigger to log all changes to a specific table

    I have an application in Access 2007 with a SQL Server 2008 R2 backend. I would like to create a trigger in SQL Server that will record all changes that are made to a particular table. When an operator changes a data element in the table, I would like to record the "before" image of the changed row in the table (all columns including the ones that weren't changed) along with date, time, user login id, and, if possible, the form that was used to make the change. I would like another trigger that would remove all rows in the audit table that is, say, over 90 days old.



    Thanks, Eddie

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I don't know about 'trigger' in SQL Server but data edit audit tracking is a common topic. Review https://www.accessforums.net/access/...0-a-30325.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    Short answer to why it needs to be a SQL Trigger and not an Access/VBA function... that's what the IT department has dictated. If I can't make it a Trigger, they won't accept it. I asked them how to write the Trigger and they said they didn't have time to look into it. Makes me ashamed to call myself a programmer/analyst.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A forum with a focus on SQL Server might be more helpful.

    Do these help:
    http://www.sqlteam.com/article/an-in...riggers-part-i
    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    Access 2010 introduced 'Data Macros', which seem to be equivalent to triggers in SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating query to print specific records
    By halt4814 in forum Forms
    Replies: 4
    Last Post: 05-15-2012, 08:23 PM
  2. Replies: 1
    Last Post: 12-18-2011, 01:52 AM
  3. Macro to trigger emailing an updateable table
    By KOIBOY in forum Import/Export Data
    Replies: 5
    Last Post: 09-10-2011, 05:46 AM
  4. date trigger
    By hyperionfall in forum Access
    Replies: 1
    Last Post: 08-08-2010, 01:15 PM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums