5

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.

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
user2821342
  • 73
  • 1
  • 1
  • 3
  • 3
    What RDBMS are you using? SQL Server? MySQL? Oracle? – Dan May 19 '14 at 10:03
  • 1
    Google for ["trigger instead" + your RDBMS name](https://www.google.at/search?q=trigger+instead) – Ruslan Bes May 19 '14 at 10:05
  • What do you prevent from getting deleted? Customers for which orders exist? This would be done with a simple foreign key. Or orders? Then you would have to write a trigger which makes any delete statement fail. – Thorsten Kettner May 19 '14 at 10:09
  • 1
    What ***do*** you want to happen instead? Do you just want to prevent all DELETEs, and raise an error if attempted? Or do you want an attempted DELETE to cause something else to happen? – MatBailie May 19 '14 at 10:09
  • We're using SQL server. For the simplicity of it all, I would just want it to make an error to make sure that it cant happen. With the relationship we've defined in our ERD, there isnt currently access to a foreign key. All data surrounding orders are stored in seperate table. – user2821342 May 19 '14 at 10:30

3 Answers3

6

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.

rory.ap
  • 34,009
  • 10
  • 83
  • 174
Dan
  • 10,480
  • 23
  • 49
  • 4
    It is a common feature to have constraints. insert, update, and delete can be constrained in most modern databases. Similar to unique columns constraints. Reason: You might not own the application that is breaking things. – TamusJRoyce Dec 15 '17 at 16:27
  • 1
    I don't understand the down votes as this is what the OP was looking for. – Mark Kram Mar 27 '19 at 20:06
1

If the table is accessed only through an application, you can use a soft delete, to do that add a column to the table, for example IsDeleted, and check it in the software to see whether the row is live or deleted.

If the final users can access the DB, you can to change his/her login or group permission to remove the delete grant on that table.

Serpiton
  • 3,676
  • 3
  • 24
  • 35
0

i do it with a simple trigger on each table that i want disable delete

Create Trigger [dbo].[RollBackDelete]
     ON [dbo].[Your Table Name]
INSTEAD OF DELETE 
AS
BEGIN
ROLLBACK;
END

ofcourse if you have any key with cascade operation on delete or update it can not be work and you should set it to "No Action"

i hope this be be useful

hossein andarkhora
  • 740
  • 10
  • 23