SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE TRIGGER after_sml_fac_corp_repay_update AFTER INSERT OR UPDATE ON SML_FAC_CORP_REPAY FOR EACH ROW DECLARE v_old_value VARCHAR2(4000); v_new_value VARCHAR2(4000); -- 用於存儲動態生成的 SQL 語句 BEGIN -- 動態生成插入語句，記錄所有列的值 FOR col IN (SELECT column_name FROM all_tab_cols WHERE table_name = 'SML_FAC_CORP_REPAY' AND column_name NOT IN ('CREATION_DATE', 'LAST_UPDATE_DATE')) -- 排除自動生成列 LOOP -- 動態獲取舊值和新值 EXECUTE IMMEDIATE 'SELECT :OLD.' || col.column_name || ' FROM dual' INTO v_old_value USING :OLD; EXECUTE IMMEDIATE 'SELECT :NEW.' || col.column_name || ' FROM dual' INTO v_new_value USING :NEW; -- 插入審計記錄（無論是否有更新） INSERT INTO facility_audit_log (table_name, column_name, old_value, new_value, modified_by) VALUES ('SML_FAC_CORP_REPAY', col.column_name, v_old_value, v_new_value, USER); END LOOP; END;