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.
I cannot use LAG function because
in my case this is not the situation. For ex:
When someone execute the application then some insert happens on xyz_id and below are the values
Before_Insert_xyz_id After_insert_xyz_id Date
20 30 18 Apr
50 10 19 Apr
40 15 21 Apr
So in my case, after_insert value is not becoming the before_insert value whenever Insert is happening. This is because in our application we have different different programs scheduled for different timings. All programs have xyz_id. Sometimes insert happened on program A, sometime it happened on program B etc., so accordingly insert value is different. Say for example, an insert happened on program A then old value for xyz_id is 20 and after insert it is 30.
after some time or some days insert happened on program C and then old value for xyz_id is 50 and after insert it is 10.
Moreover I have to achieve this functionality in a trigger. I am stuck and not able to figure out how can I achieve this.
Please guide me.
Thanks in advance :)
Let me stress... for *every* insert, the :old value is null, because there is *no* old row.
The only information you have in a trigger is:
insert - new only
update - old and new
delete - old only
It is possible that your front end application *re-interprets* what the user *perceives* is occurring, eg, the screen might suggest a value is being "updated" but the code might be doing a delete-then-insert to achieve it. Or it might mark an existing record is "not to be seen again" (via the application) and then inserting a new "active" record etc.
But at the database level, there is *never* an old value for an insert - because it is a *new* record being created.
You'll need to map what the application is doing to what the database is doing in order to correctly present an audit trail.