Re: Trigger does not work, Please Help

In article <36df3e33.0110121408.69cf9628_at_posting.google.com>, wchy65_at_hotmail.com says...
>
>Hi All
>I have two tables emp and worker. They are exactly same. The emp table
>is from oracle demo. I have created the worker table from all the data
>from the emp table. I have created a trigger to update the emp tables
>comm column whenever there is an update of non null value on workers
>tables comm column. The trigger is created successfully. But nothing
>happens when I update the worker.comm. The trigger doesnot fire and
>does not update the emp.comm. Heres the create trigger statement:
>
>create or replace trigger scott.update_emp after update
>of comm on scott.worker
>for each row
>when (new.comm <> null)
>begin
>update scott.emp set emp.comm = 10
>where emp.empno = :new.empno;
>commit;
>end;
>
>When I created it, it was compiled successfully. But it doesnot do the
>job it is supposed to do. Please help.
>Thank you in advance.

It is tri-valued logic.

There is TRUE, FALSE and UNKNOWN.

It is unknown if new.comm <> null. You cannot compare anything to null. Its always UNKNOWN (not true, not false)

tkyte_at_TKYTE816> select * from dual where null = null;

no rows selected

tkyte_at_TKYTE816> select * from dual where null <> null;

no rows selected

tkyte_at_TKYTE816> select * from emp where comm <> null;

no rows selected

tkyte_at_TKYTE816> select count(*) from emp where comm IS NOT NULL;

  COUNT(*)

         4

tkyte_at_TKYTE816>

Use new.com is NOT NULL instead.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp