Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Value of bit field changed on table - trigger to track when/how it changed? Expand / Collapse
Author
Message
Posted Tuesday, July 7, 2009 12:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:53 AM
Points: 284, Visits: 1,129
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?
Post #748798
Posted Tuesday, July 7, 2009 12:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #748801
Posted Tuesday, July 7, 2009 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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

Post #748807
Posted Tuesday, July 7, 2009 1:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:53 AM
Points: 284, Visits: 1,129
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?
Post #748810
Posted Tuesday, July 7, 2009 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #748815
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse