SQL: Create zombies (rows that won't die) in SQL Server by using INSTEAD OF triggers

SQL: Create zombies (rows that won't die) in SQL Server by using INSTEAD OF triggers

I’ve seen a number of interesting discussions lately around how to handle deletes in database tables. One discussion in Stack Overflow had a variety of suggestions.

One easy option in SQL Server 2016 and later is to use temporal tables as they’ll keep the history for you. But there are a number of reasons why they might not be suitable.

Sometimes, you just want to stop rows being deleted, and to set an IsDeleted flag instead. If you don’t want to depend upon the application doing that for you, the easiest way to accomplish that is to use INSTEAD OF triggers.

INSTEAD OF Triggers

“Normal” triggers in SQL Server were actually AFTER triggers. The operation like INSERT, UPDATE, DELETE occurred, and then the trigger fired.

But there are other types of triggers. In SQL Server, I do wish we had BEFORE triggers like other databases do, and I wish we had them for DDL operations (like CREATE, ALTER, DROP) as well as for DML (data manipulation language) statements.

Ever since SQL Server 2000 though, we’ve also had INSTEAD OF triggers. As the name suggests, these run instead of the normal operations. They were really put in place to let us have updatable views that involved more than one table, but they are perfect for the IsDeleted problem.

Implementing IsDeleted

Let’s imagine we have this table:

Let’s add the IsDeleted column with a default of 0 (i.e. not yet deleted):

Now let’s add the trigger:

It’s important to make sure you add SET NOCOUNT ON so that the row counts from the trigger don’t mess up your original query’s row counts.

And let’s see what happens when we delete one or more rows:

You can see that the row is now flagged as deleted instead of actually being deleted, even though we just used a DELETE command. Importantly, notice that the row counts are correct too:

The 1 row was from the DELETE, and the 3 rows were from the SELECT. No other extra messages appeared because we used the SET NOCOUNT ON to suppress them.

Taking it further

This has now met our requirement but we could take it a bit further. To avoid having to add AND IsDeleted = 0 to all our queries, we could do the following:

  • Rename the table to another name
  • Create a view to replace the table’s original name, and the view would automatically exclude the rows where IsDeleted aren’t zero.

2019-07-04