Защитить строку от удаления в MySQL

Я хочу защитить некоторые строки от удаления, и я предпочитаю делать это с использованием триггеров, а не логики моего приложения. Я использую базу данных MySQL.

Я придумал следующее:

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 ;

Но это приводит к ошибке при использовании 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.

Есть ли другой способ сделать такую вещь?

Ответ 1

Одним из решений было бы создать дочернюю таблицу с внешним ключом в вашей таблице разрешений и добавить зависимые строки, ссылающиеся на отдельные строки, для которых вы хотите заблокировать удаление.

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);

Теперь вы не можете удалить строку из разрешений с id 1234, потому что это будет нарушать зависимость внешнего ключа.

Если вы действительно хотите сделать это с помощью триггера, вместо повторной вставки строки, когда кто-то пытается ее удалить, просто прервите удаление. MySQL 5.5 имеет функцию SIGNAL, чтобы поднять SQLEXCEPTION в хранимом процессе или триггере.

Если вы используете MySQL 5.0 или 5.1, вы не можете использовать SIGNAL, но можете использовать трюк, который должен объявить локальную переменную INT в триггере и попытаться присвоить ей значение строки. Это конфликт типа данных, поэтому он выдает ошибку и прерывает операцию, которая породила триггер. Дополнительный умный трюк заключается в том, чтобы указать соответствующее сообщение об ошибке в строке, которую вы пытаетесь ввести в INT, потому что эта строка будет сообщена в ошибке!: -)

Ответ 2

Создайте новую таблицу и создайте внешний ключ. Задайте внешний ключ для удаления - ограничьте.

CONSTRAINT FOREIGN KEY fk_restriction (restriction_col)
table_to_restrict (restricted_id) ON DELETE RESTRICT ON UPDATE CASCADE

Примечание: Это невозможно сделать с помощью MyISAM, BlackHole и т.д. Используйте с InnoDB и потоковыми двигателями.