Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Why is data is disappearing from my SQL Server 2005 table?

I've been writing asp code for SQL Server web applications for years and have come up with a problem that has me stumped.

In short, data in one of my tables is periodically and arbitrarily being deleted.  Not ALL the data, just SOME of it.  And each time it happens, it is different data.  It's happening once or twice a week at inconsistent intervals as far as I can tell.

The table is quite narrow (only two fields) but also quite critical as it ties two other tables together and is really important for my client's cash flow.

I have checked the code inside and out and there are NO locations within the code from which to delete the data except one administrative module; using this module it would take DAYS (as well as admin clearance) to delete the amount of data that is regularly disappearing from this table, and those in the company with administrative clearance insist that they are not doing the deleting (there is no reason that they should!)

And yet the data continues to be deleted, and I continue having to restore an older versions of the table.

This system has over 80 tables and it's only happening to this one!  It's truly bizarre!

HOW THE HECK COULD THIS BE HAPPENING?  ALL IDEAS ARE WELCOME, THIS ONE HAS REALLY GOTTEN ME!

Thanks

Marc
0
marcgimbrere
Asked:
marcgimbrere
  • 7
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you methods (can be combined):
? create a trigger on the table, to log information when/who is doing the deletes.
? run a sql profiler to trace the deletes on that table

0
 
marcgimbrereAuthor Commented:
how do i set up a trigger on the table to log information when/who is doing the delete?
0
 
vasureddymCommented:
create a log table and the trigger using the below code

once this code is executed, the audit (log) table and trigger are created. then , when ever, some one/thing deletes a record, the trigger inserts a record into the audit table along with the date deleted and who deteted.

NOTE: replace the string 'YOUR_TABLE' in the code with the your table name

Some useful links:
http://forums.devarticles.com/microsoft-sql-server-5/using-triggers-to-create-an-audit-record-3009.html
SELECT TOP 0 * 
INTO LOG_AUDIT_YOUR_TABLE
FROM YOUR_TABLE
GO
 
ALTER TABLE LOG_AUDIT_YOUR_TABLE ADD DeletedOn datetime, DeletedBy varchar(50)
GO
 
CREATE TRIGGER DBO.TRG_AUDIT_YOUR_TABLE
ON YOUR_TABLE
FOR DELETE
AS
--SET IDENTITY_INSERT LOG_AUDIT_YOUR_TABLE ON 
INSERT INTO LOG_AUDIT_YOUR_TABLE
SELECT *, GETDATE(), SYSTEM_USER
FROM deleted
--SET IDENTITY_INSERT LOG_AUDIT_YOUR_TABLE OFF 
GO
 
 
-- Note: use the two SET IDENTITY_INSERT commands when you see the below error
--'An explicit value for the identity column in table 'emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.'
-- (i.e. if your source table is having a identity key)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
marcgimbrereAuthor Commented:
Thanks I am trying this approach top debug but of course it doesn't tell me what the core problem is...I'll get back to you when the mass deletion occurs again and we can look into the reasons why...meanwhile I'm leaving this open if thats OK with you.

Cheers

Marc
0
 
marcgimbrereAuthor Commented:
ok, so the question of the day is, can you set up a trigger to check to see if a single FIELD has changed?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to check that in the trigger itself, see the UPDATE() function:
http://msdn.microsoft.com/en-us/library/ms189799.aspx
<...>
Testing for UPDATE or INSERT Actions to Specific Columns

You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.
<...>
COLUMNS_UPDATED: http://msdn.microsoft.com/en-us/library/ms186329.aspx
UPDATE(): http://msdn.microsoft.com/en-us/library/ms187326.aspx

watch out, though, that when you have a column value of 'ABC', and the update sets the value to 'ABC' (the same value), both UPDATE() and COLUMNS_UPDATED will indicate that the column has been updated !
0
 
marcgimbrereAuthor Commented:
Thanks, using these techniques I found
0
 
marcgimbrereAuthor Commented:
Using these techniques I found the error, thanks
0
 
marcgimbrereAuthor Commented:
OK, this is weird, I found the solution to the problem and deleted the trigger from the triggers section of the database, but now when I delete data from the table, the trigger still runs and throws an error because it cannot find the table to put the trigger information into:

Microsoft OLE DB Provider for SQL Server error '80040e37'

Invalid object name 'LOG_AUDIT_TBL_SPEAKER_KEYWORDS'.

/speaker_keywords.asp, line 19

I can't work out what is going on, there is nothing in the trigger section in SQL 2005 Management Studio - any ideas?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I can't work out what is going on, there is nothing in the trigger section in SQL 2005 Management Studio - any ideas?

the triggers are listed below the table they are attached to.
what you see under "Server objects => Triggers" are the triggers for server-wide ddl triggers.
0
 
marcgimbrereAuthor Commented:
thanks, doh, I still have more to learn, that worked great
marc
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now