5

I know it may look like a weird question...

I have a 3rd party db and services, and I want to monitor it by DML triggers on INSERT, UPDATE and DELETE of each table.

But alas, I have a problem when TRUNCATE TABLE is called, since it's not activating the DELETE triggers.

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows
TRUNCATE TABLE X; --truncate the empty table

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

I'll be happy if there are any other proposals too.

Is something like this possible?

EDIT

I don't want to disable the truncate I just want to monitor the data.

It is 3rd party DB, and it been used by services that I can't have control on, so I can't prevent the truncate.

I'm just trying to monitor changes and currently can't track the deletion of records caused by truncate command.

Thanks!

  • @DanGuzman I initially thought that too, but I don't see any event related to TRUNCATE TABLE in the list of DDL events documented for DDL triggers. – Geoff Patterson Jul 5 '18 at 14:51
5

I'm looking for a way to "overload" the TRUNCATE TABLE to be something like:

DELETE FROM X; --remove all rows TRUNCATE TABLE X; --truncate the empty table

When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.

Or maybe activate some DDL trigger before truncate, so I can check which rows are gonna be truncated.

You do not need a trigger for this. A TRUNCATE will remove all rows in the table, you know all rows are going to be removed in the process. No survivors.

If you would like to capture the event to know who truncated what or when, you can do this with SQL Audit, as shown by Mohd Sufian on SQL Ship.

I don't believe you can fire trigger from a Truncate, here is the DDL List on MSDN of what DDL events can be fired from a trigger.

You can also use extended events to capture Truncate events as Jonathan Kehayias shows on MSDN.

If you want to prevent users from running Truncate, you can create a view or another object with SCHEMABINDING. This prevents DDL statements from altering the table in question, which includes Truncate.

One last note about Truncate is that if you have an identity column, it will reseed the value. I have seen people use the RESEED function and Truncate together. They will capture the last identity used, store it into a variable table, truncate the table removing all records with minimal logging and resetting the identity column, then reseed the last incremental. Usually if you truncate though, you don't care about the reseed. (Not always true, but usually.)

I truncate primarily on staging tables or when I need to reset an instance for testing. I rarely truncate tables otherwise because I care about the referential data integrity and which rows are removed from the tables. When I care about neither, I truncate.

I would highly recommend to read more on both Delete and Truncate, they will achieve similar results but are two very different things.

Atul Gaikwad on MSSQLTips wrote a nice quick article on some of those differences.

Now let's look at this question:

Can I count the rows that will be affected by the truncate from a third party application?

Ideally, the truncate event is scheduled or it can be planned for. Even more ideally, you will be able to alter the code or add something in so it can log information for you. From your post, I'm going to assume you can't plan, schedule, or alter the code.

So how do you track what rows will be affected by truncate if you had to, with absolute certainty?

I personally would create two tables, a trigger, and a procedure.

The trigger would be based on Insert or delete tasks and it would insert into a table the number of rows affected, the table name, and the datetime. So if your process inserted 6 rows, the trigger would insert the value 6 indicating 6 new records flowed in. If you deleted 10 rows, it would insert a value of -10 indicating 10 records are now gone.

I would want to create an extra column for notes or events. I would want the audit or extended events to log to this table the day / time the truncate occurred. They won't be able to tell you how many rows, but it can tell you when and who. That's what's really important at this moment.

The second table would be an aggregate of the first table as I assume you may have many transactions in a day. This would tell you the net total of records at the end of the day and also tell you when truncates occurred. This means you would know how many records were in the table just prior to truncation, meeting your requirements.

The procedure would do the aggregation between tables and it would also perform cleanup work on the first table.

  • Thanks for your answer. As I wrote, I don't want to disable the truncate I just want to monitor data. It is 3rd party DB, and it been used by services that I can't have control on, so I can't prevent the truncate. I'm just trying to monitor changes and currently can't track the deletion of records caused by truncate command. – SHR Jul 5 '18 at 15:34
  • @SHR Using SQL Audit or EXTENDED EVENTS gives you a very lightweight approach to monitoring who is truncating what and when. When a table is truncated and the transaction commits, you KNOW the data from that table is gone, all of it. You cannot remove individual or partial rows with a truncate (let's not talk about partitioning right now), it's an all or nothing. The only thing left to know is who did that action and when they did that action. If you need to know how many rows will be affected, you will need to log a rowcount of the table prior to the truncate occurring. – Shaulinator Jul 5 '18 at 15:44
  • If you have no control over the third party app and when it will truncate, I would recommend a procedure that runs periodically that looks at the system tables or runs a select count(*) and records the name of the table, number of records, and datetime of the count. If you NEED to know EXACTLY how many rows will be affected by the truncate at any given time, then your next best bet is to have this same mechanism as an INSERT, DELETE, UPDATE trigger. Anytime a new record enters, the table would then log to another table what the current rowcount of the table is. I'd rather schedule a proc tho. – Shaulinator Jul 5 '18 at 15:48
  • Means for each insert I can check if it is the first row, or check the seed, or something like this, and if so I guess it was previously truncated. maybe it possible to keep a replica of the table which be apdated by the triggers. Thanks. – SHR Jul 5 '18 at 15:52
  • 2
    @Joshua TRUNCATE is a DDL statement that will not fire triggers. The idea of using a trigger for inserts and deletes is to log to a table when rows would be inserted or removed, effectively keeping track of how many records exist in the table. Typically this is not a problem, but OP has a particular case with a black boxed 3rd party app with limited options. Extended Events or audit would allow you to know when a truncate occurs. Comparing the truncate event to the table that logged records inserted or deleted would effectively tell you the quantity of rows affected by the truncate. – Shaulinator Jul 5 '18 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.