MySQL - trigger - limit update to one row

Beginner to mysql trigger, I do some searches but can't reach my objective, I simply want to restrain an update query on a specific table to one row or cancel it. For multiple raisons I have to do that on the mysql server side.

Here is my trigger :

DELIMITER //
DROP TRIGGER IF EXISTS prevent_multiple_update //
CREATE TRIGGER prevent_multiple_update
  BEFORE UPDATE ON `my_table`
  FOR EACH ROW
    BEGIN
       IF( @rows_being_updated IS NULL ) THEN
         SET @rows_being_updated = 1;
       ELSE
         SET @rows_being_updated = NULL;
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Cannot update more than one line per time';
       END IF;
  END //
DELIMITER ;

It works as expected for the first query, but only for the first one.

If I try :

UPDATE `my_table` SET company=1 WHERE id=1;

It work, query is executed

If I try :

UPDATE `my_table` SET company=1 WHERE id=1 OR id=2;

It work, query is NOT executed

But, if i try :

UPDATE `my_table` SET company=1 WHERE id=1;
UPDATE `my_table` SET company=2 WHERE id=2;

The first one is executed but the second one trig the trigger and is not executed, it's like var @rows_being_updated is not set to null.

Any idea?

Thank you.

Solution

CREATE TRIGGER prevent_multiple_update
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
       IF @update_timestamp = NOW(6) THEN
         SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'Cannot update more than one line per time';
       ELSE 
         SET @update_timestamp = NOW(6);
       END IF;
END

fiddle

How does this works?

The function NOW(6) returns the datetime of the whole UPDATE query execution start (rather than SYSDATE()) with the microsecond accuracy. It is impossible that two consecutive queries starts within a microsecond. So when the query updates only one row then the value returned by this function differs from one which was set during previous UPDATE (or it is NULL if this is the most first UPDATE within the connection). When the query tries to update more than one row then the function returns the same value for each its call within the query, the trigger detects this on the 2nd row updation and breaks the query.