SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE facility_audit_log ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR2(100), column_name VARCHAR2(100), old_value VARCHAR2(4000), -- 根據需要調整長度 new_value VARCHAR2(4000), -- 根據需要調整長度 modified_by VARCHAR2(100), modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE TRIGGER after_sml_am_facility_update AFTER INSERT OR UPDATE ON SML_AM_FACILITY 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_AM_FACILITY' 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_AM_FACILITY', col.column_name, v_old_value, v_new_value, USER); END LOOP; END;