<Burk0> mmm... got another problem... I'm using a "insert or update" trigger for my previous task. When I'm inserting a new row, I'd like the varchar field "oldvalue" to report "NA". I put an IF inside the trigger, checking if :old.id=null but that doesn't seem to work..any hint?
A fortuitous situation to be sure, for I had just minutes before finished reading this 2005 blog post from Robert Vollman. Although this situation was easily answered by informing the reader that comparisons to NULL require the use of "IS" and "IS NOT", as opposed to the equals operator "=", I went a step further and explained that nothing is equal to NULL, not even NULL itself.
The comment by HJR is particularly helpful:
When you insert a row that explicitly includes a NULL, you are explicitly stating you don't know what value should be included for that column. That is again very different from just missing it out. The one is a deliberate action and acknowledgement of the limits to knowledge. The other could be just a mere oversight.Music. Sweet music
...
Incidentally, because NULL means 'I don't know', you can never equate anything to NULL... even itself. Two unknowns are two unknowns, not two bits of the same unknown... for to be able to say that would require knowing something about them!