Protect row from deletion in MySQL

mysql delete-row
BartoszCichecki picture BartoszCichecki · Dec 31, 2011 · Viewed 8.4k times · Source

I want to protect some rows from deletion and I prefer to do it using triggers rather than logic of my application. I am using MySQL database.

What I came up with is this:

DELIMITER $$

DROP TRIGGER `preserve_permissions`$$

USE `systemzarzadzaniareporterami`$$

CREATE TRIGGER `preserve_permissions`
AFTER DELETE ON `permissions`
FOR EACH ROW
BEGIN
IF old.`userLevel` = 0 AND old.`permissionCode` = 164 THEN
    INSERT INTO `permissions` SET `userLevel`=0, `permissionCode`=164;
END IF;
END$$

DELIMITER ;

But it gives me an error when I use delete:

DELETE FROM `systemzarzadzaniareporterami`.`permissions`
WHERE `userLevel` = 0 AND `permissionCode` = 164;

Error Code: 1442. Can't update table 'permissions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there another way to do such a thing?

Answer

Bill Karwin picture Bill Karwin · Dec 31, 2011

One solution would be to create a child table with a foreign key to your permissions table, and add dependent rows referencing the individual rows for which you want to block deletion.

CREATE TABLE preserve_permissions (
  permission_id INT PRIMARY KEY,
  FOREIGN KEY (permission_id) REFERENCES permissions (permission_id)
);

INSERT INTO perserve_permissions (permission_id) VALUES (1234);

Now you can't delete the row from permissions with id 1234, because it would violate the foreign key dependency.

If you really want to do it with a trigger, instead of re-inserting a row when someone tries to delete it, just abort the delete. MySQL 5.5 has the SIGNAL feature to raise an SQLEXCEPTION in a stored proc or trigger.

If you use MySQL 5.0 or 5.1, you can't use SIGNAL but you can use a trick which is to declare a local INT variable in your trigger and try to assign a string value to it. This is a data type conflict so it throws an error and aborts the operation that spawned the trigger. The extra clever trick is to specify an appropriate error message in the string you try to stuff into the INT, because that string will be reported in the error! :-)

Related questions

How to delete a certain row from mysql table with same column values?

I have a problem with my queries in MySQL. My table has 4 columns and it looks something like this: id_users id_product quantity date 1 2 1 2013 1 2 1 2013 2 2 1 2013 1 3 1 2013 id_users and id_product are foreign keys from different tables. What I want is …

Read More
SQL DELETE with JOIN another table for WHERE condition

I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. DELETE FROM guide_category AS pgc WHERE pgc.id_…

Read More
Deleting records before a certain date

How would I go about deleting all records from a MySQL table from before a certain date, where the date column is in DATETIME format? An example datetime is 2011-09-21 08:21:22.

Read More