Is there a way to avoid row deletion on an specific table using constrains or triggers?
As far as enforcing this in a constraint, my solution would be to create a dependent table, so referenced rows cannot be deleted.
CREATE TABLE NoKillI ( id INT NOT NULL, FOREIGN KEY (id) REFERENCES Accounts(id) ON DELETE RESTRICT);INSERT INTO NoKillI (id) VALUES (0);INSERT INTO NoKillI (id) VALUES (1);INSERT INTO NoKillI (id) VALUES (2);
Now no one can delete rows in
Accounts with id values 0, 1, or 2, unless they first delete the corresponding rows in
NoKillI. You can restrict deletions against the dependent table using SQL privileges.
If you don't trust your users, add security.
- Add a stored procedure that allows the users to delete the rows they want with, but disallow whichever you want according to your own rules. Then deny delete access on the table, and allow execute access to the sproc
- Add a secondary table with foreign key references, call the table MasterAccounts, or similar, deny update/delete access to this table, and add references to it to the accounts in question, this will prevent anyone from deleting an account as long as there is a reference from this table to it
- Add a trigger, as OrbMan suggests
- Add a view where they can delete rows through, make the view skip all those accounts they are not allowed to delete, deny delete access to master table, and allow delete access to view
Having said that, if your users have access enough to talk to your database through SQL, then you're really just asking for trouble. You should tighten up security, and only allow access to the database through your application and established protocols. Then you have many options to avoid problems like this.