CODEWITHSUNDEEP
sqloracle-databasetriggersoracle11g
Rn.
Rn.

Reputation: 167

Trigger is not firing when previous value is null

I am inserting old and new values to audit trail table using trigger when a change happens in the master table. 

create or replace TRIGGER audit_trg
  BEFORE UPDATE ON Employee
  FOR EACH ROW
BEGIN
  IF( UPDATING( 'E_AGE' ) )
  THEN
  IF(NVL(:OLD.E_AGE,0)!=NVL(:new.E_AGE,0))
  THEN
   INSERT INTO AUDIT_TRAIL( ID,PRIMARY_KEY,TABLENAME,COLUMNNAME,OLDVALUE,NEWVALUE,UPDATEDDATETIME,UPDATEDBY ,DESCRIPTION)
      VALUES(AUDIT_TRAIL_SEQ.NEXTVAL,:new.ID,'Employee','E_AGE',NVL(:OLD.E_AGE,0),:new.E_AGE,sysdate, :new.UPDATEDBY,:new.DESCRIPTION );
  END IF;
  END IF;
END;

But it is not inserting any values to Audit trail table only if the previous value is null.

Upvotes: 1

Views: 2824

Answers (2)

user4826005
user4826005

Reputation: 3

I am having similar issues, before update trigger is not creating any history when the value is changing from null to a value. My logic is working for any changing except for the null values

if nvl(:old.price,null) <> nvl(:new.price,null) then insert into bla bla.... values (......); end if;

Upvotes: -1

TommCatt
TommCatt

Reputation: 5636

Try 

if nvl( :old.e_age, 0 ) != nvl( :new.e_age, 0 )

You want to get rid of the NULL from both sides of the comparison.

EDIT: This is my test of the trigger. Rows 1 and 4 are a "no change" condition and the Trail column should remain unchanged with a zero value. The rest represent a change: from one value to another(2), from a value to NULL(3) and from NULL to a value(5). For these rows, the Trail value should be the OldAge/NewAge value. And they are.

drop table TestEmp;
create table TestEmp(
    id      int,
    e_age   int,
    NewVal  int,
    Trail   int default 0
);

insert into TestEmp( id, e_age, NewVal )
    select  1, 10, 10 from dual union all
    select  2, 10,  8 from dual union all
    select  3, 10, null from dual union all
    select  4, null, null from dual union all
    select  5, null, 10 from dual;

create or replace TRIGGER audit_trg
  BEFORE UPDATE ON TestEmp
  FOR EACH ROW
BEGIN
    IF UPDATING( 'E_AGE' ) THEN
        IF NVL( :OLD.e_age, 0 ) != NVL( :new.e_age, 0 ) THEN
            :new.Trail := :new.e_age;
        END IF;
    END IF;
END;
/
select * from TestEmp;
update TestEmp set e_age = NewVal;
select * from TestEmp;

Before update:

ID   E_AGE NewVal TRAIL
 1      10     10     0
 2      10      8     0
 3      10 <null>     0
 4  <null> <null>     0
 5  <null>     10     0

After update:

ID   E_AGE NewVal TRAIL
 1      10     10     0
 2      10      8     8
 3  <null> <null> <null>
 4  <null> <null>     0
 5      10     10    10

So the if statement was entered for rows 2, 3, 5 -- as expected.

Upvotes: 2

Related Questions