Delete trigger generator.sql

DROP FUNCTION IF EXISTS `GENERATE_DELETE_TRIGGER`;;
CREATE DEFINER=`vlasta`@`%` FUNCTION `GENERATE_DELETE_TRIGGER`(`src` varchar(128), `dst` varchar(128)) RETURNS text CHARSET utf8
    READS SQL DATA
    COMMENT 'generuje kód pro vytvoření zálohovacího triggeru (AD)'
BEGIN
    DECLARE src_schema, src_table, dst_schema, dst_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`, `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;
    IF LOCATE('.', dst) THEN
        SET dst_schema = SUBSTR(dst, 1, LOCATE('.', dst) - 1);
        SET dst_table  = SUBSTR(dst, LOCATE('.', dst) + 1);
    ELSE
        SET dst_schema = SCHEMA();
        SET dst_table  = dst;
    END IF;
    
    SET output = CONCAT(output, 'DELIMITER ;;', @nl, @nl);
    
    
    SET output = CONCAT(output, 'DROP TRIGGER IF EXISTS `', src_schema, '`.`',  src_table, '_ad`;;', @nl);
    SET output = CONCAT(output, 'CREATE TRIGGER `', src_schema, '`.`',  src_table, '_ad` AFTER DELETE ON `', src_schema, '`.`',  src_table, '` FOR EACH ROW', @nl);
    SET output = CONCAT(output, 'BEGIN', @nl);
    SET output = CONCAT(output, '    INSERT INTO `', dst_schema, '`.`', dst_table, '` SET', @nl);

    OPEN columns;
    
    cols: REPEAT
        FETCH columns INTO col_name, 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;
        
        SET output = CONCAT(output, '        `', col_name, '` = OLD.`', col_name, '`,', @nl);
    UNTIL done END REPEAT;
    
    SET output = CONCAT(output, '        `delete_time` = NOW(),', @nl);
    SET output = CONCAT(output, '        `delete_uid` = getUserId(),', @nl);
    SET output = CONCAT(output, '        `delete_action` = getUserAction();', @nl);
    SET output = CONCAT(output, 'END;;');
    
    RETURN output;
END;;

/* funkce generuje AD trigger, který zálohuje odstraněný řádek do jiné tabulky/databáze
   navíc přidává sloupce delete_... */