ansaurus

Question

How can I find out when a Sql Database field gets modified?

Answer 1

+4 A:

Yes, an "AFTER UPDATE" trigger on that particular table and field might give you some clues as to when and why the field gets changed.

From Books Online:

CREATE TRIGGER reminder
ON Person.Address
AFTER UPDATE 
AS 
  IF ( UPDATE (StateProvinceID) OR UPDATE (PostalCode) )
  BEGIN
    RAISERROR (50009, 16, 10)
  END;
GO

A trigger can execute basically any T-SQL code - if you have database mail set up correctly, it could send you an e-mail, yes. Or it can write an audit entry into another table or something like that.

EDIT: If you need to find out which statements updated your column, you might be actually better off running a trace on the server, limited to that specific table, and just trace what's happening there. I don't think a trigger can give you that information (which code caused the update to happen).

Marc

marc_s 2009-05-27 04:52:46

If i need to use a trigger, how can i determine what sql code was trying to modify that field?

Pure.Krome 2009-05-27 04:57:00

The trigger code basically runs within the context of the caller - e.g. you should be able to check for things like CURRENT_USER and such. But I really don't know whether you have a way to find out what piece of T-SQL code updated the table ....

marc_s 2009-05-27 05:00:31

This should be fine - RAISERROR, and then ROLLBACK. The ROLLBACK or RAISERROR should "break" the ninja code, which will force it to reveal itself. :D

Aaron Alton 2009-05-27 05:52:03

It is maybe not 'nice' to break anything that update a certain field, at least if it is a prod app. SQL Profiler or trigger-based logging is a more 'friendly' approach...

KristoferA - Huagati.com 2009-05-27 05:54:43

Instead of the raiserror, try looking up session, user, and query details using sys.sysprocesses etc. E.g. this gives current session, user, and currently running SQL:select @@spid, sp.loginame, st.textfrom sys.sysprocesses as spcross apply sys.dm_exec_sql_text(sp.[sql_handle]) as stwhere spid=@@spid

KristoferA - Huagati.com 2009-05-27 05:57:08

@KristoferA : this is a sample for a trigger which I copied 1:1 from the SQL Books Online. Just as a sample how to declare it. The RAISERROR is just one of many things you could do - it will stop the "ninja" code from executing, this helping locate it :-)

marc_s 2009-05-27 08:13:10

@marc_s I know, that's why I added the suggestion above (using sysprocesses to look up stuff etc) as a comment to your reply, and upvoted your reply. :)

KristoferA - Huagati.com 2009-05-27 11:35:05

Answer 2

A:

Determining the last Update to or Select against a table (without a trigger!)

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/03/determining-the-last-update-to-or-select-against-a-table-without-a-trigger.aspx

Robert Harvey 2009-05-27 04:53:11

That's pretty kewl, but that lists the very last update to or select. My question does not pertain to the very last entry, but when a field was changed ... not necessarily the very last action against that table.

Pure.Krome 2009-05-27 04:56:24

Answer 3

A:

Yes, you can use a trigger to execute some code (keep track of who updated the table, email you, etc.) when a table is updated. See this link: Track Updates with a Database Trigger

edit: originally posted the wrong link

Swingley 2009-05-27 04:55:05

.. but how i can i figure out what sql code tried to update the table?

Pure.Krome 2009-05-27 04:59:33

I don't know of a way to use a trigger to get the statement that updated the table. Profiler will log the statement for you but you need to have it running before the statement is issued. If you don't know which user or session to track, this will probably end up generating a huge trace that you'll then have to search through.

Swingley 2009-05-27 05:07:12

ansaurus

tags:

views:

answers:

How can I find out when a Sql Database field gets modified?

related questions