How can one record which columns of a table had been updated and who had done the update with a trigger?

How To Monitor User Column Updates Via Trigger. (Doc ID 557041.1)

For example. If the column A and column B of a row in the table "TAB01" had been updated to new values by user "USER01",  want the information to be written down by a trigger to record the change.

SOLUTION

Suppose we have a table named "TTT" in schema "HR". It has 3 columns and 2 records in it currently. And USER "SCOTT" has the privilege SELECT,INSERT,UPDATE,DELETE on it.

 

SQL> conn hr/<PASSWORD>
Connected.
SQL> desc ttt
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER

SQL> select * from ttt;

COL1 COL2 COL3
---------- ---------- ----------
1 10 10
2 20 20

SQL> grant select,insert,update,delete on ttt to scott;

Grant succeeded.

Follow the below steps to accomplish the goal.

 

Step1. Create a log table "COL_REC" to store the monitoring information.

 

create table hr.col_rec
(host varchar2(50),
ip_address varchar2(20),
session_user varchar2(30),
update_time date,
row_id varchar2(20),
col1_stat varchar2(15),
col2_stat varchar2(15),
col3_stat varchar2(15));

Table created.

 

Step2. Create a trigger to write down the information to the log table "COL_REC" before update on table "TTT".

 

create or replace trigger hr.updated_col
before update of col1,col2,col3 on ttt
referencing old as old new as new
for each row
declare
v_host hr.col_rec.host%type;
v_ip_address hr.col_rec.ip_address%type;
v_session_user hr.col_rec.session_user%type;
v_col1_stat hr.col_rec.col1_stat%type;
v_col2_stat hr.col_rec.col2_stat%type;
v_col3_stat hr.col_rec.col3_stat%type;
begin
select sys_context('USERENV','HOST') into v_host from dual;
select sys_context('USERENV','IP_ADDRESS') into v_ip_address from dual;
select sys_context('USERENV','SESSION_USER') into v_session_user from dual;
--If the old value is not equal to the new value, it's regarded the value has been changed
If :old.col1=:new.col1 then
v_col1_stat:='Not updated';
else
v_col1_stat:='Updated';
end if;
If :old.col2=:new.col2 then
v_col2_stat:='Not updated';
else
v_col2_stat:='Updated';
end if;
If :old.col3=:new.col3 then
v_col3_stat:='Not updated';
else
v_col3_stat:='Updated';
end if;
insert into hr.col_rec values (v_host,v_ip_address,v_session_user,sysdate,:old.rowid,v_col1_stat,v_col2_stat,v_col3_stat);
end;

Trigger created.

 

Step3. Try to connect to database using user "SCOTT" and update table "TTT".

 

SQL> conn scott/<PASSWORD>@R920
Connected.
SQL> update hr.ttt set col2=30,col3=30 where col1=1;

1 row updated.

SQL> commit;

Commit complete.

 

Step4. Check the LOG table "COL_REC" to find the monitoring information.

 

SQL> conn hr/<PASSWORD>
Connected.

SQL> set lin 160
SQL> col HOST for a20
SQL> col IP_ADDRESS for a15
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from col_rec;

HOST IP_ADDRESS SESSION_USER UPDATE_TIME ROW_ID COL1_STAT COL2_STAT COL3_STAT
-------------------- --------------- ------------------------------ ------------------- -------------------- --------------- --------------- ---------------
COPORATION\samzhao 192.168.1.100 SCOTT 2008-03-10 15:33:19 AAAG4YAAHAAAA9mAAA Not updated Updated Updated

 

Step5. Mine the record of "COL_REC", you can find that A user "SCOTT" connected from "COPORATION\samzhao" had updated COL2 and COL3 of a ROW with ROWID 'AAAG4YAAHAAAA9mAAA' in table "TTT" at 2008-03-10 15:33:19

 

SQL> select * from ttt where rowid='AAAG4YAAHAAAA9mAAA';

COL1 COL2 COL3
---------- ---------- ----------
1 30 30