Value of bit field changed on table - trigger to track when/how it changed?

  • So we have a table, it has "stuff" in it.

    One of those columns is a bit and we found a handfull of records that get changed each month that should NOT have been changed.

    I want to rule out the possability of some one connecting using say Access and running a query and not realizing they are making updates.

    I also want to rule out some other random thing... maybe a DEV is connected to prod and doesn't realize it or some sproc that is running, etc...

    Anyway, if I setup a trigger to fire when the column is updated (not a new record created, an existing changing), what can I use to see data about the connection changing it?

    Can I get the application name string, maybe the IP or where the connection was logged in from/as, and obviously a date/time stamp to see the when.

    Thoughts on this?

  • Sounds like an audit table is needed to capture this info for future analysis. What you are looking for you can find in Books Online, the SQL Servr Help System. part of what you need you should be able to find in system functions, like USER_NAME(). There are others as well, so read about them.

  • This allows it to handle multiple updates at one time (set based operations) and will write a log into a table with the login name (SYSTEM_USER), the hostname (which CAN be spoofed), and the database username (USER_NAME) as well as the date.

    Quick and dirty..

    CEWII

    CREATE TRIGGER dbo.triggername ON dbo.tablename FOR UPDATE

    AS

    BEGIN

    INSERT dbo.somelogtable ( LoginName, HostName, DBUserName, LogDT, LogMsg )

    SELECT SYSTEM_USER, HOST_NAME(), USER_NAME, GETDATE(), 'Field Changed'

    FROM inserted i, deleted d

    WHERE i.keyfield = d.samekeyfield

    -- If compare field is not null

    AND i.comparefield != d.samecomparefield

    -- If compare field can be null

    AND ( ( i.comparefield IS NOT NULL AND d.samecomparefield IS NULL ) OR ( i.comparefield IS NULL AND d.samecomparefield IS NOT NULL ) OR ( i.comparefield != d.samecomparefield AND ( i.comparefield IS NOT NULL AND d.samecomparefield IS NOT NULL ) ) )

    END

    GO

  • Gotcha, thanks.

    Anything WRONG with the trigger approach?

    Granted it may slow things down if there are lots of updates, but is that the only real concern?

    The slowdown due to extra work on updates as the trigger has to fire?

  • I would only keep it on their after you discover the problem if you REALLY need it, but performance is really the only potential issue. If you do a lot of single updates I don't think you will even notice, a large set operation, you might.

    CEWII

Viewing 5 posts - 1 through 4 (of 4 total)

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