Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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@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 CorpReceived on Fri Oct 12 2001 - 18:23:52 CDT