Imagine, you have a big program with many processes. Someday, your records are updated or even deleled, but you don’t know who did it or which process did it. It’ll be a big trouble. In order to avoid it, we should implement DML hunter in order to increase traceability when needed.
I say when needed, because of logging DML histoty all the time will waste memory. So we’ll need it in some scenarios like we reproduce and debug an anonymous issue. I’d like to recommend you to apply the below step 1 and 2 since it’s a good trace info and don’t waste too much space. Step 3 and 4 is applied only when really needed where we’ll register everything for newly inserted/updated/deleted rows.🧐🧐
Let’s make an example, we have table emp as below.
To increase the traceability, you could do below steps :
1. Add the new additional 3 dw_xxx columns to table emp
For anyone wondering, dw stands for Data Warehouse
2. Create a ROW-LEVEL-BEFORE trigger on table emp
- The purpose of this trigger is populate the 3 dw_xxx columns in table emp
- For a detailed walkthrough about triggers, please check here
- If your trigger failed to compile as below, then you could fix it by granting a SELECT privilege on the view v_$session
Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilege on a synonym. If you want to give permission to a V$ view you must give it like below
GRANT SELECT ON v_$session TO hr;
3. Create a new table dml_emp as a DML’s history table of table emp
The new table dml_emp will have the same structure with emp (but don’t have the primary key) plus additional 3 columns in the 1st step plus additional 2 new comlumns dw_state_value, and dw_change_id.
4. Create a ROW-LEVEL-AFTER trigger on table emp
The purpose of this trigger is insert DML events into table dml_emp
Now let’s see the result 😋😋
- Insert 3 records into table emp
- Here we can see we have trace info. i.e : the records are inserted by PL/SQL Developer tool and on DESKTOP-UAUA29B machine.
- Use sql*plus tool and update one of those records.
- Here it shows that the record is updated by sql*plus tool. A pair value “32” helps us to know that they’re in the same DML event.
- Use sql*plus tool and delete one of those records.
- Here it shows that the record is deleted by sql*plus tool. A pair value “33” helps us to know that they’re in the same DML event.
_______________THE END_______________
