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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Value of bit field changed on table - trigger...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Maxer

Posted Tuesday, July 07, 2009 12:44 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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

Lynn Pettis

Posted Tuesday, July 07, 2009 12:53 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

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.

Cool

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

Elliott Whitlow

Posted Tuesday, July 07, 2009 1:02 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Maxer

Posted Tuesday, July 07, 2009 1:06 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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

Elliott Whitlow

Posted Tuesday, July 07, 2009 1:19 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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 »

Permissions