DELIMITER //
CREATE OR REPLACE PROCEDURE sys_m_update_trans(
  IN p_para_tablename VARCHAR(100)
)
BEGIN
  /*
    function   : 配合 orm_api 的存檔後過帳, 當新增(或修改)一筆 data 後, update master 的某1-2 個同名欄位的值
    description: 目前先配合 omi_api 的存檔後機制(sys_transaction) 執行, 希望能全部透過設定, 而不是每一個 table 都必須客製
                copy from sys_transfer_comm
                目前針對某 table 存檔後, update 另一個 table 的情況寫入
    build date : 2022/06/04
    
    modify history
      item who        date       modify docs
      ==== ========== ========== ================================================
      1    michael    2022-6-4   測試
	  2    Michael    2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
  */
  DECLARE v_RelationKey VARCHAR(100);        -- RelationKey
  DECLARE v_MiddleField VARCHAR(100);        -- MiddleField
  DECLARE v_dest_table VARCHAR(100);         -- dest_table
  DECLARE v_dest_field VARCHAR(100);         -- dest_field
  DECLARE v_dest_field2 VARCHAR(100);        -- dest_field2(特殊使用, 如sys_rolemenusecurity.controlname=read)
  DECLARE v_dest_field2_value VARCHAR(100);  -- dest_field2_value(特殊使用, 如sys_rolemenusecurity.controlname=read)
  DECLARE v_sql LONGTEXT;
  DECLARE v_para01 VARCHAR(100);
  DECLARE v_para02 VARCHAR(100);
  
  /*
    read paras from sys_transaction
    RelationKey = d 檔的 pk
    MiddleField = md 檔的關聯 key
  */
  SELECT 
    RelationKey, MiddleField, para01, para02,
    dest_table, dest_field, dest_field2, dest_field2_value
  INTO
    v_RelationKey, v_MiddleField, v_para01, v_para02,
    v_dest_table, v_dest_field, v_dest_field2, v_dest_field2_value
  FROM sys_transaction WHERE tablename = p_para_tablename;
    
  -- 建立動態 SQL
  IF v_dest_field2 IS NOT NULL AND v_dest_field2 != '' THEN
    SET v_sql = CONCAT(
      'UPDATE ', v_dest_table, ' SET ', 
      v_dest_field, ' = (SELECT ', v_dest_field, ' FROM ', p_para_tablename, 
      ' WHERE ', v_MiddleField, ' = ''', v_para01, '''), ',
      v_dest_field2, ' = (SELECT ', v_dest_field2, ' FROM ', p_para_tablename,
      ' WHERE ', v_MiddleField, ' = ''', v_para01, ''') ',
      'WHERE ', v_MiddleField, ' = ''', v_para01, ''';'
    );
  ELSE
    SET v_sql = CONCAT(
      'UPDATE ', v_dest_table, ' SET ', 
      v_dest_field, ' = (SELECT ', v_dest_field, ' FROM ', p_para_tablename,
      ' WHERE ', v_MiddleField, ' = ''', v_para01, ''') ',
      'WHERE ', v_MiddleField, ' = ''', v_para01, ''';'
    );
  END IF;
  
  SET @sql = v_sql;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;