CODEWITHSUNDEEP
oracle-databaseloggingplsqltriggersdatabase-trigger
NewJavaEnthusiast
NewJavaEnthusiast

Reputation: 103

How to store changes history data in Oracle and handle the non-changed values

Hey guys I have one table called Users: enter image description here

Then I have another table holding the changes, made to the above table, called Users_History_Changes: enter image description here

I know I need trigger, which fires, when one table is updated and inserts values in Users_History_Changes table. But here is the thing I can't do. When a log is made in Users_History_Changes table, and only Last_Name is updated, other fields must remain empty. Then, the First_Name is changed, so the table shows only that. At the end we change the Age, and the User, with ID = 1, becomes from 'Raul Peres, 25' to 'Pedro Felipes, 30'. Time_Stamp is when the change is made.

Upvotes: 0

Views: 2204

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

You can test whether each value has changed as part of the insert into the history table:

create trigger users_trigger
before insert or update on users
for each row
begin
  insert into users_history_changes (id, first_name, last_name, age, timestamp_changes)
  values (:new.id,
    case when :old.first_name is null or :new.first_name != :old.first_name then :new.first_name end,
    case when :old.last_name is null or :new.last_name != :old.last_name then :new.last_name end,
    case when :old.age is null or :new.age != :old.age then :new.age end,
    systimestamp);
end;
/

It probably isn't useful to check if a value has changed to null, since that wouldn't be a very helpful history record, so I've only checked from null. And even those tests can/should be expanded to cover edge cases, e.g. to check that something has actually changed.

Anyway, with the following statements:

insert into users
select 1, 'Raul', 'Peres', 25 from dual
union all select 2, 'Francis', 'Lotters', 40 from dual
union all select 3, 'Maria', 'Lopez', 39 from dual;

update users set last_name = 'Felipes' where id = 1;
update users set first_name = 'Pedro' where id = 1;
update users set age = 30 where id = 1;

update users set first_name = 'Maria', last_name = 'Sanchez', age = 40 where id = 3;

the history table ends up with:

        ID FIRST_NAME LAST_NAME         AGE TIMESTAMP_CHANGES           
---------- ---------- ---------- ---------- ----------------------------
         1 Raul       Peres              25 19-JUN-19 20.02.33.470409000
         2 Francis    Lotters            40 19-JUN-19 20.02.33.473139000
         3 Maria      Lopez              39 19-JUN-19 20.02.33.473183000
         1            Felipes               19-JUN-19 20.02.33.548101000
         1 Pedro                            19-JUN-19 20.02.33.594305000
         1                               30 19-JUN-19 20.02.33.640293000
         3            Sanchez            40 19-JUN-19 20.02.33.688710000

db<>fiddle

Alternatively, if you want one row per changed value, you could use the updating clause:

create trigger users_trigger
before insert or update on users
for each row
begin
  if inserting then
    insert into users_history_changes (id, first_name, last_name, age, timestamp_changes)
    values (:new.id, :new.first_name, :new.last_name, :new.age, systimestamp);
  end if;

  if updating ('FIRST_NAME') then
    insert into users_history_changes (id, first_name, timestamp_changes)
    values (:new.id, :new.first_name, systimestamp);
  end if;
  if updating ('LAST_NAME') then
    insert into users_history_changes (id, last_name, timestamp_changes)
    values (:new.id, :new.last_name, systimestamp);
  end if;
  if updating ('AGE') then
    insert into users_history_changes (id, age, timestamp_changes)
    values (:new.id, :new.age, systimestamp);
  end if;
end;
/

but that will generate a row for values that are 'changed' to the same value, unless you add further logic to check for actual changes - the updating() check is that the update statement included the column in its set list. db<>fiddle

Upvotes: 3

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

You can compare new and old values for update logging

create or replace trigger trg_users on users
after insert or update 
for each row
declare
  v_dml_type varchar2(1);

  procedure pr_upd_log( i_dml_type varchar2, i_col_name varchar2, 
                        i_old_val  varchar2, i_new_val  varchar2 ) is
  begin
    insert into Users_History_Changes( dml_type, col_name, old_val, new_val )
     values( i_dml_type, i_col_name, i_old_val, i_new_val );
  end;
begin

  if updating then v_dml_type := 'U';
  elsif inserting then v_dml_type := 'I'; end if; 

  if ( nvl(:old.user_id,-987) != nvl(:new.user_id,-987) ) then     
     pr_upd_log(v_dml_type,'user_id',to_char(:old.user_id),to_char(:new.user_id));
  end if;

  if ( nvl(:old.age,-987) != nvl(:new.age,-987) ) then     
     pr_upd_log(v_dml_type,'age',to_char(:old.age),to_char(:new.age));
  end if;

  if ( nvl(:old.first_name,'NuLLxYZ') != nvl(:new.first_name,'NuLLxYZ') ) then     
     pr_upd_log(v_dml_type,'first_name',:old.first_name,:new.first_name);
  end if;

  if ( nvl(:old.last_name,'NuLLxYZ') != nvl(:new.last_name,'NuLLxYZ') ) then     
     pr_upd_log(v_dml_type,'last_name',:old.last_name,:new.last_name);
  end if;     
  -- for v_dml_type = 'I', use your former way
end;

Upvotes: 0

Related Questions