Skip to Main Content
  • Questions
  • Trigger to capture before and after insert value of particular column

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Yamini.

Asked: April 24, 2017 - 9:27 am UTC

Last updated: April 26, 2017 - 4:48 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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

and Connor said...

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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How do I map?

Yamini Bhatia, April 25, 2017 - 9:36 am UTC

Hi Team,

Your point is correct. This insert is happening in below scenairos:

1. If I am changing time for any program

How can I map to achieve this target. What should I do?

Ratan, April 25, 2017 - 2:54 pm UTC

how to trace action or any event raise on schema like drop table or modify procedure or function?
So how can i capture all event at schema level?
Connor McDonald
April 26, 2017 - 4:48 am UTC

You could start with something like this:

DROP TABLE DDL_LOG CASCADE CONSTRAINTS
/

CREATE TABLE DDL_LOG
(
  TSTAMP       TIMESTAMP(6)                     NOT NULL,
  HOST         VARCHAR2(100),
  IP_ADDRESS   VARCHAR2(100),
  MODULE       VARCHAR2(100),
  OS_USER      VARCHAR2(100),
  TERMINAL     VARCHAR2(100),
  OPERATION    VARCHAR2(100),
  OWNER        VARCHAR2(50),
  OBJECT_NAME  VARCHAR2(50),
  OBJECT_TYPE  VARCHAR2(50),
  SQLTEXT      CLOB,
  PREVSQLTEXT  CLOB
)
/


DROP TRIGGER capture_all_ddl
/

CREATE OR REPLACE TRIGGER CAPTURE_ALL_DDL
after create or alter or drop on database
begin
    insert into ddl_log
    values (systimestamp,
                sys_context('USERENV','HOST'),
                sys_context('USERENV','IP_ADDRESS'),
                sys_context('USERENV','MODULE'),
                sys_context('USERENV','OS_USER'),
                sys_context('USERENV','TERMINAL'),
                ora_sysevent,
                ora_dict_obj_owner,
                ora_dict_obj_name,
                ora_dict_obj_type,
                  ( select sql_fulltext from v$sql
                    where sql_id = ( select sql_id from v$session where sid = sys_context('USERENV','SID') and rownum = 1 )
                  )
                ,
                  ( select sql_fulltext from v$sql
                    where sql_id = ( select prev_sql_id from v$session where sid = sys_context('USERENV','SID') and rownum = 1 )
                  )
           );
     commit;
  end if;
exception
  when others then ...
end;
/



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library