SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Maxer
Maxer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1806 Visits: 1624
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91901 Visits: 38954
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)
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23166 Visits: 5314
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
Maxer
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1806 Visits: 1624
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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23166 Visits: 5314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search