0

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!

  • UPDATE: I saved the oldest id in a variable and now the procedure itself is working; the trigger is still not working and deletes the newest message – Alphastrick Jun 7 at 9:11
  • 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 – Alphastrick Jun 7 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.