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);
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)
);
My attempt:
create or replace trigger audit_tab_trg
AFTER INSERT OR DELETE OR UPDATE ON source_det
FOR EACH ROW
BEGIN
--INSERTING
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);
elsif (:new.ref_id is not null) AND (: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.ref_id);
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;
Problem statement:
- I need to insert a record into the audit table whenever new insertion or updating happens.
Trigger is working fine when I am giving single value for either ref_id or sys_other but not working when giving both the column values at once as explained below
INSERT INTO source_det VALUES (13, 'TARGET', 637, null);
Getting inserted into the audit table:
------ --------------- ------ ---------- ----------- -----------
| a_id | l_transaction | e_id | sys_name | value_old | value_new |
------ --------------- ------ ---------- ----------- -----------
| 1 | INSERT | 13 | TARGET | null | 637 |
------ --------------- ------ ---------- ----------- -----------
But when I am inserting both the records i.e for ref_id and sys_other then two entries should be inserted into the audit table
INSERT INTO source_det VALUES (15, 'SOURCE', 637, 'Upload');
Current Output:
------ --------------- ------ ---------- ----------- -----------
| a_id | l_transaction | e_id | sys_name | value_old | value_new |
------ --------------- ------ ---------- ----------- -----------
| 1 | INSERT | 15 | SOURCE | null | 637 |
------ --------------- ------ ---------- ----------- -----------
Expected Output:
------ --------------- ------ ---------- ----------- ----------- --
| a_id | l_transaction | e_id | sys_name | value_old | value_new | |
------ --------------- ------ ---------- ----------- ----------- --
| 1 | INSERT | 15 | SOURCE | null | 637 | |
| 2 | INSERT | 15 | SOURCE | null | Upload | |
------ --------------- ------ ---------- ----------- -----------
Same for update how to do this:
Below is the try which I have given
if (:old.ref_id != :new.ref_id ) then
INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
VALUES (audit_tab_sq.NEXTVAL,'UPDATE',:old.e_id,:old.sys_name,:old.ref_id,:new.ref_id);
elsif (:old.sys_other != :new.sys_other ) then
INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
VALUES (audit_tab_sq.NEXTVAL,'UPDATE',:old.e_id,:old.sys_name,:old.sys_other,:new.sys_other);
end if;
Tool used: SQL Developer(18c)
CodePudding user response：
IFs should be rearranged so that the "last" (in your code) is examined "first":
SQL> create or replace trigger audit_tab_trg
2 after insert or delete or update on source_det
3 for each row
4 begin
5 if (:new.ref_id is not null) and (:new.sys_other is not null) then
6 insert into audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
7 values (audit_tab_sq.nextval,'INSERT',:new.e_id,:new.sys_name,null,:new.ref_id);
8 insert into audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
9 values (audit_tab_sq.nextval,'INSERT',:new.e_id,:new.sys_name,null,:new.sys_other);
10 elsif :new.ref_id is not null then
11 insert into audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
12 values (audit_tab_sq.nextval,'INSERT',:new.e_id,:new.sys_name,null,:new.ref_id);
13 elsif :new.sys_other is not null then
14 insert into audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
15 values (audit_tab_sq.nextval,'INSERT',:new.e_id,:new.sys_name,null,:new.sys_other);
16 end if;
17 end;
18 /
Trigger created.
Testing:
SQL> insert into source_det (det_id, e_id, sys_name, ref_id, sys_other)
2 values (audit_tab_sq.nextval, 15, 'SOURCE', 637, 'Upload');
1 row created.
SQL> select * from audit_tab;
A_ID L_TRANSACTION E_ID SYS_NAME VALUE_OLD VALUE_NEW
---------- -------------------- ---------- -------------------- -------------------- --------------------
10 INSERT 15 SOURCE 637
11 INSERT 15 SOURCE Upload
SQL>
CodePudding user response：
Testing : we can reduce the code for code redundancy. find below updated code.
create or replace trigger audit_tab_trg
after insert or delete or update on source_det
for each row
begin
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);
end if;
if :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;
end;