Hi Team,
I have a requirement to generate audit report on a parent-child table.
We are capturing changes to the tables using trigger. But when I am using the audit tables to reconstruct the history I am getting phantom rows which never existed in history.
Below is a sample use-case.
N.B. - Both parent and child table are candidates for insert, update, delete.
drop table products;
create table products (prod_id number, product_category varchar2(20), product_desc varchar2(20), price number);
drop table AUDIT_PRODUCTS;
create table AUDIT_PRODUCTS (prod_id number, product_category varchar2(20), product_desc varchar2(20), price number, aud_who varchar2(100), aud_when date, aud_operation varchar2(20), aud_module varchar2(20));
drop table transactions;
create table transactions (txn_id number, prod_id number, qty number, txn_date date);
drop table AUDIT_transactions;
create table AUDIT_transactions (txn_id number, prod_id number, qty number, txn_date date, aud_who varchar2(100), aud_when date, aud_operation varchar2(20), aud_module varchar2(20));
create or replace trigger TRG_AUDIT_PRODUCTS
for insert or update or delete on PRODUCTS compound trigger
type t_row_list is table of AUDIT_PRODUCTS%rowtype index by pls_integer;
l_audit_rows t_row_list;
l_operation varchar2(1) :=
case when updating then 'U'
when deleting then 'D'
else 'I' end;
procedure insert_logged_so_far is
begin
forall i in 1 .. l_audit_rows.count
insert into AUDIT_PRODUCTS
values l_audit_rows(i);
l_audit_rows.delete;
end;
before statement is
begin
l_audit_rows.delete;
end before statement;
after each row is
begin
l_audit_rows(l_audit_rows.count+1).aud_who := sys_context('USERENV','SESSION_USER');
l_audit_rows(l_audit_rows.count).aud_when := sysdate;
l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
l_audit_rows(l_audit_rows.count).aud_module := sys_context('USERENV','MODULE');
if updating or inserting then
l_audit_rows(l_audit_rows.count).prod_id := :new.prod_id;
l_audit_rows(l_audit_rows.count).product_category := :new.product_category;
l_audit_rows(l_audit_rows.count).product_desc := :new.product_desc;
l_audit_rows(l_audit_rows.count).price := :new.price;
else
l_audit_rows(l_audit_rows.count).prod_id := :old.prod_id;
l_audit_rows(l_audit_rows.count).product_category := :old.product_category;
l_audit_rows(l_audit_rows.count).product_desc := :old.product_desc;
l_audit_rows(l_audit_rows.count).price := :old.price;
end if;
if l_audit_rows.count > 1000 then
insert_logged_so_far;
end if;
end after each row;
after statement is
begin
if l_audit_rows.count > 0 then
insert_logged_so_far;
end if;
end after statement;
end;
/
create or replace trigger TRG_AUDIT_TRANSACTIONS
for insert or update or delete on PRODUCTS compound trigger
type t_row_list is table of AUDIT_TRANSACTIONS%rowtype index by pls_integer;
l_audit_rows t_row_list;
l_operation varchar2(1) :=
case when updating then 'U'
when deleting then 'D'
else 'I' end;
procedure insert_logged_so_far is
begin
forall i in 1 .. l_audit_rows.count
insert into AUDIT_TRANSACTIONS
values l_audit_rows(i);
l_audit_rows.delete;
end;
before statement is
begin
l_audit_rows.delete;
end before statement;
after each row is
begin
l_audit_rows(l_audit_rows.count+1).aud_who := sys_context('USERENV','SESSION_USER');
l_audit_rows(l_audit_rows.count).aud_when := sysdate;
l_audit_rows(l_audit_rows.count).aud_operation := l_operation;
l_audit_rows(l_audit_rows.count).aud_module := sys_context('USERENV','MODULE');
if updating or inserting then
l_audit_rows(l_audit_rows.count).txn_id := :new.txn_id;
l_audit_rows(l_audit_rows.count).prod_id := :new.prod_id;
l_audit_rows(l_audit_rows.count).qty := :new.qty;
l_audit_rows(l_audit_rows.count).txn_date := :new.txn_date;
else
l_audit_rows(l_audit_rows.count).txn_id := :old.txn_id;
l_audit_rows(l_audit_rows.count).prod_id := :old.prod_id;
l_audit_rows(l_audit_rows.count).qty := :old.qty;
l_audit_rows(l_audit_rows.count).txn_date := :old.txn_date;
end if;
if l_audit_rows.count > 1000 then
insert_logged_so_far;
end if;
end after each row;
after statement is
begin
if l_audit_rows.count > 0 then
insert_logged_so_far;
end if;
end after statement;
end;
/
insert into products (prod_id, product_category, product_desc, price)
values (1, 'GROCERY', 'RICE', 10);
insert into products (prod_id, product_category, product_desc, price)
values (2, 'GROCERY', 'PULSE', 20);
insert into products (prod_id, product_category, product_desc, price)
values (3, 'STATIONERY', 'BOOKS', 30);
insert into products (prod_id, product_category, product_desc, price)
values (4, 'SPORTS', 'BALL', 5);
insert into transactions (txn_id, prod_id, qty, txn_date)
values (1, 1, 3, SYSDATE);
insert into transactions (txn_id, prod_id, qty, txn_date)
values (1, 2, 4, SYSDATE);
insert into transactions (txn_id, prod_id, qty, txn_date)
values (1, 4, 5, SYSDATE);I am using the below query to generate a audit report which will give the historical view.
select a.product_desc, a.product_category, a.price, b.qty, (a.price * b.qty) total_amt, b.txn_date
from audit_products a, audit_transactions b
where a.prod_id = b.prod_id;
update products
set price = 11 where product_id=1;
Now if I use the same query I am getting rows which never existed at any point of time due to cartesian join.
select a.product_desc, a.product_category, a.price, b.qty, (a.price * b.qty) total_amt, b.txn_date
from audit_products a, audit_transactions b
where a.prod_id = b.prod_id;
Now both the tables can be candidates for update. In which case we are generating phantom rows which is not the true history.
Please suggest design changes to generate correct audit report.
Regards
Joyan