rickquan rickquan - 1 month ago 9
MySQL Question

mysql using max in trigger

I want an update trigger, that issues an error when trying to update any row > (max(IDBCRS_Testung)-100). The trigger itself works fine aslong as I state a number, e.g.

CREATE DEFINER=`root`@`%` TRIGGER `TEST`.`BCRS_BEFORE_UPDATE` BEFORE UPDATE ON `BCRS_Test` FOR EACH ROW
BEGIN
IF New.IDBCRS_Testung > (IDBCRS_Testung)-100)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Update of record not allowed!';
END IF;
END


But of course, this is not practical as the ID number is growing, thus I tried:

CREATE DEFINER=`root`@`%` TRIGGER `TEST`.`BCRS_BEFORE_UPDATE` BEFORE UPDATE ON `BCRS_Test` FOR EACH ROW
BEGIN
IF New.IDBCRS_Testung > (Max(IDBCRS_Testung)-100)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Update of record not allowed!';
END IF;
END


But then I get the following error:

ERROR 1111: 1111: Invalid use of group function

Using latest MySQL and workbench on Linux Mint 17.3

TIA

Answer

If you want to obtain any value from a table, you need to issue a proper select statement to obtain it.

So, in the trigger's body (after the begin) declare a variable (make sure you use the correct data type):

declare max_testung int;

Then assign the max value to it:

SET max_testung = (SELECT max(IDBCRS_Testung) FROM BCRS_Test);

Then do the comparison using the variable:

IF New.IDBCRS_Testung > (max_testung-100)