Cannot create trigger if-delete-clause-CodePudding

Here is the code I have

CREATE TRIGGER free_tokens AFTER INSERT ON `csrf_token`
IF (SELECT COUNT(`csrf_token`.`id`)) > 5000 THEN
    DELETE FROM `csrf_token` WHERE `csrf_token`.`time` < (UNIX_TIMESTAMP(NOW() - INTERVAL 2 HOUR))
END IF;

which checks if there is more than 5000 entries after an insert, then deletes all entries which are greater than 2 hours old

I am getting you have an error in your mysql syntax near IF (SELECT ... DELETE FROM ...

I am using MariaDB, can someone help me understand where is the error in this simple statement?

CodePudding user response：

There are small things that your trigger definition is missing:

change the MariaDB delimiter type into '//', so that the trigger instructions can be separated by the semicolon instead
FOR EACH ROW, as part of MariaDB trigger syntax
your DELETE statement is missing a semicolon at the end
can't nest queries inside an IF-THEN-ELSE statement
END to delimit the end of the trigger after its definition, as part of MariaDB syntax

A workaround for the nested query in your IF statement can be to define a variable to be updated with the result of a SELECT, to be carried out before the IF statement.

DELIMITER //

CREATE OR REPLACE TRIGGER free_tokens 
AFTER INSERT 
    ON `csrf_token` 
FOR EACH ROW
BEGIN
    DECLARE num_rows INT;
    SELECT 
        COUNT(*) INTO num_rows 
    FROM 
        `csrf_token`;
        
    IF num_rows > 5000 THEN 
        DELETE FROM 
            `csrf_token` 
        WHERE 
            `csrf_token`.`time` < (UNIX_TIMESTAMP(NOW() - INTERVAL 2 HOUR));
    END IF;
END//

DELIMITER ;