Trigger fails to update the column value

are you trying to check for null values?


new.column2 <> ''

that won't work,  because you cant' check for equality or inequality to a null

try this...

new.column2 is not null

also,  for simple pl/sql assignments,  it's inefficient to select from dual

just assign it directly.

:new.column2 := unistr(:new.column2);


and,  what values are you updating?  unistr isn't going to change everything.

unistr('test')  =  'test'    --- no change here

unistr('test\\\00f1')  = test\ñ   --- this did change

Original code posted working for me as it was.
Tested this in Oracle 11g though.
Please see if this is of any help.
=======================================================================

SQL> create table my_Table(column2 varchar2(10)) ;

Table created.

SQL> select * From user_Triggers ;

no rows selected

SQL> create or replace
  2  TRIGGER MY_TRIGGER
  3  BEFORE INSERT OR UPDATE OF column2 ON my_table
  4  FOR EACH ROW
  5  WHEN (new.column2<>old.column2 and new.column2 <> '')
  6  DECLARE temp VARCHAR2(10);
  7  BEGIN
  8    select unistr(:new.column2) into temp from dual;
  9    :new.column2 := temp;
 10   END;
 11  /

Trigger created.

SQL> update my_table set column2='test1';

0 rows updated.

SQL> insert into my_table values(10) ;

1 row created.

SQL> commit;

Commit complete.

SQL> update my_table set column2='test1';

1 row updated.

SQL> commit ;

Commit complete.

SQL> select * from my_table ;

COLUMN2
----------
test1

SQL> insert into my_table values(20);

1 row created.

SQL> select * from my_table ;

COLUMN2
----------
test1
20

SQL> update  my_table set column2='' where column2='20';

1 row updated.

SQL> select * from my_table ;

COLUMN2
----------
test1


SQL> commit;

Commit complete.

SQL> select * from my_table ;

COLUMN2
----------
test1


SQL> insert into my_table values(20);

1 row created.

SQL> commit ;

Commit complete.

SQL> update  my_table set column2='test2' where column2='20';

1 row updated.

SQL>  select * from my_table ;

COLUMN2
----------
test1

test2

SQL> set feed on
SQL> /

COLUMN2
----------
test1

test2

3 rows selected.

SQL>

column2 recieves values like ('aaaa\00f1').
And required is when this type of value comes,

column2 should store it as -> aaaañ.

Please let me know if you need more information

My ad this column is NVARCHAR field