David Nelson <dnelson77808@xxxxxxxxx> writes: >>> 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! Huh? I think any DB platform regardless of how it does MVCC is going to leave existing fields as-is in an update if same fields aren't specified. This has nothing specifically to do with Postgres, MVCC, updatable views etc. IMO. >> 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. > >> >>> >>> Thanks! >> >> >> >> -- >> Adrian Klaver >> adrian.klaver@xxxxxxxxxxx > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
