Preventing the deletion of a specific row in a database

Question

I have a website that is linked to a database. When a user is logged in, they have the ability to delete something called a category. The website creates a prepared statement and removes this category from the database.

I want to be able to prevent the deletion of categories with a specific name or id. This is simple enough to do a check using jquery, but I want to add another layer of security by adding a check within the database. Couple questions...

Trigger or procedure? I have never used procedures before, and from what little trigger experience I have with triggers, I don't know how to go about the issue. Assuming that triggers can be used, how would I get the category being deleted? And then how would I go about stopping that row in the database from being deleted?

As a start, I have the following code for a trigger.

delimiter $$
    CREATE TRIGGER category_delete BEFORE DELETE ON categories
        FOR EACH ROW
            BEGIN

            END$$
delimiter ;

score 6 · Accepted Answer · answered Jan 24 '14 at 23:04

Throw an exception from within the trigger to abort the deletion:

delimiter $$
CREATE TRIGGER category_delete BEFORE DELETE ON categories
FOR EACH ROW
BEGIN
    IF old.id = 5 THEN -- use whatever condition you need
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'May not delete id 5';
    END IF;
END$$
delimiter ;

score 0 · Answer 2 · answered Jan 24 '14 at 22:56

0

Just add a trigger and bound it.

Use the power of the database to maintain the business logic and keep it consistent and what is required - despite whatever PHP, JQuery etc. is throwing at it. It is the primary business assert

answered Jan 24 '14 at 22:56

Ed Heal

59,252
17
87
127

3

This is right on the border of a non answer, probably not an answer. What trigger? Exactly? Where's the code? This is basically a comment. – Bohemian Jan 24 '14 at 22:59
@Bohemian - That is the best answer I could come up with the details in the question. Do you know what is the type of category and the one to avoid. Also it was a bit of a comment that databases are one of the most important and costly assert of companies. – Ed Heal Jan 24 '14 at 23:13
I used to think that too. I even wrote a production system using stored procedures. Now I know better. The *data* is important. The *database* is almost irrelevant. Read [this](https://stackoverflow.com/a/6369030/256196) and extend that to all database "features", except those supporting [ACID](http://en.wikipedia.org/wiki/ACID). The real asset is not even the app code, it's the behioural tests. Everything else is just an implementation choice. – Bohemian Jan 25 '14 at 01:18
@Bohemian your first link is dead – reformed Mar 27 '18 at 14:30
@reformed [link fixed](https://stackoverflow.com/a/6369030/256196) (the link was to an question that was later closed, but I found another to take its place) – Bohemian Mar 27 '18 at 14:48

Preventing the deletion of a specific row in a database

2 Answers2