Capturing Mysterious Truncation/deletion of a table

I have a dts which creates a table which is utilized on my local
intranet. The DTS runs without error and the table is
created/populated/transfered to the appropriate db. Then it appears
that there is an action on this table which truncates it. I have been
unable to determine the culprit. Can I create a trigger that will
capture truncation? I have tried to create a trigger to capture this
information but none that I attempt seem to work on capturing a
truncation or a drop table and re-create.

Any help would be greatly appreciated.

MT.On 14 Feb 2006 11:22:25 -0800, coolnoff@.hotmail.com wrote:

>I have a dts which creates a table which is utilized on my local
>intranet. The DTS runs without error and the table is
>created/populated/transfered to the appropriate db. Then it appears
>that there is an action on this table which truncates it. I have been
>unable to determine the culprit. Can I create a trigger that will
>capture truncation? I have tried to create a trigger to capture this
>information but none that I attempt seem to work on capturing a
>truncation or a drop table and re-create.
>Any help would be greatly appreciated.
>MT.

Hi MT,

In SQL Server 2000, there is no way to monitor either truncation or
dropping of a table using triggers. You'll have to use Profiler to find
out who/what is destorying your data.

--
Hugo Kornelis, SQL Server MVP|||(coolnoff@.hotmail.com) writes:
> I have a dts which creates a table which is utilized on my local
> intranet. The DTS runs without error and the table is
> created/populated/transfered to the appropriate db. Then it appears
> that there is an action on this table which truncates it. I have been
> unable to determine the culprit. Can I create a trigger that will
> capture truncation? I have tried to create a trigger to capture this
> information but none that I attempt seem to work on capturing a
> truncation or a drop table and re-create.

You can add a trigger FOR DELETE on the table, that will capture the
the truncation happens through DELETE.

However, the trigger will not catch if the table is emptied by
TRUNCATE TABLE or DROP TABLE + ALTER TABLE. One way you can handle
this is to add a table that has a foreign key referencing this table.
This will cause TRUNCATE TABLE and DROP TABLE to fail.

Have you examined the possibility that the reason the data disappears
is because there is a transaction that is not committed, so the
population of the table is simply rolled back?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx