Protecting SQL Server Data from Accidental Deletion

In nearly every project that I worked on in recent years, the database not only stores the data maintained by the application, but also describes (parts of) the application itself. If you ever had to implement a permission system which grants users to view or edit tables or open forms or execute functions, you already know that.

The resulting problem is that if the application relies on certain key data to be present and correct, accidental modification or deletion of that data usually causes the application to fail.

I try to show how to use triggers to prevent accidental data modification.

Prevent table data deletion

The simplest way to prevent data deletion is to have an INSTEAD OF DELETE trigger which does nothing, or simply raises an error:

CREATE TRIGGER [dbo].[Prevent_Foo_Delete]
    ON [dbo].[Foo]
    INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    RAISERROR('You cannot delete Foo',1,0);
END

If you really need to delete data, use the DISABLE TRIGGER (2005, 2008) and ENABLE TRIGGER (2005, 2008) commands.

Conditional deletion prevention

In this case, deletion should only be allowed under certain conditions. For example, we could allow to only delete single records:

CREATE TRIGGER [dbo].[Prevent_Foo_Multi_Delete]
   ON  [dbo].[Foo]
   INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF (SELECT COUNT(ID) FROM deleted) > 1
        RAISERROR('You can only delete a single Foo',1,0);
    ELSE
        DELETE Foo
        FROM Foo INNER JOIN deleted ON Foo.ID = deleted.ID

Similarly, one could prevent the deletion of detail records to only a single master record by writing

    IF (SELECT COUNT(DISTINCT BAR_ID) FROM deleted) > 1

Preventing Modifications

The same method can be used for UPDATE triggers. It may be, however, easier to define an ON UPDATE trigger to avoid rephrase the UPDATE statement in an INSTEAD OF trigger. In case of failure, we rollback the current transaction:

CREATE TRIGGER [dbo].[Prevent_Foo_Update]
   ON  [dbo].[Foo]
   FOR UPDATE
AS
BEGIN
      SET NOCOUNT ON;

      IF (SELECT COUNT(ID) FROM inserted) > 1 BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('You can only modify 1 Foo',1,0);
      END
END

Preventing Truncation

These mechanisms prevent you from an accidental UPDATE or DELETE on all records (e.g. by a missing WHERE clause, or semicolon in front of the WHERE condition).

However, there is still the TRUNCATE TABLE command which deletes all data in a table and cannot be stopped by a DELETE trigger:

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

The rescue shows in the preceding sentence:

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint

Simply have a table that references the tables to be protected:

CREATE TABLE Prevent_Truncate(
    Foo_ID INT REFERENCES Foo(ID),
    Bar_ID INT REFERENCES Bar(ID)
)

You only appreciate how valuable your data is once it’s lost 😉

Advertisements

One Response to Protecting SQL Server Data from Accidental Deletion

  1. Johnson says:

    Fantastic article, there i found one more very informative article discussed this with more explanation. This may be more helpful to understand this:
    http://www.sqlmvp.org/prevent-accidental-update-or-delete-in-a-database-table/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: