触发特定列的UPDATE

Question

CREATE TABLE regions (
region_id number NOT NULL,
region_name varchar2(25) NULL,
CONSTRAINT reg_id_pk PRIMARY KEY (region_id)
);
CREATE TABLE tbl_history
(
operation character varying(120) NOT NULL ,
table_name character varying(120) NOT NULL ,
column_name character varying(120) NOT NULL, 
old_value character varying(120),
new_value character varying(120)
);

CREATE OR REPLACE trigger regions_audit
BEFORE INSERT OR DELETE OR UPDATE ON regions
FOR EACH ROW
ENABLE
DECLARE
BEGIN
for c in (SELECT table_name, column_name from user_tab_columns ) loop
if updating(c.column_name ) then
INSERT INTO tbl_history (operation,table_name, column_name, old_value, new_value) 
VALUES('Update',c.table_name,c.column_name,:old.column_name,:new.column_name);
end if;
end loop;
END;
/

触发一个动态列的更新?结果是:操作table_name column_name old_value new_value_UPDATE regions region_name Asia EuropeUPDATE region_id 5 4

Answer 1

这是徒劳的，你不能创建"generic"触发器可以在任何桌子上工作。在Oracle中，触发器被锚定在到它们的表上，所以显示如何做的代码可能是

SQL> create or replace trigger regions_audit
2    before insert or delete or update on regions
3    for each row
4    enable
5  declare
6  begin
7    if updating then
8      insert into tbl_history
9        (operation, table_name, column_name, old_value, new_value)
10         values
11         ('Update', 'regions', 'region_name', :old.region_name,:new.region_name);
12    end if;
13  end;
14  /
Trigger created.
SQL>

换句话说:表和列名是static;表名是regions，因为这是触发器所基于的表。列名是region_name，因为您确实不应该修改主键列的值。新旧值也与region_name有关。

对于其他操作(删除、插入)，您也可以执行类似的操作。