Disable DELETE for a table in SQL Server

Question

I'm currently working on creating a table for customers in an order management system for a course at university. The system relies on being able to retrieve order histories for the customers. Since we've chosen not to store these in a separate table, removing the option to delete rows from our customer table is essential.

How and where do I set this up in the CREATE statement? I suspect I'll have to create a rule about what should happen instead, but I'm not entirely sure about the specifics.

Answer 1

On SQL Server, you have the following options:

• Deny object permissions. For example: DENY DELETE ON OBJECT::dbo.Customer TO db_datawriter;
• Raise an error in a trigger: CREATE TRIGGER nodelete1 ON dbo.Customer INSTEAD OF DELETE AS RAISERROR('You can't delete from this table', 16, 10)
• Rely on referential integrity without cascading updates/deletes. Note that this will only prevent deletion of a customer, if the customer has at least 1 order.

In my honest opinion, however, I think that this should be solved at the application level and not the database level. Even if using the techniques above, what would prevent someone from simply removing the trigger or grant the necessary permissions before DELETE'ing the records? Or simply dropping the entire table?

If you don't want your users to delete records from a table, simply make sure that your application does not allow them to do that. Anyone working directly with the database should know that issuing a DELETE statement could be dangerous - especially if you don't have a backup.