How to make sure a row cannot be accidentally deleted in SQL Server?

Question

In my database I have certain data that is important to the functioning of the app (constants, ...). And I have test data that is being generated by testing the site. As the test data is expendable it delete it regularly. Unfortunately the two types of data occur in the same table so I cannot do a delete from T but I have to do a delete from T where IsDev = 0.

How can I make sure that I do not accidentally delete the non-dev data by forgetting to put the filter in? If that happens I have to restore from a production backup which is wasting my time. I would require some sort of foreign key like behavior that fails a delete when a certain condition is met. This would also be useful to ensure that my code does not do anything harmful due to a bug.

Answer 1

keep a backup of the rows you want to retain in a separate admin table

Answer 2

I would use a trigger.

Answer 3

Well, you could use a trigger that throws an exception if any of the records in the deleted meta-table have IsDev = 1.

CREATE TRIGGER TR_DEL_protect_constants ON MyTable FOR DELETE AS
BEGIN
    IF EXISTS(SELECT 1 FROM deleted WHERE IsDev <> 0)
    BEGIN
        ROLLBACK
        RAISERROR('Can''t delete constants', 1, 16)
        RETURN
    END
END

I'm guessing a bit on the syntax, but you get the idea.

Answer 4

Seems like you need a trigger on delete operation that would look at the row and rollback transaction if it sees that it's a row that should never be deleted.

Also, you might want to read this article: Prevent accidental update or delete commands of all rows in a SQL Server table

Answer 5

Depending on how transparent you want to make this, you could use an INSTEAD OF trigger that will always remember the WHERE for you.

CREATE TRIGGER TR_IODEL_DevOnly ON YourTable
INSTEAD OF DELETE
AS
BEGIN
    DELETE FROM t
        FROM Deleted d
            INNER JOIN YourTable t
                ON d.PrimaryKey = t.PrimaryKey
        WHERE t.IsDev = 0
END

Answer 6

I suggest that instead of writing the delete statement from scratch every time, just create a stored procedure to do the deletions and execute that.

create procedure ResetT as delete from T where IsDev = 0

Answer 7

You could create an extra column IS_TEST in your tables, rename the TABLE_NAME to TABLE_NAME_BAK, and create a view TABLE_NAME on the TABLE_NAME_BAK so that only rows where IS_TEST was set are displayed in it. Setting IS_TEST to zero for the data you wish to keep, and adding a DEFAULT 1 to the IS_TEST column should complete the job. It is similar to the procedure required for creating 'soft deletes'.