Update trigger generator.sql

DROP FUNCTION IF EXISTS `GENERATE_UPDATE_TRIGGERS`;;
CREATE DEFINER=`vlasta`@`%` FUNCTION `GENERATE_UPDATE_TRIGGERS`(`src` varchar(130)) RETURNS text CHARSET utf8
    READS SQL DATA
    COMMENT 'generuje kód pro vytvoření update triggerů (BI, BU)'
BEGIN
    DECLARE src_schema, src_table VARCHAR(64);
    DECLARE output TEXT DEFAULT '';
    
    DECLARE col_name, x_schema, x_table VARCHAR(64);
    DECLARE nullable VARCHAR(3);
    DECLARE done INT DEFAULT 0;
    
    DECLARE columns CURSOR FOR SELECT `COLUMN_NAME`, `IS_NULLABLE`, `TABLE_SCHEMA`, `TABLE_NAME` FROM `information_schema`.`COLUMNS`;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    SET @nl = CHAR(10);
    
    IF LOCATE('.', src) THEN
        SET src_schema = SUBSTR(src, 1, LOCATE('.', src) - 1);
        SET src_table  = SUBSTR(src, LOCATE('.', src) + 1);
    ELSE
        SET src_schema = SCHEMA();
        SET src_table  = src;
    END IF;
    
    SET output = CONCAT(output, 'DELIMITER ;;', @nl, @nl);
    
    
    SET output = CONCAT(output, 'DROP TRIGGER IF EXISTS `', src_schema, '`.`',  src_table, '_bi`;;', @nl);
    SET output = CONCAT(output, 'CREATE TRIGGER `', src_schema, '`.`',  src_table, '_bi` BEFORE INSERT ON `', src_schema, '`.`',  src_table, '` FOR EACH ROW', @nl);
    SET output = CONCAT(output, 'BEGIN', @nl);
    SET output = CONCAT(output, '    SET NEW.update_time = NOW();', @nl);
    SET output = CONCAT(output, '    SET NEW.update_uid = getUserId();', @nl);
    SET output = CONCAT(output, '    SET NEW.update_action = getUserAction();', @nl);
    SET output = CONCAT(output, 'END;;', @nl, @nl);
    
    
    SET output = CONCAT(output, 'DROP TRIGGER IF EXISTS `', src_schema, '`.`',  src_table, '_bu`;;', @nl);
    SET output = CONCAT(output, 'CREATE TRIGGER `', src_schema, '`.`',  src_table, '_bu` BEFORE UPDATE ON `', src_schema, '`.`',  src_table, '` FOR EACH ROW', @nl);
    SET output = CONCAT(output, 'BEGIN', @nl);
    SET output = CONCAT(output, '    SET NEW.`update_time`   = OLD.`update_time`;', @nl);
    SET output = CONCAT(output, '    SET NEW.`update_uid`    = OLD.`update_uid`;', @nl);
    SET output = CONCAT(output, '    SET NEW.`update_action` = OLD.`update_action`;', @nl, @nl);
    SET output = CONCAT(output, '    IF', @nl);

    OPEN columns;
    
    cols: REPEAT
        FETCH columns INTO col_name, nullable, x_schema, x_table;
        IF done THEN LEAVE cols; END IF;
        
        IF x_schema != src_schema THEN ITERATE cols; END IF;
        IF x_table  != src_table  THEN ITERATE cols; END IF;
        IF col_name IN ('ts', 'update_time', 'update_uid', 'update_action') THEN ITERATE cols; END IF;
        
        IF nullable = 'NO' THEN
            SET output = CONCAT(output, '        (NEW.`', col_name, '` != OLD.`', col_name, '`) ||', @nl);
        ELSE
            SET output = CONCAT(output, '        (NEW.`', col_name, '` != OLD.`', col_name, '` || NEW.`', 
                col_name, '` IS NULL AND OLD.`', col_name, '` IS NOT NULL || NEW.`', col_name, '` IS NOT NULL AND OLD.`', col_name, '` IS NULL) ||', @nl);
        END IF;
    UNTIL done END REPEAT;
    
    SET output = CONCAT(SUBSTR(output, 1, LENGTH(output) - 3), @nl, '    THEN', @nl);
    SET output = CONCAT(output, '        SET NEW.update_time = NOW();', @nl);
    SET output = CONCAT(output, '        SET NEW.update_uid = getUserId();', @nl);
    SET output = CONCAT(output, '        SET NEW.update_action = getUserAction();', @nl);
    SET output = CONCAT(output, '    END IF;', @nl);
    SET output = CONCAT(output, 'END;;', @nl, @nl);
    
    SET output = CONCAT(output, 'DELIMITER ;');
    
    RETURN output;
END;;

/* funkce generuje trigger, který v případě vzniku/změny řádku nastaví sloupce update_... 
   přijímá jméno tabulky */