lundi 13 novembre 2017

mysql trigger if/else insert

Well, this is pretty embarrasing, but can't figure it out: Let my trigger be:

DROP TRIGGER IF EXISTS TBAC_101_NUMBER_OF_BACKUPS;
DELIMITER $$
CREATE TRIGGER TBAC_101_NUMBER_OF_BACKUPS AFTER INSERT ON TBAC_101_BACKUP_INSTANCES
FOR EACH ROW
BEGIN
    DECLARE current_number_of_instances     INT;
    DECLARE max_allowed_backup_instances    INT;
    SET current_number_of_instances     =   (SELECT COUNT(*)            FROM TBAC_101_BACKUP_INSTANCES      WHERE element_description=NEW.element_description);
    SET max_allowed_backup_instances    =   (SELECT max_allowed_backups FROM TBAC_100_ELEMENTS_TO_BACKUP    WHERE element_description=NEW.element_description);
    IF(current_number_of_instances > max_allowed_backup_instances) THEN
        INSERT INTO aux(element_description,backup_datetime,conteo,maximo,debugazo) VALUES (NEW.element_description,NEW.backup_datetime,current_number_of_instances,max_allowed_backup_instances,'OK');
    ELSE
        INSERT INTO aux(element_description,backup_datetime,conteo,maximo,debugazo) VALUES (NEW.element_description,NEW.backup_datetime,current_number_of_instances,max_allowed_backup_instances,'KO!!!!');
    END IF;
END $$
DELIMITER ;

So, how is it possible, after performing a debug insert in TBAC_101_NUMBER_OF_BACKUPS, to have a row in table aux such as 'Backups dashboard' 2017-11-13 23:08:05 1 3 KO!!!! It is, it seems that the IF(x > y) wasn't working. Indeed, I tried replacing > with = (which actually also achieves the logic I need) and it isn't working either. Any ideas? Thanks!

Aucun commentaire:

Enregistrer un commentaire