On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > > Hello. > > > On 27.8.2015 18:35, David Nelson wrote: > >>> So in the UPDATE statement, I only provided a value for last_user. But the > >>> first test of the trigger function tests for a NULL value of > >>> NEW.empname. Since > >>> I did not provide one, I was expecting it to be NULL and an exception to > >>> be thrown. Am I just misunderstanding how things work? Is there any way to > >>> test to see if the UPDATE statement contained a reference to empname? If the > >>> answer is no, I can certainly work with that, but before I go on I wanted > >>> to make sure I wasn't missing anything. > >> > >> > >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted > > and the NEW one inserted with the OLD values unless they where explicitly > > changed. So > > > > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing > > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's > > called MVCC. Thanks for setting me straight! > > > >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That > > test would only work if someone explicitly set empname = NULL in the update. If > > you want to check whether the value has not been changed then: > >> > >> IF NEW.empname = OLD.empname THEN > > > > That's exactly the solution I hit on. Back to work, and thanks again. > > Just for the sake of completeness... > > If the value (empname in the above example) can be NULL, the compare does not > work, because > > SELECT NULL = NULL > > returns NULL which is treated as FALSE. > > But I am sure you know this :-) > > > HTH, > > Ladislav Lenart
Thanks Ladislav. To be able to capture updates to NULL, I ended up going with IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...
