想用触发器记录跟踪一张表数据的更新情况, 记录下来什么字段发生了变化, 变化前后的内容
create or replace trigger t_update_itemm
before update
on itemm for each row
declare
temp varchar(30);
begin
FOR up_columns IN (SELECT c.column_name
FROM user_tab_cols c
WHERE c.table_name = upper('itemm')) LOOP
IF updating(up_columns.column_name) THEN
temp := up_columns.column_name;
-- execute immediate 'insert into aaaa (name) values (:old.'||temp||')';
-- execute immediate 'insert into aaaa (name) values (:old.fitemno)';
insert into aaaa (name) values(temp);
END IF;
END LOOP;
end;
up_columns.column_name能准确的获取, 并记录到测试表aaaa中. 但是无法获取更新前后的值, :old.temp(记录字段名的变量)这种写法是错误的. 有大神知道怎么动态获取么
create or replace trigger t_update_itemm
before update
on itemm for each row
declare
temp varchar(30);
begin
FOR up_columns IN (SELECT c.column_name
FROM user_tab_cols c
WHERE c.table_name = upper('itemm')) LOOP
IF updating(up_columns.column_name) THEN
temp := up_columns.column_name;
-- execute immediate 'insert into aaaa (name) values (:old.'||temp||')';
-- execute immediate 'insert into aaaa (name) values (:old.fitemno)';
insert into aaaa (name) values(temp);
END IF;
END LOOP;
end;
up_columns.column_name能准确的获取, 并记录到测试表aaaa中. 但是无法获取更新前后的值, :old.temp(记录字段名的变量)这种写法是错误的. 有大神知道怎么动态获取么