SOLUTION: MariaDB doesn't allow - like Akina told - to edit old data with a trigger or from a trigger called procedure. Possible alternatives are time-based procedures.
I have this few lines testing each insert on
contact_message if there are already more than 3 messages and deletes the oldest. The question is why it doesn't do that. The logic itself should be correct; it was tested and returned the expected result.
Is there a rule for procedures I don't know?
-- This function gets the id and is proved working correctly DELIMITER $$ CREATE FUNCTION get_id_of_oldest_message_by_mail(mail text) RETURNS int BEGIN RETURN ( SELECT cf.id FROM contact_message cf WHERE cf.email = mail ORDER BY cf.date ASC LIMIT 1 ); END$$ DELIMITER ; -- This procedure deletes the oldest message from given mail -- if there are more than 3 messages send DELIMITER $$ CREATE PROCEDURE delete_oldest_message_by_mail(IN mail text) BEGIN DECLARE c int; SET c = (SELECT COUNT(cf.id) FROM contact_message cf WHERE cf.email = mail); IF c > 3 THEN DELETE FROM contact_message WHERE id = get_id_of_oldest_message_by_mail(mail); END IF; END$$ DELIMITER ; -- This trigger calles delete_oldest_message_by_mail (proved working) DELIMITER $$ CREATE TRIGGER delete_old_messages AFTER INSERT ON contact_message FOR EACH ROW BEGIN CALL delete_oldest_message_by_mail(NEW.email); END$$ DELIMITER ;
Thank you for any idea!