CREATE TRIGGER delete_invoice_complex AFTER DELETE ON t_sfi_inter_invoice FOR EACH ROW BEGIN INSERT INTO t_sfi_inter_bus_complex (TABLE_NAME,OPER_TIME, OPERATION_TYPE, OLD_OBJECT, NEW_OBJECT) VALUES ('t_sfi_inter_invoice',NOW(), 'delete', JSON_OBJECT('id', OLD.ID, 'invoice_no', OLD.INVOICE_NO, 'sum', OLD.SUM, 'fact_sum', OLD.FACT_SUM, 'status', OLD.STATUS, 'business_id', OLD.BUSINESS_ID, 'reinvoice', OLD.REINVOICE, 'is_printed', OLD.IS_PRINTED, 'print_user_id', OLD.PRINT_USER_ID, 'print_user_name', OLD.PRINT_USER_NAME, 'print_date', OLD.PRINT_DATE, 'invoice_type', OLD.INVOICE_TYPE, 'invoice_status', OLD.INVOICE_STATUS, 'tomb_certificate', OLD.TOMB_CERTIFICATE),null); END;字段太多怎么优化成通用的
时间: 2024-02-10 10:17:48 浏览: 122
在MySQL中,你可以使用动态SQL来优化这个触发器,将其转换为通用的形式。
动态SQL允许你在运行时生成SQL语句。你可以使用CONCAT()函数将多个字符串连接起来,从而动态生成SQL语句。例如,你可以使用以下语句:
SET @sql = CONCAT('INSERT INTO t_sfi_inter_bus_complex (TABLE_NAME,OPER_TIME, OPERATION_TYPE, OLD_OBJECT, NEW_OBJECT) VALUES (''t_sfi_inter_invoice'',NOW(), ''delete'',', JSON_OBJECT('id', OLD.ID, 'invoice_no', OLD.INVOICE_NO, 'sum', OLD.SUM, 'fact_sum', OLD.FACT_SUM, 'status', OLD.STATUS, 'business_id', OLD.BUSINESS_ID, 'reinvoice', OLD.REINVOICE, 'is_printed', OLD.IS_PRINTED, 'print_user_id', OLD.PRINT_USER_ID, 'print_user_name', OLD.PRINT_USER_NAME, 'print_date', OLD.PRINT_DATE, 'invoice_type', OLD.INVOICE_TYPE, 'invoice_status', OLD.INVOICE_STATUS, 'tomb_certificate', OLD.TOMB_CERTIFICATE),',null)');
然后,你可以使用PREPARE语句来准备动态SQL语句,并使用EXECUTE语句来执行它:
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这样,你就可以通过动态SQL来优化这个触发器,使其更加通用。
阅读全文