Post reply

varbinary COLUMNS_UPDATED()

  • February 10, 2004 at 7:58 am

    #84570

    Trying to grab data changed and store it in a DB from a trigger's perpective.

    We have MSAccess forms running against SQL2k backend. Because users can, they do tend to make changes directly to the table data, skipping the forms.  We've tried locking Access down, but they've learned that they can simply create a new DB and relink.

    While this application is being rewritten outside of Access, it will be a year or so before it's complete. 

    Therefore, I've written a trigger that captures events on each table and the user and timedate..., and stores it away.  However, I really need to augment these triggers with the capacity to know which columns were changed.  If I knew that, I could store the before/after values.

    I've found the COLUMNS_UPDATED() method, but can't seem to make good sense of the varbinary field type.  Binary is one thing, but varbinary?! Give me a break. 

    At any rate, I'm looking to see if someone has created a good algorithm to convert this to useful data.  Not looking to buy log explorer for just a years use either.

  • February 11, 2004 at 6:47 am

    #493627

    Couldn't you change the password to the SQL database and make sure that the end user's don't get it?  That way they won't be able to re-link the tables in a new Access database.  You should be able to re-link the tables yourself and have Access save the password, so users don't need to know it when they are using the Access front-end.  It might save having to track each user's every move.

  • February 11, 2004 at 8:08 am

    #493652

    Neat thought, we tried all manner of things, we have Access experts on staff.  Apparently, once you open Access and connect with the protected form, then Access remembers that and new links 'auto-remember' the password.  Thanks for the reply!~

    My intention is to track the users, this should be pretty straightforward; however, it seems no one has taken the time to handle varbinary types extensively.  In fact, BOL indicates, varbinary translations are inconsistent across database versions.  Isnt that wonderful?! 

    This type of audit logging would be valuable to know how to do at any rate...just in case someone out there has done this

     

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

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