Hi Team,
I have to create a trigger which will record the old value as well as new value in case of insert. Situation is that I have one application and on frontend part whenever some changes happened then the xyz_id got changed. At the backend, I can see insert is happening. I wanted to log the old xyz_id and the new xyz_id in one history table. So that whenever I check for that history table I came to know that this was the old xyz_id and it has been changed to new xyz_id.
Please see the code I am using:
CREATE TABLE xyzID_log(
xyz_id_old number(11,0),
xyz_id_new number(11,0)
);
CREATE OR REPLACE PACKAGE audit_PKG AS
PROCEDURE CHECK_VAL( TAB_NAME IN VARCHAR2,COL_NAME IN VARCHAR2, NEW_VAL IN VARCHAR2,
OLD_VAL IN VARCHAR2 ) ;
END;
/
CREATE OR REPLACE PACKAGE BODY audit_PKG AS
PROCEDURE CHECK_VAL( TAB_NAME IN VARCHAR2, COL_NAME IN VARCHAR2, NEW_VAL IN VARCHAR2, OLD_VAL IN VARCHAR2 )
IS
BEGIN
IF ( NEW_VAL <> OLD_VAL OR
(NEW_VAL IS NULL AND OLD_VAL IS NOT NULL) OR
(NEW_VAL IS NOT NULL AND OLD_VAL IS NULL) )
THEN
INSERT INTO xyzID_log (xyz_id_old,xyz_id_new)
VALUES (OLD_VAL,NEW_VAL);
END IF;
END;
END audit_PKG;
/
CREATE OR REPLACE TRIGGER xyzAUDTRG_after
AFTER INSERT OR DELETE OR UPDATE ON Department FOR EACH ROW
DECLARE
V_TABLE_NAME VARCHAR2(50);
V_COL_NM VARCHAR2(50);
BEGIN
V_TABLE_NAME:='Department';
V_COL_NM:=xyz_ID;
audit_pkg.check_val('Department', 'xyz_ID', :new.xyz_ID, :old.xyz_ID);
END;
/
When I am running this code and in frontend application insert happen, then the output of xyzID_log this table is as below:
xyz_id_old xyz_id_new
NULL 7
I want that the column xyz_id_old also should have a value instead of NULL.
I know that in case of insert we can get only New value and old value will be NULL. But this is my task.
I am explaining you one situation:
Let us assume the value of xyz_ID =5
In application whenever I am doing something like changing time of a program , its xyz_ID got changed from 5 to 7. So in this case old xyz_ID =5 and new xyz_ID=7 . After checking the trace file I can see that insert happens for this type of change.
So in above xyzID_log table ,for column xyz_id_old I want that its value should show 5 not NULL.
Can you please help me know?
I would just store the new value, and then a simple analytic SQL to pick the old value. For example, (where 'id' just populate by a sequence to give order)
SQL> create table T ( id int, newval int);
Table created.
SQL>
SQL> insert into t values (1, 10);
1 row created.
SQL> insert into t values (2, 20);
1 row created.
SQL> insert into t values (3, 50);
1 row created.
SQL> insert into t values (4, 10);
1 row created.
SQL> insert into t values (5, 24);
1 row created.
SQL> insert into t values (6, 22);
1 row created.
SQL> insert into t values (7, 12);
1 row created.
SQL>
SQL> select * from t;
ID NEWVAL
---------- ----------
1 10
2 20
3 50
4 10
5 24
6 22
7 12
7 rows selected.
SQL>
SQL> select id,
2 lag(newval) over ( order by id ) as oldval,
3 newval
4 from t;
ID OLDVAL NEWVAL
---------- ---------- ----------
1 10
2 10 20
3 20 50
4 50 10
5 10 24
6 24 22
7 22 12
7 rows selected.