How to compare old value and new value in triggers and then mark as insert/update/delete in audit table

CREATE TABLE source_det (
det_id number(10) by default IDENTITY
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(30)
);

INSERT INTO source_det VALUES(1,11,'SOURCE',992,null);
INSERT INTO source_det VALUES(2,11,'SOURCE',637,null);
INSERT INTO source_det VALUES(3,11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(4,11,'TARGET',637,null);
INSERT INTO source_det VALUES(5,12,'TARGET',637,null);
Audit table:

CREATE SEQUENCE audit_tab_sq;
CREATE TABLE audit_tab (
    a_id       NUMBER(10) default audit_tab_sq.nextval,
    l_transaction varchar2(20),--INSERT, UPDATE, DELETE
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    value_old  VARCHAR2(20),
    value_new  VARCHAR2(20)
);
I need to create a trigger that will get fired whenever there is new event occurred on the main table i.e source_det

​x
 
CREATE TABLE source_det (det_id number(10) by default IDENTITY    e_id       NUMBER(10),    sys_name   VARCHAR2(20),    ref_id     NUMBER(10),    sys_other  VARCHAR2(30));​INSERT INTO source_det VALUES(1,11,'SOURCE',992,null);INSERT INTO source_det VALUES(2,11,'SOURCE',637,null);INSERT INTO source_det VALUES(3,11,'SOURCE',null,'Manual');INSERT INTO source_det VALUES(4,11,'TARGET',637,null);INSERT INTO source_det VALUES(5,12,'TARGET',637,null);Audit table:​CREATE SEQUENCE audit_tab_sq;CREATE TABLE audit_tab (    a_id       NUMBER(10) default audit_tab_sq.nextval,    l_transaction varchar2(20),--INSERT, UPDATE, DELETE    e_id       NUMBER(10),    sys_name   VARCHAR2(20),    value_old  VARCHAR2(20),    value_new  VARCHAR2(20));I need to create a trigger that will get fired whenever there is new event occurred on the main table i.e source_det​​

My Attempt:

create or replace trigger audit_tab_trg
AFTER INSERT OR DELETE OR UPDATE ON source_det 
FOR EACH ROW
BEGIN
  --Need to insert records into the audit table whenever there is a new entry
  IF inserting THEN
    --new ref_id value
     INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.ref_id);
    
    --new sys_other 
     INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.sys_other);
  END IF;
END;

 
create or replace trigger audit_tab_trgAFTER INSERT OR DELETE OR UPDATE ON source_det FOR EACH ROWBEGIN  --Need to insert records into the audit table whenever there is a new entry  IF inserting THEN    --new ref_id value     INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.ref_id);        --new sys_other      INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.sys_other);  END IF;END;​

When I have inserted one record for ref_id into the table source_Det then in the audit table I am getting two records but ideally, it should check and load only that value which is newly inserted.

Current Output:

+------+---------------+------+----------+-----------+-----------+
| a_id | l_transaction | e_id | sys_name | value_old | value_new |
+------+---------------+------+----------+-----------+-----------+
|    1 | INSERT        |   13 | TARGET   | null      | 637       |
|    2 | INSERT        |   13 | TARGET   | null      | null      |
+------+---------------+------+----------+-----------+-----------+

 
+------+---------------+------+----------+-----------+-----------+| a_id | l_transaction | e_id | sys_name | value_old | value_new |+------+---------------+------+----------+-----------+-----------+|    1 | INSERT        |   13 | TARGET   | null      | 637       ||    2 | INSERT        |   13 | TARGET   | null      | null      |+------+---------------+------+----------+-----------+-----------+​

Expected Output:

+------+---------------+------+----------+-----------+-----------+
| a_id | l_transaction | e_id | sys_name | value_old | value_new |
+------+---------------+------+----------+-----------+-----------+
|    1 | INSERT        |   13 | TARGET   | null      |       637 |
+------+---------------+------+----------+-----------+-----------+

 
+------+---------------+------+----------+-----------+-----------+| a_id | l_transaction | e_id | sys_name | value_old | value_new |+------+---------------+------+----------+-----------+-----------+|    1 | INSERT        |   13 | TARGET   | null      |       637 |+------+---------------+------+----------+-----------+-----------+​

Basically, I need to check two columns ref_id and sys_other. If only ref_id entered by user or modified by user or deleted by user then it should get marked as inserted/updated/deleted into the audit table. And If only sys_other entered by user or modified by user or deleted by user then it should get marked as inserted/updated/deleted into the audit table.

Answer

Why you didn’t listen to my previous suggestion? You should really add new pair of new/old columns into the audit table, not put everything into value_old/value_new because – as you don’t store information what it represents – you’ll have to guess (and that’s the way to problems).

BTW, what will you do if you – in the same statement – modify both REF_ID and SYS_OTHER? You’ll have to check what you did first, and then insert (or not) a row. Too much code for a little benefit.

if :new.ref_id is not null then
  INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.ref_id);
elsif :new.sys_other is not null then
  INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.sys_other);
end if;

 
if :new.ref_id is not null then  INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.ref_id);elsif :new.sys_other is not null then  INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)     VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.sys_other);end if;​

Advertisement

Answer