J. Chomel 2017-10-15T19:15:49
If you don't want to use Oracle native mechanism, you could have your very own framework that generates and reads your own auditing table (I know you can, we had similar thing where I once worked).\n\nHere are the main components:\n\n\na_sqnc is the sequence you will use in TrackTable to keep track of the order of actions in column NO_ORD (even though there is also a D_UPD column with the modification time).\n\n\n\n\ncreate sequence a_sqnc\nminvalue 1\nmaxvalue 99999999\nstart with 1\nincrement by 1\nnocache;\n\n\n\nTrackTable will have a TABLE_NAME column in order to track changes from different tables. It also have a PK_VALUE and ROW_VALUE where we store the data that changed. Here is the table creation with useful indexes:\n\n\n\n\ncreate table TrackTable (\n table_name VARCHAR2(50) not null,\n action VARCHAR2(240) not null,\n no_ord NUMBER(12) not null,\n nature VARCHAR2(3) not null,\n pk_value VARCHAR2(4000),\n row_value VARCHAR2(4000),\n ori VARCHAR2(250),\n c_user VARCHAR2(20),\n d_upd DATE\n);\n\ncreate index AP_D_UPD on TrackTable (D_UPD);\ncreate index AP_NO_ORD on TrackTable (NO_ORD);\ncreate index AP_TABLE_NAME on TrackTable (TABLE_NAME);\n\n\n\nSay you have a simple table BANK with two columns PK_val (the primary key) and val:\n\n\n\n\ncreate table BANK (\n pk_val VARCHAR2(50) not null,\n val VARCHAR2(240) not null\n);\n\nalter table BANK\n add constraint BK_PK primary key (pk_val)\n using index ;\n\n\n\nUse DBMS_APPLICATION_INFO.READ_MODULE(w_sess_mod, w_sess_act) to know what module and what action operates: I concatenate both in column ORI in TrackTable; \nuser Oracle session variable will allow you tracking who did the change in column c_user;\nHere is how to create trigger TRCK_BNK to track changes in table BANK; it will categorize in 3 actions: DELETE, UPDATE, INSERT (you can remove the INSERT case if needed).\n\n\n\n\nCREATE OR REPLACE TRIGGER \"TRCK_BNK\" \nAFTER DELETE OR INSERT OR UPDATE \n ON BANK\nREFERENCING NEW AS NEW OLD AS OLD\nFOR EACH ROW\n\nDECLARE\n w_a VARCHAR2(10);\n W_ERRM VARCHAR2(1000);\n W_CODE VARCHAR2(1000);\n w_n VARCHAR2(200) := 'BANK';\n w_id NUMBER := a_sqnc.nextval;\n w_act v$session.action%type;\n w_mod v$session.module%type;\n w_ori TrackTable.ORI%TYPE; \nBEGIN\n DBMS_APPLICATION_INFO.READ_MODULE(w_mod, w_act);\n w_ori := 'Module : '||w_mod ||' ; Action : '||w_act;\n ----------------------------------\n -- test which action is for change\n ----------------------------------\n IF UPDATING\n THEN\n w_a := 'UPDATE';\n ELSIF DELETING\n THEN\n w_a := 'DELETE';\n ELSIF INSERTING\n THEN\n w_a := 'INSERT';\n END IF;\n ----------------------------------\n -- Insert into TrackTable \n ----------------------------------\nIf w_a in ('UPDATE', 'DELETE') then\n Insert into TrackTable \n Select w_n, w_a, w_id, 'OLD', :OLD.pk_val, :OLD.val\n , w_ori, user, sysdate\n From Dual;\nEnd if;\n\n-- if you update, there is a new value and an old value\nIf w_a in ('UPDATE', 'INSERT') then\n Insert into TrackTable \n Select w_n, w_a, w_id, 'NEW', :NEW.pk_val, :NEW.val\n , w_ori, user, sysdate\n From Dual;\nEnd if;\n\nException\nWhen others then\n Begin\n W_ERRM := SQLERRM;\n W_CODE := SQLCODE;\n -- try inserting in case of error anyway\n Insert into TrackTable \n Select w_n, w_a, -1, 'ERR', 'Grrr: '||W_CODE, W_ERRM\n , w_ori, user, sysdate\n From Dual;\n End;\nEnd;\n/\n\n\nThen add functions to your framework that generates the triggers given a table, retrieves changes, reverts table to a given date...\n\nNB:\nThis way of tracking every change on the table impairs performances if table changes a lot. But it is great for parameter tables that scarcely change.",