mysql 触发器 before,MySQL Before Delete触发器可避免删除多行

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

The sample table and trigger are as below.

Table test:

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

Trigger:

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 ;

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;

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

Thank you!

解决方案

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

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 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.

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.

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.

Following will work:

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 ;

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

Result:

Query Error: Error: ER_SIGNAL_EXCEPTION: Cannot delete more than one

order per time!

Query #1

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

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

Query #2

SELECT * FROM `test`;

| id | a | b |

| --- | --- | --- |

| 2 | 3 | 4 |