MySQL Before Delete 触发器以避免删除多行

Question

我试图通过使用 BEFORE DELETE 触发器来避免在 MySQL 中一次删除超过 1 行。

示例表和触发器如下。

表格测试:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) NOT NULL,
 `b` int(11) NOT NULL,
 PRIMARY KEY (`id`));


INSERT INTO `test` (`id`, `a`, `b`)
VALUES (1, 1, 2);

INSERT INTO `test` (`id`, `a`, `b`)
VALUES (2, 3, 4);

触发器:

DELIMITER //
DROP TRIGGER IF EXISTS prevent_multiple_deletion;
CREATE TRIGGER prevent_multiple_deletion
BEFORE DELETE ON test
FOR EACH STATEMENT 
BEGIN

  IF(ROW_COUNT()>=2) THEN  
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';
  END IF;

END //

DELIMITER ;

这仍然允许删除多行。即使我将 IF 更改为 >= 1，仍然允许操作。

我的想法是避免以下操作:

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

你能帮帮我吗？我知道当前版本的 MySQL 不允许 FOR EACH STATEMENT 触发器。

谢谢!

Answer 1

首先，从您最初的尝试中排除一些语法错误:

• 而不是FOR EACH STATEMENT，它应该是FOR EACH ROW。
• 因为您已经将分隔符定义为//；您需要在 DROP TRIGGER IF EXISTS .. 语句中使用 //(而不是 ;)。
• Row_Count() Before Delete Trigger 中的值为 0，因为尚未更新任何行。所以这种方法行不通。

现在，这里的技巧是使用 session 级可访问(和持久) user-defined variables .我们可以定义一个变量，比如说 @rows_being_deleted，然后检查它是否已经定义。

For Each Row 对被删除的每一行运行相同的语句集。因此，我们将只检查 session 变量是否已经存在。如果没有，我们可以定义它。所以基本上，对于第一行(被删除)，它将被定义，只要 session 存在，它就会持续存在。

现在，如果要删除更多行，Trigger 将为剩余的行运行相同的语句集。在第二行，现在可以找到之前定义的变量，我们现在可以简单地抛出一个异常。

请注意，在同一个 session 中，有可能会触发多个删除语句。所以在抛出异常之前，我们需要将 @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 Demo 1 :尝试删除多行。

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

结果:

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

---

DB Fiddle Demo 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   |