CREATE TRIGGER update_invoice_complex AFTER UPDATE 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(), 'update', 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) , JSON_OBJECT('id', NEW.ID, 'invoice_no', NEW.INVOICE_NO, 'sum', NEW.SUM, 'fact_sum', NEW.FACT_SUM, 'status', NEW.STATUS, 'business_id', NEW.BUSINESS_ID, 'reinvoice', NEW.REINVOICE, 'is_printed', NEW.IS_PRINTED, 'print_user_id', NEW.PRINT_USER_ID, 'print_user_name', NEW.PRINT_USER_NAME, 'print_date', NEW.PRINT_DATE, 'invoice_type', NEW.INVOICE_TYPE, 'invoice_status', NEW.INVOICE_STATUS, 'tomb_certificate', NEW.TOMB_CERTIFICATE)); END;优化一下
时间: 2024-02-10 11:17:35 浏览: 28
这段代码可以进行一些简单的优化:
1. 将 JSON_OBJECT 的参数列表提取出来,方便维护和修改,例如:
```
SET @OLD_OBJECT = 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
);
SET @NEW_OBJECT = JSON_OBJECT(
'id', NEW.ID,
'invoice_no', NEW.INVOICE_NO,
'sum', NEW.SUM,
'fact_sum', NEW.FACT_SUM,
'status', NEW.STATUS,
'business_id', NEW.BUSINESS_ID,
'reinvoice', NEW.REINVOICE,
'is_printed', NEW.IS_PRINTED,
'print_user_id', NEW.PRINT_USER_ID,
'print_user_name', NEW.PRINT_USER_NAME,
'print_date', NEW.PRINT_DATE,
'invoice_type', NEW.INVOICE_TYPE,
'invoice_status', NEW.INVOICE_STATUS,
'tomb_certificate', NEW.TOMB_CERTIFICATE
);
```
2. 可以使用 NEW 和 OLD 的列名来引用相应的列,例如:
```
SET @OLD_OBJECT = 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
);
SET @NEW_OBJECT = JSON_OBJECT(
'id', NEW.id,
'invoice_no', NEW.invoice_no,
'sum', NEW.sum,
'fact_sum', NEW.fact_sum,
'status', NEW.status,
'business_id', NEW.business_id,
'reinvoice', NEW.reinvoice,
'is_printed', NEW.is_printed,
'print_user_id', NEW.print_user_id,
'print_user_name', NEW.print_user_name,
'print_date', NEW.print_date,
'invoice_type', NEW.invoice_type,
'invoice_status', NEW.invoice_status,
'tomb_certificate', NEW.tomb_certificate
);
```
3. 可以将 INSERT INTO 的语句拆分成多行,方便阅读和维护,例如:
```
INSERT INTO t_sfi_inter_bus_complex (
TABLE_NAME,
OPER_TIME,
OPERATION_TYPE,
OLD_OBJECT,
NEW_OBJECT
)
VALUES (
't_sfi_inter_invoice',
NOW(),
'update',
@OLD_OBJECT,
@NEW_OBJECT
);
```