Menu
Is there a way to avoid row deletion on an specific table using constrains?
I'd like to (for example) deny row deletion if the id is 0,1 or 2
This is in order to avoid users deleting master accounts for an application, and I'd like to avoid it even if someone tries it (by mistake) using sql directly.
Thanks!
EDIT:
The whole idea of this question is not to touch the application. It's not a matter of security, I just need to know if It's possible to do what I asked with constrains or any other thing that SQL Server has (It does not need to be an standard db solution).
EDIT 2:
Code samples are very, very appreciated :D
888
If you want to prevent intentional deletes then you should rely on security (deny DELETE permission on the table).
You just add ON DELETE SET NULL or NO ACTION .
One of the effective ways that can prevent accidental DML operations on SQL Server Table is to create a trigger on important table that contain crucial data. These triggers are also called as Prevention triggers. We can use triggers to prevent both accidental UPDATE or DELETE operations.
One thing that you should do before performing mass updates or deletes is to run a select statement using conditions provided.
You do this by writing a database trigger that fires on DELETE for the table in question. All it needs to do is throw an exception if the ID is invalid.
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
