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

Question

My database has certain data that is important for the application to work (constants, ...). And I have test data that is generated by testing the site. Since the test data is consumable, they delete it regularly. Unfortunately, two types of data are found in the same table, so I cannot do it delete from T, but I need to do it delete from T where IsDev = 0.

How can I make sure that I do not accidentally delete non-dev data, forgetting to enable the filter? If this happens, I need to restore from a backup that wastes my time. I will need some kind of foreign key, such as behavior that does not delete deletion when a certain condition is met. It would also be useful to ensure that my code does nothing harmful due to an error.

Answer 1

Well, you can use a trigger that throws an exception if any of the entries in the meta table deletedhas 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 guess a little syntax, but you get the point.

Answer 2

I would use a trigger.

Answer 3

, admin

Answer 4

, , , , , .

, : SQL Server

Answer 5

, , INSTEAD OF, WHERE .

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

, delete , .

create procedure ResetT as delete from T where IsDev = 0

Answer 7

You can create an additional IS_TEST column in your tables, rename TABLE_NAME to TABLE_NAME_BAK and create a TABLE_NAME view in TABLE_NAME_BAK so that it only displays the rows where IS_TEST was set. Setting IS_TEST to zero for the data you want to save, and adding DEFAULT 1 to the IS_TEST column should complete the job. This is similar to the procedure required to create soft deletes.