0

I'm working with a SQL Server 2008 installation that was maintained for years by another team of programmers.

I'm having a problem that rows of data seem to be mysteriously disappearing from a specific table in my server.

I would like to be able to set up some sort of monitoring system that would tell me when the table is modified, and a summary of the modification.

I think that "triggers" might be what I'm looking for, but I've never used them before. Are triggers what I want to use, and if so, what is a good resource for learning to use them? Is there a better solution?

I think that I should mention that the table I'm referring to is not that frequently updated, so I don't think that adding a little bit of overhead should be a big deal, but I would prefer a solution that I can brush away once the problem is resolved.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vivian River
  • 31,198
  • 62
  • 198
  • 313

4 Answers4

2

A FOR DELETE trigger could help you capture the rows that are being deleted. You could create an audit table (copy of the table that you'd like to monitor) and then add this code to your trigger:

INSERT INTO [Your Audit Table]
SELECT * FROM deleted

I've also seen some "more advanced" scenarios involving FOR XML.

I don't know that the trigger would help determine who is deleting the records, but you might be able to PROVE that the records are being deleted, and perhaps what time, etc. That could help you troubleshoot further.

BlakeH
  • 3,354
  • 2
  • 21
  • 31
2

The following sample should be a basic idea of what you're looking for.

CREATE TABLE MyTestTable(col1 int, col2 varchar(10));
GO

CREATE TABLE MyLogTable(col1 int, col2 varchar(10), ModDate datetime, ModBy varchar(50));
GO

CREATE TRIGGER tr_MyTestTable_IO_UD ON MyTestTable AFTER UPDATE, DELETE
AS
    INSERT MyLogTable
    SELECT col1, col2, GETDATE(), SUSER_SNAME()
    FROM   deleted;
GO

Insert MyTestTable Values (1, 'aaaaa');
Insert MyTestTable Values (2, 'bbbbb');
UPDATE MyTestTable Set col2 = 'bbbcc' WHERE col1 = 2;
DELETE MyTestTable;
GO

SELECT * FROM MyLogTable;
GO

However, keep in mind that there are still ways of deleting records that won't be caught by a trigger. (TRUNCATE TABLE and various bulk update commands.)

DeadZone
  • 1,633
  • 1
  • 17
  • 32
1

Another solution would be to attach Sql Profiler to the database with specific conditions. This will log every query run for your inspection.

I like to stay away from triggers but they could help for your problem like Draghon said

Marc
  • 16,170
  • 20
  • 76
  • 119
0

I think you have it figured out. A trigger is likely your best bet as it's as close to the data as you can get. Inspecting the code (programming or even a stored procedure) would not give you as much an assurance as a trigger would; a Delete trigger in this case.

Check out this article: http://www.go4expert.com/forums/showthread.php?t=15510

Draghon
  • 347
  • 3
  • 9
  • Can a trigger give me information about the SQL statement that triggered it? – Vivian River Oct 05 '12 at 19:20
  • Not by itself; triggers are really meant for injecting code in the middle of DML statements. I found a similar question [here](http://stackoverflow.com/questions/3094735/create-trigger-to-log-sql-that-affected-table/3149717#3149717). The solution called for the use of the system schema's dm_exec_query_stats and dm_exec_sql_text functions. A small query might reveal the last statements that performed the delete operations and you may be able to use this without employing a trigger. A trigger would still come in handy since only a certain amount of queries (days) are stored. – Draghon Oct 05 '12 at 19:49
  • In the article you reference, it explains that triggers can make use of `inserted` and `deleted` temporary tables to allow me to see what data is being inserted or deleted. That's exactly what I need. – Vivian River Oct 05 '12 at 19:51