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

Question

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

Maxer

SSCrazy Eights

Points: 8964
More actions
July 7, 2009 at 12:44 pm

#211834

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?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

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

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

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?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

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