Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to avoid row deletion on an specific table using constrains or triggers?

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

like image 888
Pablo Fernandez Avatar asked Dec 14 '08 19:12

Pablo Fernandez


People also ask

Which constraint or object do you use to prevent a DELETE operation?

If you want to prevent intentional deletes then you should rely on security (deny DELETE permission on the table).

Can we DELETE row from parent table without deleting child table?

You just add ON DELETE SET NULL or NO ACTION .

What are some practices that you can deploy to prevent accidental changes to the tables?

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.

When you UPDATE a table what is best practice to do prior to deleting the data?

One thing that you should do before performing mass updates or deletes is to run a select statement using conditions provided.


1 Answers

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.

like image 85
D'Arcy Rittich Avatar answered Nov 06 '22 11:11

D'Arcy Rittich