Preventing accidental data deletion in SQL Server

After a few hours of working on the proper table definition for your base table, and filling it with data, you sit back, and decide to do some minor maintenance on a few other tables. You truncate a table, and seconds later realise to your horror that you just purged the table you spent quite some time on filling.

Sound familiar?

It recently happened to me, and I decided to put some mechanisms in place to prevent accidental deletion of data. Of course the best way to prevent accidental deletion is by making sure you are always 100% awake, and you simply do not make errors. Unfortunately that is unrealistic, so I have a few tips to help out.


As a very basic and rather obvious tip, I'll advise you to just make a database backup every time you spent a lot of time on filling in basetables. Now for some added technical tricks:

1) To prevent accidental deletion of data, I decided to use INSTEAD OF triggers. BOL again:

INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL triggers.

At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.

INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

The definition of my trigger is similar to this:

CREATE TRIGGER DeleteTable_1 ON Table_1
INSTEAD OF DELETE
AS
BEGIN
IF @@rowcount > 0
BEGIN
RAISERROR( 'Rows in Table_1 cannot be deleted!', 16, 2 )
ROLLBACK
END
END
GO

When I now try to delete rows (using DELETE FROM Table_1), I will see the following errors in my Query result:

Msg 50000, Level 16, State 2, Procedure DeleteTable_1, Line 7
Rows in Table_1 cannot be deleted!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Unfortunately, that does NOT prevent the table from being Truncated.

2) To avoid accidently dropping a table, I create views over them and use the option WITH SCHEMABINDING. From Bol (my emphasis):

SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns."

The first bit I emphasised clearly shows that using views in this way is a neat little feature to prevent accidental drops of a table.

The second quote is IMHO also important, as it shows that clearly the table should be "final". If you later on decide that the table in fact needs extra (or less) columns, the VIEW will actually make your life harder.

As an extra tip, when you create a view to use WITH SCHEMABINDING, always use the fully qualified name of the table (i.e. dbo.Salaries instead of just Salaries). Also, you cannot use *, but instead you will have to define all the columns in the query statement.

Nice, but still no protection against a TRUNCATE.

3) In fact, the only way to prevent TRUNCATE from executing on your table is to ensure there is a Foreign Key relationship to the table. If you are (like me) still working on the tables that will eventually link to the base table, you can always create a dummy table that references your base table:

CREATE TABLE [dbo].[Table_2]
(
[TableID] [int] NULL,
[Table_1ID] [int] NULL
)
ON [PRIMARY]

GO
ALTER TABLE [dbo].[Table_2]
WITH CHECK
ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([Table_1ID])
REFERENCES [dbo].[Table_1] ([TableID])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]

Now when you try to TRUNCATE Table_1, you will receive the following error:

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'TABLE_1' because it is being referenced by a FOREIGN KEY constraint.

Well, that concludes this post. I hope you will find it helpful in preventing you from accidentally destroying some of your hard work. Of course all comments are welcome :)