Posted by Erland Sommarskog on 02/15/06 00:42

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]