Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Trigger fails to update the column value

Trigger fails to update the value for column2

create or replace
TRIGGER MY_TRIGGER
BEFORE INSERT OR UPDATE OF column2 ON my_table
FOR EACH ROW
WHEN (new.column2<>old.column2 and new.column2 <> '')
DECLARE temp VARCHAR2(10);
BEGIN
  select unistr(:new.column2) into temp from dual;
  :new.column2 := temp;
 END;
0
dojjol
Asked:
dojjol
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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
0
 
sdstuberCommented:
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






0
 
Chakravarthi AyyalaDatabase AdministratorCommented:
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>
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dojjolAuthor Commented:
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
0
 
dojjolAuthor Commented:
My ad this column is NVARCHAR field
0
 
sdstuberCommented:
you still need to fix your WHEN condition as noted above


this is wrong
 WHEN (new.column2<>old.column2 and new.column2 <> '')

it should be...

 WHEN (new.column2<>old.column2 and new.column2 is not null)


this assumes your old value is already not null.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now