MySQL Before Delete触发器可避免删除多行 [英] MySQL Before Delete trigger to avoid deleting multiple rows

问题描述

我试图通过使用BEFORE DELETE触发器来避免一次在MySQL中删除多于一行的内容.

I am trying to avoid deletion of more than 1 row at a time in MySQL by using a BEFORE DELETE trigger.

示例表和触发器如下.

表测试:

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，仍然允许该操作.

This is still allowing multiple rows deletion. Even if I change the IF to >= 1, still allows the operation.

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

I my idea is to avoid operations such as:

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

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

Can you help me? I know that the current version of MySQL doesn't allow FOR EACH STATEMENT triggers.

谢谢！

推荐答案

首先，从您的原始尝试中消除一些语法错误:

Firstly, getting some syntax error(s) out of our way, from your original attempt:

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

• Instead of FOR EACH STATEMENT, it should be FOR EACH ROW.
• Since you have already defined the Delimiter to //; you need to use // (instead of ;) in the DROP TRIGGER IF EXISTS .. statement.
• Row_Count() will have 0 value in a Before Delete Trigger, as no rows have been updated yet. So this approach will not work.

现在，这里的窍门是使用会话级可访问(并且持久)

Now, the trick here is to use Session-level Accessible (and Persistent) user-defined variables. We can define a variable, let's say @rows_being_deleted, and later check whether it is already defined or not.

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

For Each Row runs the same set of statements for every row being deleted. So, we will just check whether the session variable already exists or not. If it does not, we can define it. So basically, for the first row (being deleted), it will get defined, which will persist as long as the session is there.

现在，如果有更多行要删除，则Trigger将为其余行运行同一组语句.在第二行中，现在可以找到先前定义的变量，现在我们可以简单地引发一个异常.

Now if there are more rows to be deleted, Trigger would be running the same set of statements for the remaining rows. In the second row, the previously defined variable would be found now, and we can simply throw an exception now.

注意，在同一会话中可能会触发多个删除语句.因此，在引发异常之前，我们需要将@rows_being_deleted值重新设置为null.

Note that there is a chance that within the same session, multiple delete statements may get triggered. So before throwing exception, we need to set the @rows_being_deleted value back to 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小提琴演示1 :正在尝试删除多于行.

---

DB Fiddle Demo 1: Trying to delete more than row.

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

结果:

查询错误:错误:ER_SIGNAL_EXCEPTION:不能删除多个 每次订购！

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

---

DB Fiddle演示2 :正在尝试只删除一行

---

DB Fiddle Demo 2: Trying to delete only one row

查询#1

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

删除成功发生.我们可以使用Select检查剩余的行.

查询#2

SELECT * FROM `test`;

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

这篇关于MySQL Before Delete触发器可避免删除多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！