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

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;

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      |
 ------ --------------- ------ ---------- ----------- -----------

Expected Output:

 ------ --------------- ------ ---------- ----------- ----------- 
| 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.

CodePudding user response：

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;