Vfrdtyky

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 ;

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

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

Result:

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

DB Fiddle Demo 2: Trying to delete only one row

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   |