DELIMITER //
CREATE OR REPLACE PROCEDURE audit_trail () AS
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_name CHAR(255);
DECLARE column_name CHAR(255);
DECLARE column_type CHAR(255);
DECLARE column_is_nullable CHAR(3);
DECLARE column_default_value TEXT;
DECLARE cur CURSOR FOR
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name NOT LIKE 'audit_trail%' AND table_name != 'schema_version';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO table_name, column_name, column_type, column_is_nullable, column_default_value;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SET @audit_sql = CONCAT('INSERT INTO audit_trail(table_name, column_name, column_type, column_is_nullable, column_default_value, action) SELECT "',
table_name, '", "', column_name, '", "', column_type, '", "', column_is_nullable, '", "', column_default_value, '", "ALTERED" FROM ',
table_name, ' WHERE ', column_name, ' IS NOT NULL AND NOT EXISTS (SELECT 1 FROM audit_trail WHERE table_name = "', table_name,
'" AND column_name = "', column_name, '" AND column_default_value = "', column_default_value, '" AND action = "ALTERED")');
PREPARE stmt FROM @audit_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
Can I know whether the code works or not I have have tested but getting some error unable to find out. Help with resolution