I'm interested in all kinds of code enhancement, but I split my audit trail project for MySQL in smaller snippets. In today's snippet I want to concentrate on the ugly code I've written: a script writing a script.The generator-snippet:-- write after update trigger.INSERT INTO tmp_sql_install(line) VALUES ('-- Create after update trigger: ');INSERT INTO tmp_sql_install(line)SELECT CONCAT('DROP TRIGGER IF EXISTS `',@myDataSchema,'`.`',@myTable,'_audit_update`;','DELIMITER @@CREATE TRIGGER `',@myDataSchema,'`.`',@myTable,'_audit_update` AFTER UPDATE ON `',@myDataSchema,'`.`',@myTable,'`        FOR EACH ROW BEGINIF @audit_current_sequence_id IS NULL     OR NOT EXISTS(SELECT auseq.audit_seq FROM `',@myAuditSchema,'`.audit_sequence AS auseq JOIN `',@myAuditSchema,'`.audit_transactions autr         ON auseq.a_transaction_id = autr.a_transaction_id WHERE autr.a_closed = ''NO'') THEN    INSERT  INTO  `',@myAuditSchema,'`.audit_sequence (a_transaction_id) VALUES (@audit_current_transaction_id);    SET @audit_current_sequence_id = LAST_INSERT_ID();END IF;INSERT INTO `',@myAuditSchema,'`.audit_updates (audit_seq,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,OLD_VALUE,NEW_VALUE,IS_CHANGED,IS_PRIMARY_KEY) VALUES ',(SELECT GROUP_CONCAT( CONCAT( '(@audit_current_sequence_id, ''',TABLE_SCHEMA,''',''',TABLE_NAME,''',''',COLUMN_NAME,''',OLD.`',COLUMN_NAME,'`,NEW.`',COLUMN_NAME,'`,    IF (OLD.`',COLUMN_NAME,'` <> NEW.`',COLUMN_NAME,'`,''YES'',''NO''),    (SELECT COL_IS_PRIMARY FROM `',@myAuditSchema,'`.audit_columns         WHERE TABLE_SCHEMA = ''',TABLE_SCHEMA,''' AND TABLE_NAME = ''',TABLE_NAME, ''' AND COLUMN_NAME = ''',COLUMN_NAME,'''))') SEPARATOR ',    ') FROM audit_columns    WHERE TABLE_SCHEMA = @myDataSchema AND TABLE_NAME = @myTable AND COL_INTERNAL = 'NO'),';INSERT INTO `',@myAuditSchema,'`.`',@myTable,'_audit` (',    (SELECT GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`') SEPARATOR ',    ') FROM audit_columns WHERE TABLE_SCHEMA = @myDataSchema AND TABLE_NAME = @myTable AND COL_NEW_VAL_UPDATE IS NOT NULL),    ') VALUES (',    (SELECT GROUP_CONCAT(COL_NEW_VAL_UPDATE SEPARATOR ',     ') FROM audit_columns WHERE TABLE_SCHEMA = @myDataSchema AND TABLE_NAME = @myTable    AND COL_NEW_VAL_UPDATE IS NOT NULL),');END @@DELIMITER ;');I need a strategy/edit proposal how to format this script to get a readable form for the generator as well as for the output.-- Create after update trigger: DROP TRIGGER IF EXISTS `audit_testdata`.`testtable_audit_update`;DELIMITER @@CREATE TRIGGER `audit_testdata`.`testtable_audit_update` AFTER UPDATE ON `audit_testdata`.`testtable`        FOR EACH ROW BEGINIF @audit_current_sequence_id IS NULL     OR NOT EXISTS(SELECT auseq.audit_seq FROM `audit`.audit_sequence AS auseq JOIN `audit`.audit_transactions autr         ON auseq.a_transaction_id = autr.a_transaction_id WHERE autr.a_closed = 'NO') THEN    INSERT  INTO  `audit`.audit_sequence (a_transaction_id) VALUES (@audit_current_transaction_id);    SET @audit_current_sequence_id = LAST_INSERT_ID();END IF;INSERT INTO `audit`.audit_updates (audit_seq,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,OLD_VALUE,NEW_VALUE,IS_CHANGED,IS_PRIMARY_KEY) VALUES (@audit_current_sequence_id, 'audit_testdata','testtable','text_id',OLD.`text_id`,NEW.`text_id`,    IF (OLD.`text_id` <> NEW.`text_id`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_id')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_type_id',OLD.`text_type_id`,NEW.`text_type_id`,    IF (OLD.`text_type_id` <> NEW.`text_type_id`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_type_id')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_other_id',OLD.`text_other_id`,NEW.`text_other_id`,    IF (OLD.`text_other_id` <> NEW.`text_other_id`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_other_id')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_lng_id',OLD.`text_lng_id`,NEW.`text_lng_id`,    IF (OLD.`text_lng_id` <> NEW.`text_lng_id`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_lng_id')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_access_id',OLD.`text_access_id`,NEW.`text_access_id`,    IF (OLD.`text_access_id` <> NEW.`text_access_id`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_access_id')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_html',OLD.`text_html`,NEW.`text_html`,    IF (OLD.`text_html` <> NEW.`text_html`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_html')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_plain',OLD.`text_plain`,NEW.`text_plain`,    IF (OLD.`text_plain` <> NEW.`text_plain`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_plain')),    (@audit_current_sequence_id, 'audit_testdata','testtable','text_symbol',OLD.`text_symbol`,NEW.`text_symbol`,    IF (OLD.`text_symbol` <> NEW.`text_symbol`,'YES','NO'),    (SELECT COL_IS_PRIMARY FROM `audit`.audit_columns         WHERE TABLE_SCHEMA = 'audit_testdata' AND TABLE_NAME = 'testtable' AND COLUMN_NAME = 'text_symbol'));INSERT INTO `audit`.`testtable_audit` (`audit_user`,    `audit_event`,    `audit_timestamp`,    `audit_seq`,    `text_id`,    `text_type_id`,    `text_other_id`,    `text_lng_id`,    `text_access_id`,    `text_html`,    `text_plain`,    `text_symbol`) VALUES (USER(),     'UPDATE',     CURRENT_TIMESTAMP,     @audit_current_sequence_id,     NEW.`text_id` ,     NEW.`text_type_id` ,     NEW.`text_other_id` ,     NEW.`text_lng_id` ,     NEW.`text_access_id` ,     NEW.`text_html` ,     NEW.`text_plain` ,     NEW.`text_symbol` );END @@DELIMITER ;In this form it is hard to maintain, always putting generator and output side by side and correct every bug in both, iterating as usual in SQL.
Code beautifying for dynamic coding to create triggers (Audit Trail)
sql;mysql