MySQL Before Delete триггер, чтобы избежать удаления нескольких строк

Во-первых, мы избавились от некоторых синтаксических ошибок из вашей исходной попытки:

• Вместо FOR EACH STATEMENT должен быть FOR EACH ROW.
• Поскольку вы уже определили разделитель для //; вам нужно использовать // (вместо ;) в операторе DROP TRIGGER IF EXISTS ...
• Row_Count() будет иметь значение 0 в Before Delete Trigger, поскольку строки еще не обновлены. Так что этот подход не сработает.

Теперь уловка состоит в том, чтобы использовать Доступный (и постоянный) на уровне сеансапользовательские переменные. Мы можем определить переменную, скажем, @rows_being_deleted, и позже проверить, определена ли она уже или нет.

For Each Row запускает один и тот же набор операторов для каждой строки удаляется. Итак, мы просто проверим, существует ли уже переменная сеанса или нет. Если нет, мы можем определить это. Итак, в основном, для первой строки (удаляемой) она будет определена, которая будет сохраняться, пока существует сеанс.

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

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

Следующее будет работать:

DELIMITER //
DROP TRIGGER IF EXISTS prevent_multiple_deletion //
CREATE TRIGGER prevent_multiple_deletion
  BEFORE DELETE ON `test`
  FOR EACH ROW  
    BEGIN

       -- check if the variable is already defined or not
       IF( @rows_being_deleted IS NULL ) THEN 
         SET @rows_being_deleted = 1; -- set its value

       ELSE -- it already exists and we are in next "row"

         -- just for testing to check the row count
         -- SET @rows_being_deleted = @rows_being_deleted + 1;

         -- We have to reset it to null, as within same session
         -- another delete statement may be triggered.
            SET @rows_being_deleted = NULL;

         -- throw exception
         SIGNAL SQLSTATE '45000' 
         SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';
       END IF;

  END //

DELIMITER ;

DB Fiddle Демо 1: Попытка удалить более строки.

DELETE FROM `test` WHERE `id`< 5;

Результат:

Query Error: Error: ER_SIGNAL_EXCEPTION: Cannot delete more than one order per time!

DB Fiddle Демо 2: попытка удалить только одну строку

Запрос №1

DELETE FROM `test` WHERE `id` = 1;

Deletion successfully happened. We can check the remaining rows using Select.

Запрос №2

SELECT * FROM `test`;

| id  | a   | b   |
| --- | --- | --- |
| 2   | 3   | 4   |