CODEWITHSUNDEEP
oracle-databaseplsqldatabase-trigger
Thilina
Thilina

Reputation: 137

How to get exact updated column name in oracle trigger?

I'm going to create a trigger to audit a table. Suppose my table define as below.

COUNTRY_ID   NOT NULL CHAR(2)      
COUNTRY_NAME          VARCHAR2(40) 
REGION_ID             NUMBER

and my log table created as below.

create table country_log(
  username varchar2(10),
  transaction_date date,
  new_value varchar(20),
  old_value varchar(20)
)

My half completed trigger would be like below.

CREATE OR REPLACE TRIGGER tr_countryTable
AFTER UPDATE ON COUNTRIES
FOR EACH ROW
BEGIN
  insert into country_log (username,transaction_date,new_value,old_value ) values (USER, sysdate,**:New, :Old** );
END;
/

I need to know instead of comparing each column value in :old and :new, how to get exactly updated column's new and old values.

Upvotes: 2

Views: 4343

Answers (1)

diziaq
diziaq

Reputation: 7825

In an UPDATE trigger, a column name can be specified with an UPDATING predicate to determine if the named column is being updated. Let's define a trigger as the following:

CREATE OR REPLACE TRIGGER tr_countryTable
AFTER UPDATE ON COUNTRIES
FOR EACH ROW
BEGIN
   IF UPDATING ('COUNTRY_NAME') THEN 
     -- :new.COUNTRY_NAME is new value
     -- :old.COUNTRY_NAME is old value
   END IF;

   IF UPDATING ('REGION_ID') THEN 
     -- :new.REGION_ID is new value
     -- :old.REGION_ID is old value
   END IF;

END;
/

Oracle 11g triggers documentation

Upvotes: 2

Related Questions